Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 56 След.
Cобрать данные из столбца в строку
 
PQ
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Наименование", type text}, {"Год", Int64.Type}, {"Параметр", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Год", type text}}, "lt-LT"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Год", type text}}, "lt-LT")[Год]), "Год", "Параметр", List.Sum),
    #"Added Conditional Column" = Table.AddColumn(#"Pivoted Column", "Custom", each if [2021] = 0 then "" else null ),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Наименование", "Custom", "2021", "2022", "2023"})
in
    #"Reordered Columns"
Удалять строки с текстом, который не подходит условию.
 
Спасибо за подсказку, т.к. я только  "тупарь-самоучка".
Удалять строки с текстом, который не подходит условию.
 
Power Query
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Исходные данные", type text}, {"Количество", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Исходные данные",Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),{"Исходные данные.1", "Исходные данные.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Исходные данные.1", type text}, {"Исходные данные.2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Исходные данные.2] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each [Исходные данные.1]&" "&[Исходные данные.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Исходные данные.1", "Исходные данные.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Количество"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "исходные данные"}})
in
    #"Renamed Columns"
В Power Query создать столбец со значениями за предыдущий год
 
Смотрите приложение с кодами
2020
Код
let
    Source = Excel.Workbook(File.Contents("C:\Users\seven\Documents\Documents\выручка_1.xlsx"), null, true),
    #"2020_Sheet" = Source{[Item="2020",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2020_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"дата", type date}, {"выручка2021", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "дата", "дата - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column",{{"дата - Copy", Date.Month}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "дата", "дата - Copy.1"),
    #"Extracted Day" = Table.TransformColumns(#"Duplicated Column1",{{"дата - Copy.1", Date.Day}})
in
    #"Extracted Day"
2021
Код
let
    Source = Excel.Workbook(File.Contents("C:\Users\seven\Documents\Documents\выручка_1.xlsx"), null, true),
    #"2021_Sheet" = Source{[Item="2021",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2021_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"дата", type date}, {"выручка2021", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "дата", "дата - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column",{{"дата - Copy", Date.Month}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "дата", "дата - Copy.1"),
    #"Extracted Day" = Table.TransformColumns(#"Duplicated Column1",{{"дата - Copy.1", Date.Day}}),
    #"Merged Queries" = Table.NestedJoin(#"Extracted Day",{"дата - Copy", "дата - Copy.1"},#"2020 (2)",{"дата - Copy", "дата - Copy.1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"выручка2021"}, {"NewColumn.выручка2021"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.выручка2021", "выручка2020"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"выручка2020"})
in
    #"Removed Other Columns"
Разделение таблицу на листы по фамилиям
 
Перенсите первую таблицу в Power Query, создайте необходимое количесво копий, отфильтруйте каждую таблицу, переименуйте и загрузите обратно каждую таблицу.
Формирование списка фамилий отсутствующих из столбца А по критерию, приведенному в столбце В
 
PQ
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Фамилия", type text}, {"Причина отсутствия", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Фамилия", "Список отсутствующих"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Причина отсутствия] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Причина отсутствия"})
in
    #"Removed Columns"
Изменено: jakim - 07.11.2021 20:53:25
Макрос транспонирования массива
 
А если так с PQ.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Было", type text}, {"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Месяц"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
Power Pivot расчет стажа сотрудников.
 
Предлагаю вариант PQ.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Сотрудник", type text}, {"Дата приема", type date}}),
    #"Calculated Age" = Table.TransformColumns(#"Changed Type",{{"Дата приема", each Date.From(DateTime.LocalNow()) - _, type duration}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Calculated Age",{{"Дата приема", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Дата приема", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Дата приема", "Дата приема - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Дата приема - Copy", "Дата приема - Copy - Copy"),
    #"Added Custom" = Table.AddColumn(#"Duplicated Column1", "Custom", each [Дата приема]-1900),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Inserted Year" = Table.AddColumn(#"Removed Columns", "Year", each Date.Year([Дата приема]), type number),
    #"Added Custom1" = Table.AddColumn(#"Inserted Year", "Custom", each [Year]-1900),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Дата приема", "Year"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Year"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Сотрудник", "Year", "Дата приема - Copy", "Дата приема - Copy - Copy"}),
    #"Inserted Month" = Table.AddColumn(#"Reordered Columns", "Month", each Date.Month([#"Дата приема - Copy"]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([#"Дата приема - Copy - Copy"]), type number),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Day",{"Дата приема - Copy", "Дата приема - Copy - Copy"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type3","","",Replacer.ReplaceValue,{"Year"}),
    #"Lowercased Text" = Table.TransformColumns(#"Replaced Value",{{"Year", Text.Lower}}),
    #"Uppercased Text" = Table.TransformColumns(#"Lowercased Text",{{"Year", Text.Upper}}),
    #"Added Suffix" = Table.TransformColumns(#"Uppercased Text", {{"Year", each Text.From(_, "lt-LT") & " г.", type text}}),
    #"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Month", each Text.From(_, "lt-LT") & " м.", type text}}),
    #"Added Suffix2" = Table.TransformColumns(#"Added Suffix1", {{"Day", each Text.From(_, "lt-LT") & " д.", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Added Suffix2", "Custom", each [Year] &[Month] &[Day]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"Сотрудник", "Year", "Month", "Day"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Custom", "Стаж"}})
in
    #"Renamed Columns1"
Изменено: jakim - 02.11.2021 11:41:04
Найти среднее значение 5 минимальных положительных значений в диапазоне
 
Ещё одна формула
Код
=AVERAGE(AGGREGATE(15;6;A1:A10/(A1:A10>0);{1\2\3\4\5}))
Изменено: jakim - 31.10.2021 08:07:27
Количество заказов у клиента, если один из заказов - определенный товар
 
Может с помощью PQ.
Изменено: jakim - 29.10.2021 16:47:55
промежуточная группировка в PQ
 
Упрощённый вариант
Счет количества по условиям в 2 столбцах
 
Возможно применить и PQ.
Частично транспонировать таблицу
 
Формулами
Изменено: jakim - 23.10.2021 16:32:00
Умножить на 20% значения ячеек
 
Покажите "ручками", как должен результат.
Найти значение из столбца А, соответствующее наибольшей разнице двух других столбцов
 
При использовании функции АГРЕГАТ массивный ввод не требуется.
Код
=INDEX(A1:A140;AGGREGATE(15;6;ROW($1:$1000)/(MAX(C1:C140-B1:B140)=C1:C140-B1:B140);1))
Как разделить и выбрать данные одной ячейки в другие ячейки
 
1. Данные --- Текст по столбцам (разделитель-"пробел")
2. Данные --- Текст по столбцам (разделитель-"-")
 
первые столбцы в последний столбец, по очереди
 
формула
Код
=IFERROR(INDEX($A$2:$D$6;CEILING(ROWS($2:2)/4;1);MOD(ROWS($2:2)-1;4)+1);"")
Сортировка формулами таблицы по столбцу с датами
 
Вначале сортируем дату формулой (даты дожны быть в цифровом варианте)
Код
=SMALL($C$6:$C$200;ROWS($6:6))
а потом остальные данные
Код
=IFERROR(INDEX(B$6:B$200;AGGREGATE(15;6;ROW($1:$200)/($C$6:$C$200=SMALL($C$6:$C$200;ROWS($6:6)));COUNTIF($I$6:$I6;$I$6:$I$200)));"")
Изменено: jakim - 11.09.2021 09:06:03
Расчет зарплаты для сотрудников по условиям
 
Формула
Код
=H3*(1+INDEX(E$30:E$37;AGGREGATE(15;6;ROW($1:$20)/(VLOOKUP(J3;B$13:C$20;2;0)=B$30:B$37)/(I3=C$30:C$37)/(K3=D$30:D$37);1)))
Изменено: jakim - 09.09.2021 10:19:04
Выборка из таблицы по нескольким условиям
 
Номер учета
Код
==IFERROR(INDEX(Учет!G$2:G$100;AGGREGATE(15;6;ROW($1:$100)/(B$2=Учет!D$2:D$100)/(B$3=Учет!E$2:E$100)/(B$4=Учет!F$2:F$100);ROWS($2:2)));"")
Нумерация
Код
=IF(E2<>"";ROWS($2:2);"")
Изменено: jakim - 07.09.2021 16:49:56
С другого листа вставить значения по идентификатору ID. Все вставить в одну строку с ID
 
Ещё одна формула без массивного ввода
Код
=IFERROR(INDEX(№1!$B$2:$B$1000;AGGREGATE(15;6;ROW($1:$10000)/($A2=№1!$C$2:$C$10000);COLUMNS($B:B)));"")
Почему не работает консолидация, Объясните
 
В Вашем случае лучше воспользоваться Power Query.
Изменено: jakim - 29.08.2021 10:26:46
Найти пропущенные значения в последовательности
 
Формулой с дополнительным столбцом В.
Суммировать значения столбца по критерию, который указывается в отдельной ячейке
 
Формула
Код
=IF(B1=B2;"";SUMIF(B:B;B1;C:C))
Преобразование прайса. Определенные столбцы с ценами переводить в строки
 
Если количество атрибутов и цен постоянно
Изменено: jakim - 22.07.2021 14:19:00
Развернуть таблицу из горизонтальной в вертикальную
 
Смотрите приложение
Изменено: jakim - 17.07.2021 15:39:36
Поиск нужного тарифа по трём критериям (участок, масса автопоезда, масса груза)
 
Предлагаю такую формулу
Код
=INDEX(Тарифы!D$2:D$41;AGGREGATE(15;6;ROW($1:$1000)/(Тарифы!A$2:A$41=D2)/(Тарифы!B$2:B$41>=F2/1000)/(Тарифы!C$2:C$41=G2);1))
Изменено: jakim - 10.07.2021 08:30:59
Вычисление позиции значения в списке (k-позиция)
 
Формула
Код
=RANK.EQ(INDEX(Res!D$2:D$215;MATCH(E6;Res!I$2:I$215;0));Res!$D$2:$D$215)
Заполнить таблицу из данных другого листа по двум условиям., Заполнить таблицу из данных другого листа по двум условиям.
 
Формула
Код
=IFERROR(INDEX('Ввод данных'!D$2:D$100;AGGREGATE(15;6;ROW($1:$100)/('Ввод данных'!$B$2:$B$100=$D$18)/('Ввод данных'!$A$2:$A$100=$B$18);ROWS($21:21)));"")
В нижней части таблицы уберите объединение ячеек.
Как перевести данные по условию со строк в столбцы
 
Формулы
для даты
Код
=IFERROR(INDEX($A$2:$A$600;AGGREGATE(15;6;ROW($1:$1000)/($B$2:$J$1000<>"");ROWS($2:2)));"")
для времени
Код
=IFERROR(INDEX($B$2:$J$1000;MATCH(O2;A$2:A$1000;0);AGGREGATE(15;6;COLUMN($A:$J)/($B$2:$J$1000<>"")/($A$2:$A$1000=O2);COUNTIF(O$2:O2;O2)));"")
Изменено: jakim - 04.07.2021 14:06:31
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 56 След.
Наверх