Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 17 След.
Подсчет ячеек по условиям с исключением повторяющихся
 
Андрей,
Код
=СЧЁТЗ(УНИК(ФИЛЬТР(Таблица1[#Все];(Таблица1[[#Все];[Статус]]=G3)*(Таблица1[[#Все];[Дата]]<44197))))/3
Создание таблицы из столба, Создание таблицы из столба
 
точь в точь была тема от 13 февраля 21 года, даже файл с примером преобразования остался
преобразование делалось на простом макросе + pq
Цитата
Mershik написал: а как определить конец?
1 блок это от "жирного" до "жирного"
Свод данных из нескольких столбцов в один друг под другом при наличии пустых столбцов
 
pq
Код
let
    Source = Excel.Workbook(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=437821&action=download")){[Item="Исходные данные",Kind="Sheet"]}[Data],
    a = Table.UnpivotOtherColumns(Source, {}, "w", "q")[q],
    b = List.Sort(a, Order.Ascending)
in
    b
Разбить текст с ячейки по новым строкам
 
pq
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    a = Table.Distinct(Table.ExpandListColumn(Table.TransformColumns(Source, {"Изображения", Splitter.SplitTextByDelimiter(", ")}), "Изображения"), "Изображения")
in
    a
Сцепить диапазон с символом
 
Код
=ОБЪЕДИНИТЬ(";";;F7:J7)
Разбить столбец на 2 в PQ
 
Код
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\1\Пример.txt"))}),
    a = Table.FromRows(List.Split(Source[Column1],2), {"1", "2"})
in
    a
Автоматическая сортировка по суммам с использованием двух критериев
 
Добрый!
Код
=СОРТПО(ФИЛЬТР(ФИЛЬТР(Таблица1[#Все];(Таблица1[[#Все];[КОЛОННА]]=M2)*(Таблица1[[#Все];[МЕСЯЦ]]=M3));{1;0;0;1});ФИЛЬТР(ФИЛЬТР(Таблица1[#Все];(Таблица1[[#Все];[КОЛОННА]]=M2)*(Таблица1[[#Все];[МЕСЯЦ]]=M3));{0;0;0;1});-1)
Преобразовать бинарное значение в текстовое значение, обращение к базе данных 1с из Power Query
 
Код
= Table.TransformColumns(Source, {}, (x)=> Text.FromBinary(Text.ToBinary(Text.From(x), 866), 1251))

посмотрите, может подойдет
Группировка списков запросов
 
Цитата
mazersw написал:
код выдает - Syntax Error и подсвечивается красным
это не VBA, это Power Query
в файле, который я приложил все это есть
Поиск подстроки в ячейке
 
Добрый!
Код
=ПОИСК(";"&A2&";";";"&B2&";";1)
Группировка списков запросов
 
Добрый!
pq
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    a = Table.ExpandListColumn(Table.TransformColumns(Source, {"Список1", Splitter.SplitTextByDelimiter(", ")}), "Список1"),
    b = Table.AddColumn(a, "q", each [Категория] & ": " & [Список1] & " " & Text.From([Список2]))[[q]]
in
    b
Поиск значения по двум параметрам один из которых примерное время
 
Код
=ПРОСМОТРX($H3;ФИЛЬТР(ФИЛЬТР($A:$C;$A:$A=ПРОСМОТРX(I$2;$A:$A;$A:$A;;-1));{0;0;1});ФИЛЬТР(ФИЛЬТР($A:$C;$A:$A=ПРОСМОТРX(I$2;$A:$A;$A:$A;;-1));{0;1;0}))

на большом объеме считать будет медленно, есть другие методы подсчета, более быстрые, pq к примеру
Подсчитать уникальные значения в рабочее время
 
Код
=СЧЁТЗ(УНИК(ФИЛЬТР(ФИЛЬТР(G2:H20;(ВРЕМЗНАЧ(ТЕКСТ(G2:G20;"[$-x-systime]ч:мм:сс AM/PM"))>=ВРЕМЯ(8;0;0))*(ВРЕМЗНАЧ(ТЕКСТ(G2:G20;"[$-x-systime]ч:мм:сс AM/PM"))<=ВРЕМЯ(17;0;0))*(ДЕНЬНЕД(G2:G20)<>1)*(ДЕНЬНЕД(G2:G20)<>7));{0;1})))
Сравненть строки по ФИО и при совпадении копировать их в другую книгу
 
можно применить 1 раз функцию ПРОСМОТРX
Склеивание текста ячеек, если в соседнем столбце одинаковое имя
 
Код
=УНИК(A2:A188)

Код
=ОБЪЕДИНИТЬ(", ";;ЕСЛИ(A2:A188=J2;D2:D188;""))
Макрос. Копирование и вставка транспонированием по условию.
 
добрый!
почему нельзя все данные сразу транспонировать?
Код
let
    a = Table.Group(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], {"C"}, {"q", Table.Transpose}),
    b = Table.Combine(a[q])
in
    b
Настройка кодировки чтения dbf файла в Power Query, Настроить чтение кириллических символов dbf файлов
 
Добрый день!
Код
let
    Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\1\;extended properties=dBASE IV;", [Query="select * from [021746.dbf]"]),
    a = Table.TransformColumns(Source, {}, (x)=> Text.FromBinary(Text.ToBinary(Text.From(x), 866), 1251))
in
    a
Изменено: Антон - 23 фев 2021 14:39:43
Заполнение одной таблицы из нескольких других с одинаковой структурой данных
 
вариант на pq
Код
let
    Source = Table.AddColumn(Folder.Files("C:\12"), "q", each Excel.Workbook([Content])),
    a = Table.TransformColumns(Source, {"q", each Table.SelectRows(_, each [Name] = "Лист1")})[[Name], [q]],
    b = Table.TransformColumns(a, {"q", each Table.SelectRows(Table.Skip(Table.PromoteHeaders([Data]{0}), 1), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))}),
    c = Table.ExpandTableColumn(b, "q", Table.ColumnNames(b{0}[q]))
in
    c

где должны находиться файлы и с какого листа берутся данные видно из запроса
Извлечь из ячейки строчные буквы
 
вариант на pq
Код
let
    a = Table.DuplicateColumn(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], "Столбец1", "old"),
    b = Table.AddIndexColumn(a, "№", 1),
    c = Table.SplitColumn(b, "Столбец1", Splitter.SplitTextByAnyDelimiter({"А".."Я", "A".."Z"}), {"q", "new"})[[#"№"], [old], [new]]
in
    c
Найти в таблице дубли данных физических лиц и копировать их на новый лист
 
Камиль Гусейнов, скрины увиден, но в моем файле нет ни одного макроса
Найти в таблице дубли данных физических лиц и копировать их на новый лист
 
Цитата
Камиль Гусейнов написал:
Сам код не понял если честно как применить отдельно
почитайте Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel, будет интересно
Цитата
Камиль Гусейнов написал:
файл ругается, что браузер нужен старее чем у меня установлен
что-то я сомневаюсь, покажите скрин
Найти в таблице дубли данных физических лиц и копировать их на новый лист
 
вариант на pq, время работы на ~ 100 тыс. строках около 15 сек
Код
let
    z = Table.Buffer(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content]),
    a = Table.Group(z, {"Фамилия ", "Имя ", "Отчество ", "Дата рождения "}, {"q", each Table.RowCount(_)}),
    b = Table.Group(z[[Физ. Лицо идентификатор]], {"Физ. Лицо идентификатор"}, {"w", each Table.RowCount(_)}),
    c = Table.Join(z, "Физ. Лицо идентификатор", b, "Физ. Лицо идентификатор"),
    d = Table.Join(c, {"Фамилия ", "Имя ", "Отчество ", "Дата рождения "}, a, {"Фамилия ", "Имя ", "Отчество ", "Дата рождения "}),
    f = Table.AddColumn(d, "q1", each [w]=[q]),
    e = Table.SelectRows(f, each ([q1] = false)),
    q = Table.SelectColumns(e,Table.ColumnNames(z))
in
    q
Изменено: Антон - 12 фев 2021 00:42:08
Разница между временем в Power Query
 
Андрей VG, согласен - #time()
Разница между временем в Power Query
 
Evgenii Kov, изучайте #duration(x,x,x,x)
Код
= Table.SelectRows(a, each [Время] > #duration(0, 7, 30, 0) and [Время] < #duration(0, 17, 30, 0))
Оставить только по 5 строк с одинаковыми дата+домен
 
еще меньше кода на pq
Код
let
    Source = Table.Group(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], {"Домен", "Дата"}, {"q", each Table.FirstN(_, 5)}),
    a = Table.Combine(Source[q])
in
    a
Список должников студентов на VBA Excel
 
pq
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    a = Table.UnpivotOtherColumns(Source, {"ФИО", "Группа", "Ср.Балл"}, "q", "w"),
    b = Table.SelectRows(a, each ([w] = 2)),
    c = Table.Pivot(b, List.Distinct(b[q]), "q", "w"),
    d = Table.ReorderColumns(c, Table.ColumnNames(Source),MissingField.Ignore)
in
    d
Поиск с условием и конкатенация данных, совпадающих по одному столбцу
 
Добрый!
на pq
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    a = Table.Group(Table.TransformColumnTypes(Source,{"Столбец1", type text}), {"Столбец2"}, {"q", each Text.Combine([Столбец1], ",")})
in
    a
Сделать гиперссылки активными
 
рядом ввести =ГИПЕРССЫЛКА()
Power Query. Вычисление средневзвешенной цены в сводной таблице
 
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица_ExternalData_1"]}[Content],
    a = Table.Group(Источник, {"Append1[Канал сбыта]", "Append1[Группа товара 4 ур.]", "Append1[Товар]", "Append1[Показатель]", "Append1[Значения]"}, {"w", each List.Sum([#"Append1[Итог]"])}),
    b = Table.CombineColumns(a,{"Append1[Показатель]", "Append1[Значения]"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"q"),
    c = Table.Pivot(b, List.Distinct(b[q]), "q", "w"),
    d = Table.AddColumn(c, "a", each [#"План Стоимость с НДС, руб"]/[#"План Отгружено, кг"]),
    f = Table.AddColumn(d, "b", each [#"Факт Стоимость с НДС, руб"]/[#"Факт Отгружено, кг"])
in
    f
Изменено: Антон - 9 фев 2021 15:18:40
PQ. Преобразование одинаковых идентификаторов в разные
 
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    a = Table.Group(Source, {"Номер договора"}, {"z", each [q = Table.AddColumn(Table.AddIndexColumn(_, "q",3), "Номер договора ", each if [q] > 3 then Text.From([Номер договора]) & Text.Repeat("0",[q]) else [Номер договора])][q]}),
    b = Table.Combine(a[z]),
    c = Table.SelectColumns(b,{"Дата", "Контрагент", "Номер договора ", "Сумма"})
in
    c
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 17 След.
Наверх