Да вроде работает, большое спасибо. Но выглядит все таки немного сложновато, буду пытаться разобраться.

Если будет возможность подскажите как в дополнение в эту же сводную вывести колонки "сумма ремонта" и время "ремонта по коэф"

по поводу "суммы ремонта" наверно все таки правильнее будет относить всю сумму на месяц в котором ремонт завершился



А у вас случайно файл не сохранился? приложите пожалуйста файлом если остался.
Как бы ваш вариант тоже интересен.
Возможно в чем то он громоздкий но его проще понять (без спец знаний).
Но без самого файла не очень удобно

Вообще если честно, то я возлагаю надежды на Сводную + DAX
Там вроде есть функция получающая диапазон между датами
Что-то вроде DATESINPERIOD
Но Dax для меня сложен и я не могу вкурить как вообще проделать этот трюк и возможен ли он вообще

Вообще наверно как вариант можно пойти в эту сторону.
По факту мне даже важнее кол-во часов чем сумма затрат.
В вашем варианте убрать часть формулы и получатся часы.

Но сводная таблица предпочтительнее т.к.
реальная таблица содержит чуть больше данных
плюс надо понимать что условно наименование ВАЗ может повторятся много раз (например каждый день ремонт по 2 часа)
и этот ваз надо соответственно отсортировать, просуммировать в рамках месяца
а если еще захочется вывести все 3 показателя (время/ сумма /время по коэф) то придется городить 3 колонки
да и потом продлевать это дело вправо наперёд

Но в любом случае это лучше чем ничего, это уже хоть какое-то направление

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

Но как я уже говорил рассмотрим любые варианты.

Решил попробовать создать максимально простую таблицу учета времени ремонтов.
Но то что казалось простым в итоге принесло только проблемы.

Изначальная мысль была проста. Указать дату и время начала и окончания работ вычесть их друг из друга и получить время ремонта.
Потом создать сводную таблицу и вывести время в разрезе месяцев.
Если даты начала и окончания ремонта в одном месяце всё ок. Но если в разных то тогда вопрос как делить.

Накидал простой пример в нем должно быть всё понятно.
В текущий момент устроят такие инструменты:
любые формулы кроме массивных;
вспомогательные столбцы,таблицы;
умные таблицы;
сводная таблица;
сводная таблица по модели и формулы DAX;

Макрос возможно но прям в самом крайнем случае, желательно простой
Power Query можно, но тоже лучше приберечь на край
Вообще PQ мне очень нравится но как правило при его использовании много нюансов
(то различные разрешения, то ошибки пути, то в данных какое то отклонение и может всё поплыть)
И для человека который в excel не понимает ничего это может стать огромной проблемой


Вообще не откажусь от идей как в excel можно вести учёт времени ремонтов
Если у кого-то есть примеры, подсказки, ссылки, идеи. Интересно всё
Основной критерий: Для того кто заполняет данные всё должно быть максимально просто.

Мне кажется автор столкнулся с проблемой для которой как я понимаю нет адекватного решения.
И если я правильно понимаю то эта проблема называется "Текст нулевой длины" который получается когда формула выдает результат =""

У меня была подобная проблема, я в одном файле собирал числовые данные часть из которых как раз выводилась =""
Потом эти данные копировал и вставлял в другой файл значениями и думал что после вставки значениями ячейка становится пустой.
Но нет на самом деле туда всёравно переносится значение ="". И если на эту ячейку посадить любую формулу, типа =2+"эта ячейка", она выдаст знач.

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

Но если это всётаки текст нулевой длины виновник, то как я понимаю вариантов не много.
Это или "найти и заменить" или макрос или удаление или вручную


А что понимает под слиянием? и увидеть бы сам файл word

Чисто теоретически вы можете использовать на вкладке Данные инструмент Промежуточные итоги.
Но я бы не стал, лучше сводная.

Вообще мне кажется есть множество вариантов, не совсем понятен объем работ.
То есть в таблице реально 5 наименований или их там 105? может вам больше подойдут макросы, PQ, или что то ещё.

Я возможно не до конца понял суть проблемы.
Но подозреваю беда в том что вы не можете просто протянуть формулу вниз.
И соответственно надеюсь что вы шапку из наименований выделяете и нажимаете вставить (выбрав параметр Транспонировать)
И со списком наименований проблем нет.

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

Примеры в файле

Hugo, Абсолютно прав вам по сути нужно просто протянуть формулу вниз.
Но потом я подумал а зачем у вас в формуле вписано "МИН"
И тут до меня доперло что вы хотите. Но вы неправильно задали вопрос, ввели всех в заблуждение.
Пропишите так =МИН(C2/D2;1)

PS/ Хотя может и я сам себя ввел в заблуждение. И эта "МИН" у вас вообще лишняя. Зависит от того понимаете ли вы сами что она делает.

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


Вообще первоначально я пытался добавить столбец с пользовательской функцией, но постоянно значение ставилось Error, что то не правильно делал.
Но в итоге сейчас попробовал ещё раз и получилось.
Решение канеш так себе, и редактировать сложно и дополнять неудобно. (по сути почти тоже что и с функцией, хотя наверно функция вышла бы полегче, если бы я знал как её применить на листы, а не на файлы)
В общем проделал все шаги с помощь интерфейса PQ и перезаписал руками в "типо формулу"

По факту вопрос наверно закрыт. Но если всё таки кто-то сможет сделать какой-то пример которые будет работать как сборка для файлов (но только для листов). С редактированием в режиме реального времени. Будет шикарно.

sotnikov,
Да по факту работает и делает то что нужно. Но беда в том, что сложно для понимания.

Тоесть например в основной таблице у меня 2 верхние строки лишнии. И в обычной ситуации ты знаешь как что сделать, а тут знаний не хватает и просто наугад пытаешься править.

В связи с этим всётаки вопрос, реально ли прийти к ситуации когда сделан пример по аналогии с "Создать запрос из папки".
Чтобы можно было редактировать лист обычными средствами PQ и эти действия повторялись на других листах?

Я думаю это можно сделать но вам однозначно нужно приложить пример файла.
В котором указать какие данные первоначальные и какие хотите получить в итоге.
Условно 5-10 строчек будет достаточно.

Если последние числа всегда имеют 18 знаков то на вскидку что-то типо "=ПРАВСИМВ(A1;18)"

Ну и тему наверно поменять на "Excel извлечь часть текста из ячейки с помощью формулы"
Ктстате говоря таких тем я думаю очень много.

В Power Query есть шикарная история, когда ты нажимаешь "Создать запрос из папки" и выбираешь объединить файлы , PQ создает "Пример файла" и ниже "как-бы готовый запрос из всех файлов отредактированных как в примере"
Но если в "Примере" несколько листов и перед объединением этих листов, каждый из них надо отредактировать, при условии что листы разные. Возникает вопрос как это сделать.
Я точно знаю что можно. Но знаний как сделать не хватает. Поиском тоже не получилось найти, либо находится похожее но другое, либо я неправильно запрос делаю.

В общем суть такая есть файл в нем условно от 3 до 5 листов (всегда по разному)
На будущее представим что каждый из этих файлов называется Январь,Февраль,Март ит.д.

Листы друг на друга похожи, но все таки разные (тоесть условно это список товаров и шапка из 2 строк. Но длина и очередность колонок в этой шапке, на каждом листе разные)
Однако каждый из этих листов по отдельности можно привести к одной и той-же структуре и в итоге объединить в 1 таблицу.

Я думаю все знают эту систему когда мы получаем список всего что есть в книге, фильтруем его и остается колонка наименование и данные ([Data])
А дальше по идее мы разворачиваем данные и получаем одну единую таблицу.

"Хотел вставить скрин но не получилось"
Ну я думаю вы представляете это обычное, первоначальное окно Power Query, первый шаг "источник", который отображает всё что есть в книге, листы, диапазоны, умные таблицы, фильтры, и тд

Но это в том случае когда на всех листах шапка одинаковая.
А когда шапка разная надо каждый лист по очереди преобразовать (одим и тем-же алгоритмом)
И уже потом разворачивать.
Собственно вопрос как это сделать.
Грубо говоря как PQ создает "пример" для нескольких файлов из папки, также нужно создать "пример" для нескольких листов одного и того-же файла.

Описание не мой конёк, приложил файл. Но и файл в этот раз тоже кривовато вышел.

Alex,
Похоже работает отлично. Честно говоря пока что в полную силу (на большом массиве данных) протестировать нет возможности.
Но на первый взгляд работает шикарно.
Большое спасибо.

Еще небольшие уточнение,
1 Для чего "Календарь" выведен в отдельную таблицу (общими фразами чтобы понять суть)
2 Теоретически я же могу обойтись без календаря?
3 Наличие или отсутствие "Календаря" как-то влияет на скорость работы формулы?
4 Если предположить что "Календарь" необходим и я решу его добавить. Но у меня нет Power Pivot и модель данных я редактировать не могу.
  Я же могу тогда просто очередную "умную таблицу" с календарем закинуть и её использовать в своде (создав связь через кнопку "отношения" на св таб)
  Будет ли вообще в в таком календаре смысл?

Появилась проблема нужно в сводной таблице определить "Остатки на начало" и "остатки на конец"
К сожалению формул на DAX не знаю "Вообще" никак

По сути в таблице нужно определить остаток топлива на начало в разрезе каждой машины и общий по группам и периодам.
Возможно я вообще не правильно начал действовать и решение можно сделать по другому.
Готов к любым подсказкам (но к сожалению решения через макросы, Power Query, формулы массива в текущем варианте не подходят)
На счет PQ подумать можно но пока что только в образовательных целях.
По факту есть только формулы и сводная по модели.
Если всё таки делать через меру на DAX то её желательно составить так чтобы она "минимально" выдавала ошибку и рушила сводную.
Лучше канеш вообще без ошибок
И желательно составить её так чтобы она была понятной, чтобы я мог её адаптировать под себя и соответственно поменять на "остатки на конец"

Формат данных именно такой дата в формате 01.01.2001 (без времени)
И дата повторяется одна и таже несколько раз. (тоесть дубли дат)
По факту адекватный критерий отбора этих дублей только один это "показания пробега"
На какой дате пробег меньше, та дата и есть первая

В тексте объясняю плохо (много воды лью). Вот пример в нем должно быть понятнее.
Там же мои попытки что то смастерить на DAX (вышло спорно)

А всё я похоже понял там где цифры 9 и 18 увеличить их пропорционально 3 раза.
Большое спасибо это то что надо

Вроде как работает а вроде как и не совсем работает.

К сожалению есть разные размеры 1200х1400х800
Странно канеш почему не получается просто заменить "х"
Я надеялся что этот вопрос давно решён.

А может быть тогда попробовать как-то вытащить числа между "х" (до/после/между и перемножить их) Это Реально?

Чисел всегда 3
"х" вроде везде один и тотже
пробелов вроде тоже нет
(но это не 100%)


Желательно не формулой массива

Хотя кстате сейчас пришла мысль, можно же воспользоваться "разделением по столбцам" (но это не автоматизация "=(" )

Достаточно сложноватый путь я надеялся что можно обойтись какой-нибуть простенькой формулой


В какой-то момент что-то идёт не так и в результате формула просто выдаёт первую цифру "500"

Может быть это потому что у меня excel 2016

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

Вроде должно быть всё просто, но что то не могу найти как сделать.
Есть ячейка в ней текст
500х700х300

Ставлю формулу
=ПОДСТАВИТЬ(A1;"х";"*";)
Получаю "Знач"
Дальше пытался присоединить сюда ДВссыл и правдами и неправдами присоединить знак "="
Всё тщетно

Что делаю не так? и как мне текст 500х700х300 заменить на формулу =500*700*300

К сожалению сейчас времени уже нет.
Накидал вариант как можно собирать на сводных таблицах.
Формулы канешно тоже написанные по дилетантски, наверняка можно проще и быстрее.

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

В синих столбцах формулы всё просчитывают автоматом их толька надо вниз на длину списка протащить
И потом сводная таблица вам выводит всё информацию в адекватном виде там можно и отсортировать.


Главная проблема в том что я так и не смог придумать формулу которая будет распределять штуки в комплекты, опираясь на фасовку.
Тоесть я распределил что на озон надо 200 болтов, на яндекс 300. Но уже внутри маркет плейсов эти 200 и 300 я не распределил по фасовкам =(
А без этого распределения вся работа насмарку. Ну может ктото другой подскажет как сделать.

Адекватных мыслей нет.
Но есть "какие-то мысли"

Например в основной таблице добавить колонку в неё вписать формулу и обновить сводную.
типо такой =ЕСЛИ(AA2>=3000;"более 3000";"менее 3000")
при желании её там дальше можно развить если внутри если. А в новых версиях там вроде есть ЕСЛИМН
И добавить ещё 1 срез (минус в том что диапазоны, фиксированные. Если вдруг вам захочется ценны от 367 до 422 то придётся переписывать формулу именно на эти диапазоны)

Ну и соответственно хз правильно ли будут отображаться ваши данные, надо проверять

Я так думаю в старом экселе вот так.
Если в УФ то обычная формула, а если в ячейку то как формула массива.

Ну у вас действительно всё немного запутанно.
Много доп вопросов возникает и непонятно как вы 2 лист делаете.
То есть вы второй лист строите на основе количества? а почему не на основе наименования? как вы вообще привязываетесь к количеству если оно одинаковое?


Всё что выше это риторические вопросы, давайте начнем с основного.

Вопрос 1: А в чем суть этой таблицы? у вас 2 листа
На 1 листе у вас получается 3 выгрузки из 3 маркетплейсов которые показывают ваши остатки верно?
И в конце у вас выгрузка поступившего товара верно?
И на втором листе вы получается распределяете поступивший товар по маркетплейсам? так?
Хоть что нибудь угадал?

Вопрос 2: Вам принципиально выводить информацию именно в таком виде как у вас сейчас? Готовы вообще переделать её?
может вам идти в сторону сводных таблиц?
Вопрос 3: Вы пишите у одного товара от 2 до 7 карточек. А почему карточек так много? они чем-то отличаются? там разные размеры товаров или это просто какой-то маркетинговый ход (типо один и тот же товар с разными картинками?)
Вообще важно както распределять товар в разрезе карточек? или достаточно просто в разрезе названия товара?
Вопрос 4: А у вас вообще сколько маркетплейсов всего? три? поэтому и "блоков" у вас три?

Вопрос 5: У меня к сожалению старая версия эксель, в ней нет функции "ЕСЛИМН" поэтому у меня формула не работает.
у вас в колонках Y+товар O+товар W+товар Y-итог O-итог W-итог в итоге получается количество равное количеству поступления из первого листа?

Да действительно на формате .xlsx работает.

Подскажите а на формате .xlsb возможно реализовать тоже самое?




И немного не в тему (а вообще могут быть какие-то "страшные" последствия, преобразования .xlsb в .xlsx и наоборот)

Реальный файл приложить не могу.
Состряпал болванку. Но мне кажется в данном случае в файле нет смысла, тк как вопрос фундаментальный.
Возможно ли вообще заставить PQ обращаться не к листу а к умной таблице на этом листе.
Excel 2016

Удаление строки
Базасвод1 = Источник{[Name="Базасвод"]}[Data]

Приводит вид, к табличке с названием листов.

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

В общем к сути дела
Вопрос такой "Можно ли, при создании запроса Power Query к другому файлу Excel, указать в качестве результата не весь лист(вкладку) а только диапазон умной таблицы находящейся на этой вкладке?"

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

У меня кстате похожая проблема, но у меня не 1 файл а 10 и более.

Если у вас файлов всего два

И если идти примитивным не автоматизированным путём
То просто скопировать одну таблицу под другую и сделать сводную

Вот тут мне подсказывают макрос (который копирует одну таблицу под другую (если с макросами все плохо то можно просто руками)

Тема с макросом

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

Если со сводными тож плохо то можно вместо сводной, сделать список уникальных значений и формулу сумм если или сумм если мн

В общем всё зависит от того чем вы умеете пользоваться

Пытался разобраться в том какое вообще задание, чето до меня с трудом дошло.

Я так понял что суть задания в том что надо объединить текст из колонки "содержание"
Проблема в том что сложно определить какие строки объединять.

К сожалению ни в формулах ни в PQ я не силён, но может както натолкну на мысли.

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

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

Хотя сейчас более внимательно посмотрел скрин выписки, там есть строки и с 3 символами (тогда вообще странно, по какому принципу перенос на строку тогда не ясно)