Доброго всем! С наступающим праздником нашу сильную половину человечества! Пусть жизнь будет мирной!
Спасибо вам за то, что мы чувствуем ваше мужество и доблесть всегда, каждый день и знаем, что наши мужчины - настоящие, сильные, правильные! И этот форум прямое тому доказательство! Вы всегда найдете решение! Вы всегда умнее и сообразительнее!

А теперь буду просить решить нерешаемое:D

Есть две талицы:
Таблица 1

Месяц	Подразделение	Организация	Вид услуги	Внутренний вид услуги	Сумма
Январь	А	Ромашка	Доставка	Доставка	916
Январь	А	Ромашка	Обработка	Комплектация	449
Январь	А	Василек	Хранение	Приемка	548
Январь	А	Василек	Хранение	Хранение	608
Январь	А	Василек	Обработка	Возврат	510
Январь	Б	Лютик	Доставка	Доставка	565
Январь	Б	Лютик	Доставка	Доставка	785
Январь	Б	Лютик	Обработка	Комплектация	784
Январь	Б	Гладиолус	Хранение	Приемка	300
Январь	Б	Гладиолус	Хранение	Хранение	511
Январь	Б	Нарцис	Обработка	Возврат	783
Январь	Б	Нарцис	Обработка	Производство	998
Январь	В	Шалфей	Хранение	Доставка	994
Январь	В	Шалфей	Хранение	Производство	933
Январь	В	Шалфей	Обработка	Комплектация	143
Январь	В	Колокольчик	Хранение	Производство	116
Январь	В	Подснежник	Обработка	Комплектация	254
Январь	В	Подснежник	Хранение	Приемка	625
Январь	Г	Роза	Хранение	Хранение	403
Январь	Г	Мимоза	Доставка	Доставка	518
Январь	Д	Фиалка	Доставка	Доставка	572
Январь	Д	Орхидея	Обработка	Комплектация	948
Январь	Д	Орхидея	Хранение	Приемка	871

Таблица 2

Месяц	Подразделение	Организация	Кол-во
Январь	А	Ромашка	24
Январь	А	Василек	24
Январь	Б	Лютик	12
Январь	Б	Гладиолус	22
Январь	Б	Нарцис	9
Январь	В	Шалфей	22
Январь	В	Колокольчик	11
Январь	В	Подснежник	7
Январь	Г	Роза	14
Январь	Г	Мимоза	12
Январь	Д	Фиалка	7
Январь	Д	Орхидея	8

Необходимо в Таблицу 1 подтянуть данные из Таблицы 2 из столбца кол-во, а потом еще и сумму разделить на количество.

Но! Мы же понимаем, что если мы вот так просто начнем тянуть данные из Таблицы 2 в Таблицу 1 это даст повторы по кол-ву.
Выход есть - группировать данные Таблицы 1 по Месяцу, Подразделению и Организации и вывести сумму, потом прекрасно подтянутся зачения по количеству и просто замечательно посчитается сумма/кол-во.

Но!!!!!!!! При построении сводной я не смогу вытащить в фильтры услуги!!!!! А это супер важно!
Т.е. идея такая: у меня в фильтре тип услуг; сумма меняется, кол-во нет, но вот результат от деления суммы на кол-во меняется.

Информация изначально обрабатывается в Повер квери, потом я строила сводную, но просто среднее вместо суммы брать не получалось т.к. тогда бы я не смогла делить сумм на кол-во, шла в повер пивот там писала две меры одна суммировала данные по сумме, другая усредняла данные по кол-ву потом я их делила и все бы ничего, но в сводной столбец со средним значением по кол-во по организациям показывает правильно, а по подразделениям то среднее, ЕСТЕССНО, а мне надо сумму....
В общем всё в файле...
Простите за лонгрид, просто мне уже кажется, что это невозможно...

Заранее благодарю:))
Хотя бы за то, что дочитали)))

Добрый день, друзья!

Выручайте, пожалуйста:)
Делаю ряд преобразований, финальным штрихом должен быть шаг Столбец сведения, но он выдает
Expression.Error: Элементов в перечислении было слишком много для выполнения операции.

Вообще задача состояла в том, чтобы помимо основной таблицы, оставить еще две строки. 1-ю и 3-ю и перенести их в столбцы.

let
   Источник = Excel.Workbook(Параметр1, null, true),
   Exite_Sheet = Источник{[Item="Exite",Kind="Sheet"]}[Data],
   #"Удалены пустые строки" = Table.SelectRows(Exite_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
   #"Добавлен индекс" = Table.AddIndexColumn(#"Удалены пустые строки", "Индекс", 0, 1, Int64.Type),
   #"Строки с примененным фильтром" = Table.SelectRows(#"Добавлен индекс", each [Индекс] = 0 or [Индекс] = 1 or [Индекс] >= 10),
   #"Удаленные нижние строки" = Table.RemoveLastN(#"Строки с примененным фильтром",6),
   #"Удаленные столбцы" = Table.RemoveColumns(#"Удаленные нижние строки",{"Индекс"}),
   #"Транспонированная таблица" = Table.Transpose(#"Удаленные столбцы"),
   #"Заполнение вниз" = Table.FillDown(#"Транспонированная таблица",{"Column1", "Column2"}),
   #"Объединенные столбцы" = Table.CombineColumns(#"Заполнение вниз",{"Column3", "Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Сведено"),
   #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Объединенные столбцы", {"Сведено", "Column2", "Column1"}, "Атрибут", "Значение"),
   #"Удаленные столбцы1" = Table.RemoveColumns(#"Другие столбцы с отмененным свертыванием",{"Атрибут"}),
   #"Сведенный столбец" = Table.Pivot(#"Удаленные столбцы1", List.Distinct(#"Удаленные столбцы1"[Сведено]), "Сведено", "Значение")
in
   #"Сведенный столбец"

Спасибо вам огромное заранее.
Верю, что вы знаете ответ на мой вопрос.
Я пыталась через столбец индекса... но при чем он тут... у меня же есть столбец под который мне надо разбросать значения и это столбец сведено.

Друзья, когда я задаю параметризацию пути в повер квери при обращении к одному файлу, мне всё понятно.
А вот когда запрос изначально обращается к папке и мне необходимо редактировать путь в примере файла, то не получается.

let
   Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]),
   #"C:\Users\alyon\OneDrive\Рабочий стол\Новая папка\_Картока счета 62 январь-июль xls" = Источник{[#"Folder Path"="C:\Users\alyon\OneDrive\Рабочий стол\Новая папка\",Name="Картока счета 62 январь-июль.xls"]}[Content]
in
   #"C:\Users\alyon\OneDrive\Рабочий стол\Новая папка\_Картока счета 62 январь-июль xls"

Как прописать параметризацию ? Помогите, пожалуйста...
Заранее огромное спасибо!

Всем доброго дня!
Спасибо большое, друзья, что всегда помогаете и отвечаете:))
И вот в очередной раз... но уе не совсем ексель, а повер квери.
Не могу найти ничего похожего на просторах "необъятного".
У меня есть таблица из которой я сохраняю нужные строки.
1, 5, это я делаю добавляя столбец индекса.
А еще мне надо все время сохранять последнюю.
А она может быть и 7 и 41... всё зависит от файлика...
Я знаю как вы не любите вопросы без примеров.
Но если ответа нет на поверхности и надо писать код, то просто так и скажите.


Заранее огромное спасибо!

Друзья, приветствую вас!
Вопрос будет странный, наверное.
Плюс, то, что вы не любите больше всего - "у меня нет файла..."

Ситуация следующая. У коллеги mac, там был хороший масштабный отчет, велся пару лет.
Есть "тело", которое копировалось из 1С, в качестве умной таблицы, а в колонках справа формулы и они автозаполнялись .
Далее миллионы листов с различными вариациями этого же отчета, всё на формулах. БЫЛО!

И тут вдруг все эти отчеты оказываются сохранены как значения. Человек на него дышать боится, плохо знает ексель. Либо ничего не трогал, либо тронул случайно. Но это же как надо тронуть, чтобы все отчеты за 2 года ( т.е. 24 файла минимум) остались без формул???

Я копалась, но а) я не понимаю mac, б) я увидела, что облачко, где хранятся эти файлы, переполнено.

Я знаю, что не любите вы эти темы "про порассуждать" и я не сторонник, но не гуглится ничего и не яндексится...

Может сталкивались с подобным и знаете как починить? Буду очень благодарна!

Всем доброго предоброго!

Друзья, буду очень благодарна вашим гениальным идеям! Верю, кто-то сталкивался с подобными задачами.

Друзья, есть у меня таблица с оборудованием и комплектующими.
В первой колонке содержится информация и о самом оборудовании и о его составе, а далее два столбца с количеством.
Мне очень хочется сделать из нее сводную, чтобы я видела наименование оборудования - ЕГО количество, а ниже перечислялись бы все его компоненты и уже ИХ количество.
Но я категорически не понимаю, какими инструментами это вывести. Ведь сводная здесь не подойдет, даже если я преобразую источник данных? Т.к. по оборудованию я должна видеть не сумму по количеству его составляющих, а сумму самого оборудования?

Помогите, пожалуйста, с вариантами и инструментами решения?

Оборудование/Составляющие	Кол-во   оборудования	Кол-во   составляющих
Оборудование 1	1
Шасси		2
Процессор		1
Блок питания		3
Модуль		2
Жесткий диск		4
Оборудование 2	2
Модуль расширения		1
Модуль памяти		5
Сетевой адаптер		2
Процессор		3
SD карта		2

Друзья, доброго времени суток.
Файлик прикладывать не буду, ибо вы миллион раз сталкивались с подобным.
Ексель сохраняет числа как текст, вариантов справится море, но вопрос не как справится, а почему он так делает?
Что там у него внутри ТАКОГО происходит, что он решает, что число будет текстом и форматом этого не победить.
Меня очень часто спрашивают "почему", я не плохо знаю Ексель (на 0,5% от того, что знаете вы , что автоматом позволяет считать себя гуру ), но я не могу объяснить, что там происходит у него "под капотом".
Спасибо большое!)

Всем доброго вечера,
Меня всегда восхищают решения, которые вы предлагаете, особенно своей лаконичности.
Я попробовала прописать если в если в если и еще в если, но, конечно, запуталась со скобками.
Может у вас появятся идеи как сделать это грамотнее?
Описание задачи в файлике.

Заранее огромное спасибо!

Друзья, всем добрый день!

Подскажите, пожалуйста, как можно создать зависимые выпадающие списки, если не совпадают имя диапазона и имя, которое в ячейки?
К примеру: у меня в ячейке должно быть: "Пищевая промышленность", а имя диапазона будет Пищевая_промышленность. И как быть???

Подскажите, пожалуйста... регулярно с этим сталкиваюсь:))))))))

Друзья, миленькие, помогите, пожалуйста!
Я думаю вам и лишнии слова не нужны, и так поймете, что я тут пытаюсь сделать, но всё же:

=query('Лист1'!A:AE;"select * where N='"&A1&"' and "&if(B1="";;"where C='"&B1&"'"))

надо чтобы функция выдавала массив по первому фильтру where N='"&A1&"'
и если в ячейке В1 пустота, то всё, а если какое-то значение, то по нему и фильтровала.

Спасибо большое!!!

Upd: сделала)))
=query('Лист1'!A:AE;"select * "&ЕСЛИ(B1="";"where N='"&A1&"'";"where N='"&A1&"' and C='"&B1&"'"))

Здравствуйте, подскажите, пожалуйста, можно ли как-то ускорить работу?
есть запрос, который собирает информацию из папки, а в неё в свою очередь добавляются еженедельно файлики.
Далее небольшое количество шагов, но обновляется запрос просто вечность? Это нормально?
Я склоняюсь к мысли, что чуть ли не проще руками одни данные под другие копировать из нужных колонок:(
Что-то не так,да?  

let
   Источник = Folder.Files("W:\Общие документы\Алёна\ТУК"),
   #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Extension] = ".xlsx")),
   #"Отфильтрованные скрытые файлы1" = Table.SelectRows(#"Строки с примененным фильтром", each [Attributes]?[Hidden]? <> true),
   #"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл из ТУК", each #"Преобразовать файл из ТУК"([Content])),
   #"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
   #"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл из ТУК"}),
   #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл из ТУК", Table.ColumnNames(#"Преобразовать файл из ТУК"(#"Пример файла"))),
   #"Другие удаленные столбцы" = Table.SelectColumns(#"Столбец расширенной таблицы1",{"Source.Name", "Column1", "Column2", "Column3", "Column4", "Column5", "Column366", "Column367"}),
   #"Строки с примененным фильтром1" = Table.SelectRows(#"Другие удаленные столбцы", each [Column1] <> null and [Column1] <> 1 and [Column1] <> 2),
   #"Строки с примененным фильтром2" = Table.SelectRows(#"Строки с примененным фильтром1", each not Text.Contains([Column1], "Отчет по") and not Text.Contains([Column1], "ИТОГОВЫЙ ОТЧЕТ")),
   #"Повышенные заголовки" = Table.PromoteHeaders(#"Строки с примененным фильтром2", [PromoteAllScalars=true]),
   #"Строки с примененным фильтром3" = Table.SelectRows(#"Повышенные заголовки", each not Text.Contains([МР], "МР")),
   #"Извлеченные первые символы" = Table.TransformColumns(#"Строки с примененным фильтром3", {{"18 нед_итог_07.05.xlsx", each Text.Start(_, 2), type text}}),
   #"Измененный тип2" = Table.TransformColumnTypes(#"Извлеченные первые символы",{{"18 нед_итог_07.05.xlsx", Int64.Type}}),
   #"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип2",{{"18 нед_итог_07.05.xlsx", "КН"}}),
   #"Измененный тип3" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Column6", Percentage.Type}}),
   #"Переименованные столбцы2" = Table.RenameColumns(#"Измененный тип3",{{"Column6", "Результат"}, {"МР", "Дивизион"}, {"Column7", "ТО ТУК"}, {"Column8", "ТО общий"}})
in
   #"Переименованные столбцы2"

Заранее огромное спасибо!

Здравствуйте,
Могли бы, пожалуйста, помочь разобраться с теоретической частью. Много прочитала, но понимание не пришло.
Если я гружу данные сначала в повер квери, а потом просто создаю подключение или создаю подключение и добавляю данные в модель данных. В чем разница? Как быстрее? На какой стороне в каком соучае обрабатываются данные? Спасибо большое заранее.

Добрый день,

У меня есть файл. А там столбец стоимость.
Мне по этому столбцу надо определить среднюю стоимость. Но, среднее я не могу считать пока не исключу экстримально максимальные и минимальные значения.
Т.е. мне нужна возможность на каком-то шаге исключить 5-10-15% наибольших и наименьших значений.
Как это можно прописать в Power Query?

Заранее огромное спасибо!
Я верю, что у вас есть решение)))  

Здравствуйте!

Подскажите, пожалуйста, как в Power Query добавить несколько запросов из текущего файла?
Если я использую функцию Excel.CurrentWorkbook, то она автоматом добавляет табицы друг под друга, а мне надо, чтобы они именно загрузились отдельными запросами, как в случае обращения к другому файлу.
Попробовала из самого файла обратиться к нему же, но он ругается, говорит занят файл другим приложением.
А оно и понятно, он же открыт...

Заранее благодраю!
Знаю, что задаю какой-то очевидный вопрос, но я вначале пути и очень нужен ответ:)

Здравствуйте!

Мне кажется, у меня очень интересный вопрос.

Есть огромный список сотрудников, которые работают вахтовым методом. Кто-то 60/60, кто-то 45/45, кто-то 30/30.
Есть таблица. Что-то вроде диаграммы Ганта. И вот хочется, чтобы на год сразу закрашивались периоды, когда сотрудник работает, в зависимости от выбранного графика.
В приложении таблица, где я сделала классическую диаграмму Ганта, а мне надо, чтобы также до конца года окрасилось.
Т.е. если сотрудник работает 60/60, то 60 дней окрасилось, 60 пропустилось, 60 снова окрасилось, а зависел этот "окрас" от первой даты вахты и от графика, выбранного в выпадающем списке.

Заранее спасибо огромное!!!!!!!!

Добрый день!

Подскажите. пожалуйста, как необходимо прописать пользовательский формат, чтобы числа не просто округлялись, а именно отображались как кратные 1000

например
196 296 должно отобразить как 200 000
568 265 как 600 000
887 как 900
и т.д.

Заранее очень благодарна, т.е. смысл как окрвверх, но с помощью пользовательского....

Подскажите, пожалуйста, какой смысл в впр или в суммесли, например, искомое значение/условие указывать диапазоном, а не одной ячейкой?
Зачем-то же в екселе есть тская возможность...
=ВПР(A2:А15;А:Е;5;0), а не =ВПР(A2;А:Е;5;0)
Заранее огромное спасибо!!!
Очень давно не могу найти ответ на этот вопрос!

Спасибо большое всем! Просто огромное!
Благодаря вам я просто начинаю думать иначе.
Хотя, бываю близка в своих догадках, но опыта прописать не хватает)))
Спасибо еще раз огромное! Это просто мой университет Excel)))

Смотрите, проблема в том, что у меня диаграмма ганта одна по дням и там с условным форматированием всё ок, рисуется.
а вот втора должна отображать сжатый вариант предыдущей. Т.е. уже не по дня, а по месяцам. И там где вопрос касается проекта, т.е. длительность более месяца, всё ок.
А вот когда вопрос касается этапов проекта, то если этап длился менее месяца, то уже не красит.
отдельно выделять диапазоны не вариант, т.к. проектов огромное количество.
Можно ли как-то одним правилом прописать?

В приложенном файле всё что зеленым это с помощью условного форматирования, а вот красным то, как бы хотелось, но уже от руки:))

Доброго вечера, друзья!

Помогите, пожалуйста, решить следующую задачу.
Думаю для вас она вполне тривиальна.

Есть, пока три условия, в будущем их может быть больше, и необходимо, чтобы выгружался массив данных по этим трем условиям.
У меня получалось элементарно выгрузить весь массив.
Т.е. где истина, там значения подтянулись, где ложь, там ложь.
А как вот оставить только значения истина??

У меня формула написана в файле, может она не сильно жизнеспособна для нескольких условий.
да и опять же она выдает весь массив и истину и ошибки...

Помогите, пожалуйста, я очень многому учусь на этом форуме.

И благодарна всегда каждому варианту и каждому ответу!

Доброго всем вечера!

1) ДЛЯ СТОЛБЦОВ I и J. Смотрите, есть таблица в которую необходимо подтянуть суммы.
Условием для суммирования является дата в текстовом формате.
И формулу я написала, но я ее не могу протянуть.
Ок сейчас три ячейки, а дальше будет 333:))
2) ДЛЯ СТОЛБЦОВ H и K необходимо подтянуть последнюю сумму месяца.
Опять же, по формуле, которую я там проставила, понятно какую ячейку надо подтягивать, но как написать так, чтобы протянуть?

Заранее огромное спасибо!

Доброго вечера всем!

Подскажите, пожалуйста, как транспонировать хаотичный набор данных (2 столбца) последовательно в единую строку?

Приветствую, знатоки!

Не приложу ума как реализовать задачу.
Необходимо просто данные из двух столбцов перенести в строку.
Трансп не получается... там данные находятся в хаотично выбранных ячейках.

Буду очень благодарна, если прольёте свет!

Доброго вечера всем!

Есть таблица в которую необходимо подтягивать данные, но с учетом выбора диапазона.
Подтягивать можно было бы индексом и поиском позиции, но как учесть из какого диапазона?
Индексом со ссылкой на массивы мне кажется не вариант, т.к. у меня выпадающий список городов, а не номеров диапазонов.
Двссыл и смещ пробовала, но тут возможно владею не в совершенстве.

Буду очень благодарна, если научите)))

Доброй ночи, всем
помогите, пожалуйста, понять можно ли каким-о образом прописать в одной ячейке следующий алгоритм расчёта.
Есть таблица, в неё по заданным условиям необходимо подтянуть сумму. Но вот диапазон условия в таблице из которой мы планируем подтягивать суммы, отсутствует.
Его можно подтянуть туда с трех других таблиц (я так и сделали). Но мне бы хотелось, чтобы дополнительного столбца в таблице с суммами не было.
заранее большое спасибо!

Добрый день, всем!

Интересное тестовое задание.
Необходимо как бы используя суммеслимн просуммировать данные с другого листа, но там отсутствует один из диапазонов.
При этом есть другие листы, с которых можно этот диапазон сгенерить. Я так и сделала. Но мне интересно, а можно ли как-то прописать всё в одной формуле?

Или может есть способы сократить формулу?
Посмотрите, пожалуйста?

Заранее огромное спасибо!

Добрый вечер, уважаемые знатоки!

Может ли как-то функция или вернуть массив ответов? Или есть другой вариант?

=ЕСЛИ(И(Дивизион="Краснодар";ИЛИ(GR=B97;GR=B100));Сумма_с_НДС*18%)

Заранее спасибо)

Добрый вечер,

Опять никак без вашей помощи, профи Excel:))
Но когда-нибудь я тоже научусь)) Кстати, есть прогресс

Помогите, пожалуйста, написать следующую формулу.

У меня огромная таблица.
В ней большое количество информации по обратной связи.
Мне необходимо просчитать количество оставленных комментариев по следующей логике:
Считать если это Москва, Ромашка и с момента прошлого обращения прошло более 30 дней.
Цель просуммировать по каждому филиалу и наименованию количество обращений.

Заранее благодарю вас!

Добрый день, друзья!

Подскажите, пожалуйста, как можно написать суммесли (а может и вовсе не суммесли) для суммирования значений более чем из одного столбца, при том, что этот диапазон суммирования определяется в зависимости от выбранного месяца.

Заранее очень благодарна:))

Добрый вечер,
Могли бы, пожаааалуйста, помочь заменить чем-нибудь если в если, 2 дня думаю(

В файле в зависимости от региона и суммы фот, надо подтянуть расчётную ставку,
т.е. если город Москва и ФОТ 500, то расчётная ставка равна 4.



Заранее очень благодарна!