Декартово произведение (все комбинации значений) формулой (на новых функциях)
Сначала получаем первый список без пустых значений. Функция ПОСТОЛБЦ / TOCOL вернет его без пустых значений, то есть мы можем сослаться на весь столбец, но исключить пустые вторым аргументом функции (равным 1 для такого случая), чтобы предусмотреть появление новых значений в будущем.
Второй список сделаем строкой с помощью ПОСТРОК / TOROW.
Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон).
Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ.
Сначала получаем первый список без пустых значений. Функция ПОСТОЛБЦ / TOCOL вернет его без пустых значений, то есть мы можем сослаться на весь столбец, но исключить пустые вторым аргументом функции (равным 1 для такого случая), чтобы предусмотреть появление новых значений в будущем.
Второй список сделаем строкой с помощью ПОСТРОК / TOROW.
Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон).
Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ.
=ПОСТОЛБЦ(ПОСТОЛБЦ(первый список;1)&" "&ПОСТРОК(второй список;1))
👍15❤11
Media is too big
VIEW IN TELEGRAM
Задать указанную точность
Этот инструмент округлит все ваши числа с такой точностью, с какой они отображаются (отформатированы)
Так что использовать его надо очень осторожно и это скорее предупреждение, но вдруг кому-то пригодится 😸
Когда мы просто форматируем число — меняем его формат, например, снижая число знаков после запятой — мы никак не влияем на значение. И вычисления будут идти с точными значениями, а не отображаемыми.
А эта опция именно изменит числа, как если бы:
1 вы применили одну из функций рабочего листа для округления, например, ОКРУГЛ / ROUND, или
2 изменили тип данных в столбце в Power Query (на целое число, например)
В коротком видео на 2 минуты со звуком — демонстрация того, как это происходит и где найти в параметрах.
А именно — по этому адресу:
Файл — Параметры — Дополнительно — Задать указанную точность
Этот инструмент округлит все ваши числа с такой точностью, с какой они отображаются (отформатированы)
Так что использовать его надо очень осторожно и это скорее предупреждение, но вдруг кому-то пригодится 😸
Когда мы просто форматируем число — меняем его формат, например, снижая число знаков после запятой — мы никак не влияем на значение. И вычисления будут идти с точными значениями, а не отображаемыми.
А эта опция именно изменит числа, как если бы:
1 вы применили одну из функций рабочего листа для округления, например, ОКРУГЛ / ROUND, или
2 изменили тип данных в столбце в Power Query (на целое число, например)
В коротком видео на 2 минуты со звуком — демонстрация того, как это происходит и где найти в параметрах.
А именно — по этому адресу:
Файл — Параметры — Дополнительно — Задать указанную точность
🔥12👍5❤4
This media is not supported in your browser
VIEW IN TELEGRAM
Ctrl + левая кнопка мыши: быстрое копирование листов или объектов
Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.
Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.
И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥
Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.
Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.
И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥
🔥25👍11
This media is not supported in your browser
VIEW IN TELEGRAM
Добавляем к дате день недели и выделяем выходные
Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".
Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "
Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:
И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".
Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "
ДДД" (DDD). Это краткое обозначение дня недели ("Пн"). Для полного ("Понедельник") понадобится код "ДДДД" (DDDD).Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:
=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)
Тогда понедельнику будет соответствовать единица (иначе неделя будет начинаться с воскресенья, если пропустить второй аргумент функции), вторнику — двойка и так далее.И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
👍24❤10
This media is not supported in your browser
VIEW IN TELEGRAM
Быстрая фильтрация в сводной таблице
Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).
Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.
Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).
Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.
👍30❤6🔥1
Как объединять несколько таблиц в Excel?
Вот основные варианты:
1 Формулы
2 Объединение (merge) в Power Query
3 Связи в модели данных Power Pivot
А также их преимущества и недостатки — вторая таблица из книги "Современная аналитика данных в Excel" Джорджа Маунта
Вот основные варианты:
1 Формулы
2 Объединение (merge) в Power Query
3 Связи в модели данных Power Pivot
А также их преимущества и недостатки — вторая таблица из книги "Современная аналитика данных в Excel" Джорджа Маунта
❤18👍5
This media is not supported in your browser
VIEW IN TELEGRAM
Найти и заменить: меняем форматы, а не значения
У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.
И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.
Вызываем окно "Найти и заменить" — Ctrl + H
Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть
Нажимаем "Заменить все". Готово!
У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.
И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.
Вызываем окно "Найти и заменить" — Ctrl + H
Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть
Нажимаем "Заменить все". Готово!
👍24❤6👏3
This media is not supported in your browser
VIEW IN TELEGRAM
Слегка экзотическое применение формул массива: защищаем строки от удаления
Выделяете много строк где-нибудь далеко справа.
И вводите в них формулу массива, возвращающую ничего
Или какое-нибудь число, как в видео (там ноль) (чтобы потом не искать пустые ячейки с формулой, если захотите удалить)
И вводите формулу сочетанием клавиш Ctrl + Shift + Enter.
Это старые формулы массива, работающие во всех версиях.
Готово — теперь удалить отдельную строчку будет нельзя, ибо в этих строках формула массива, которую можно удалить только целиком.
Выделяете много строк где-нибудь далеко справа.
И вводите в них формулу массива, возвращающую ничего
=""
Или какое-нибудь число, как в видео (там ноль) (чтобы потом не искать пустые ячейки с формулой, если захотите удалить)
=0
И вводите формулу сочетанием клавиш Ctrl + Shift + Enter.
Это старые формулы массива, работающие во всех версиях.
Готово — теперь удалить отдельную строчку будет нельзя, ибо в этих строках формула массива, которую можно удалить только целиком.
🔥17👍5❤3
Выводим в ячейке выбранные в фильтре значения
Или не в фильтре, а просто видимые (не скрытые).
Логика такая:
1. Пользуемся функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) — или функцией АГРЕГАТ (AGGREGATE), чтобы найти видимые строки. Функция с аргументом 103 будет подсчитывать значения в видимых строках диапазона. Но в качестве диапазона мы будем давать ей каждую очередную ячейку в нашей таблице. Таким образом, для видимых ячеек результатом подсчета будет единица.
Почему мы делаем это не напрямую, как-то так:
А через MAP, обращаясь к каждому значению по очереди?
Потому что одна функция по всему столбцу выдаст количество видимых ячеек. А нам нужно получить единицу в каждой видимой строке. Словно мы протянули формулу и получили вспомогательный столбец. Который потом используется в функции ФИЛЬТР (FILTER). Только вот столбец этот виртуальный, на листе его нет — мы получаем его с помощью MAP для каждого значения из таблицы.
2. Далее мы отфильтруем столбец, который проверяем (в примере — канал продаж) по этим единицам, то есть по видимым строкам. Используем функцию FILTER (ФИЛЬТР).
3. И удалим дубликаты, так как даже если в таблице много сделок с каналом продаж "Выставка", нам нужно этот канал показать один раз. Так что используем УНИК (UNIQUE).
4. В конце объединяем все в одну текстовую строку, чтобы результат не вываливался вовне, а был в одной ячейке. Функция ОБЪЕДИНИТЬ (TEXTJOIN).
Или не в фильтре, а просто видимые (не скрытые).
Логика такая:
1. Пользуемся функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) — или функцией АГРЕГАТ (AGGREGATE), чтобы найти видимые строки. Функция с аргументом 103 будет подсчитывать значения в видимых строках диапазона. Но в качестве диапазона мы будем давать ей каждую очередную ячейку в нашей таблице. Таким образом, для видимых ячеек результатом подсчета будет единица.
Почему мы делаем это не напрямую, как-то так:
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(столбец;103)
А через MAP, обращаясь к каждому значению по очереди?
Потому что одна функция по всему столбцу выдаст количество видимых ячеек. А нам нужно получить единицу в каждой видимой строке. Словно мы протянули формулу и получили вспомогательный столбец. Который потом используется в функции ФИЛЬТР (FILTER). Только вот столбец этот виртуальный, на листе его нет — мы получаем его с помощью MAP для каждого значения из таблицы.
2. Далее мы отфильтруем столбец, который проверяем (в примере — канал продаж) по этим единицам, то есть по видимым строкам. Используем функцию FILTER (ФИЛЬТР).
3. И удалим дубликаты, так как даже если в таблице много сделок с каналом продаж "Выставка", нам нужно этот канал показать один раз. Так что используем УНИК (UNIQUE).
4. В конце объединяем все в одну текстовую строку, чтобы результат не вываливался вовне, а был в одной ячейке. Функция ОБЪЕДИНИТЬ (TEXTJOIN).
=ОБЪЕДИНИТЬ("разделитель";;УНИК(ФИЛЬТР(столбец;MAP(столбец;LAMBDA(a;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;a)))=1)))🔥11❤4
Вот это хорошая новость, друзья! ⬇️
Когда я готовил курс по визуализации, собирал и читал все более-менее значимые книги по теме, вот эта — одна из лучших, и здорово, что она выйдет на русском.
В канале у Александра можно еще поучаствовать в опросе по поводу приемлемой цены на новую книгу, возможно, и она выйдет на русском тоже.
Когда я готовил курс по визуализации, собирал и читал все более-менее значимые книги по теме, вот эта — одна из лучших, и здорово, что она выйдет на русском.
В канале у Александра можно еще поучаствовать в опросе по поводу приемлемой цены на новую книгу, возможно, и она выйдет на русском тоже.
❤8
Forwarded from Александр Гинько (автор и переводчик)
📈 Легендарная Большая книга дашбордов уже в переводе!!! Жмите кнопку, и вам придет личное уведомление об окончании перевода! В конце поста расскажу, как записаться на уведомление о выходе книги!
Друзья, делаю первый из двух анонсов и спешу сообщить о том, что уже перевел половину абсолютно мастхэвной книги для любого биайщика, название которой в оригинале знают все – The Big Book of Dashboards (ссылка на амазон: https://www.amazon.com/Big-Book-Dashboards-Visualizing-Real-World/dp/1119282713, можно посмотреть содержание и первую главу)! Это настоящий шедевр в отношении философии построения и дизайна дашбордов от Стива Векслера, Джеффа Шаффера и Энди Котгрива, без которого ни один визуальщик не должен выходить из дома!
Да, обычно я перевожу самые свежие книги, а этой книге уже целых 8 лет! Но я решился на это исключение для вас. Ведь рукописи не горят, и это как раз тот самый случай. Книга исключительно практическая, но без единого упоминания какой бы то ни было BI-системы. В ней показано великое множество реальных дашбордов, завоевывавших награды в разные годы, с подробнейшим разбором всех использованных в них приемов.
Это действительно потрясающая книга, которую я давно хотел перевести, но только недавно мы смогли получить на нее лицензию. Советую ее абсолютно всем специалистам и начинающим строителям дашбордов!
Кстати, в сентябре этого года вышла новая книга от того же трио авторов. Называется она Dashboards That Deliver. Многие подумали, что это второй том Биг Бука, но это не так, это совершенно новая книга (первое издание), просто обложка в стиле Биг Бука. Мы ооочень хотим перевести и эту книгу, и по поводу нее на этой неделе я создам опрос с вашим мнением и желанием ее приобрести. Кроме того, спрос на Большую книгу дашбордов непосредственно будет влиять на желание издательства ДМК Пресс перевести Dashboards That Deliver, так что советую покупкой этой книги проголосовать за перевод следующего шедевра этих авторов.
Завершить перевод книги я планирую в конце ноября (отслеживайте прогресс перевода ежедневно в моем боте по кнопке Прогресс перевода книг), а сейчас вы уже можете жамкнуть кнопку в моем боте, чтобы вам в телеграм пришло уведомление о выходе книги! Как это сделать: идете к моему боту (@alexanderginko_books_bot), жмете на кнопку Оформить предзаказ на книги (если у вас обновленная версия бота, кнопка будет называться Уведомить о выходе книги, т.к. хочется подчеркнуть, что никаких денег за предзаказ вы не платите) и выбираете нужную вам книгу. И тогда по выходу книги вам придет сообщение от моего бота.
Давайте все оформим уведомление на книгу, чтобы в издательстве поняли, как мы любим биай и хотим читать новые и новые книги!)) И ждите опроса по новой книге, а на следующей неделе будет новый анонс, в работе есть и вторая книга!
Друзья, делаю первый из двух анонсов и спешу сообщить о том, что уже перевел половину абсолютно мастхэвной книги для любого биайщика, название которой в оригинале знают все – The Big Book of Dashboards (ссылка на амазон: https://www.amazon.com/Big-Book-Dashboards-Visualizing-Real-World/dp/1119282713, можно посмотреть содержание и первую главу)! Это настоящий шедевр в отношении философии построения и дизайна дашбордов от Стива Векслера, Джеффа Шаффера и Энди Котгрива, без которого ни один визуальщик не должен выходить из дома!
Да, обычно я перевожу самые свежие книги, а этой книге уже целых 8 лет! Но я решился на это исключение для вас. Ведь рукописи не горят, и это как раз тот самый случай. Книга исключительно практическая, но без единого упоминания какой бы то ни было BI-системы. В ней показано великое множество реальных дашбордов, завоевывавших награды в разные годы, с подробнейшим разбором всех использованных в них приемов.
Это действительно потрясающая книга, которую я давно хотел перевести, но только недавно мы смогли получить на нее лицензию. Советую ее абсолютно всем специалистам и начинающим строителям дашбордов!
Кстати, в сентябре этого года вышла новая книга от того же трио авторов. Называется она Dashboards That Deliver. Многие подумали, что это второй том Биг Бука, но это не так, это совершенно новая книга (первое издание), просто обложка в стиле Биг Бука. Мы ооочень хотим перевести и эту книгу, и по поводу нее на этой неделе я создам опрос с вашим мнением и желанием ее приобрести. Кроме того, спрос на Большую книгу дашбордов непосредственно будет влиять на желание издательства ДМК Пресс перевести Dashboards That Deliver, так что советую покупкой этой книги проголосовать за перевод следующего шедевра этих авторов.
Завершить перевод книги я планирую в конце ноября (отслеживайте прогресс перевода ежедневно в моем боте по кнопке Прогресс перевода книг), а сейчас вы уже можете жамкнуть кнопку в моем боте, чтобы вам в телеграм пришло уведомление о выходе книги! Как это сделать: идете к моему боту (@alexanderginko_books_bot), жмете на кнопку Оформить предзаказ на книги (если у вас обновленная версия бота, кнопка будет называться Уведомить о выходе книги, т.к. хочется подчеркнуть, что никаких денег за предзаказ вы не платите) и выбираете нужную вам книгу. И тогда по выходу книги вам придет сообщение от моего бота.
Давайте все оформим уведомление на книгу, чтобы в издательстве поняли, как мы любим биай и хотим читать новые и новые книги!)) И ждите опроса по новой книге, а на следующей неделе будет новый анонс, в работе есть и вторая книга!
❤17
Горячие клавиши по понедельникам 🔥
Друзья, предлагаю внедрять несколько горячих клавиш в неделю. Сугубо Excel-ных или общеофисных. Все сотни сочетаний запомнить невозможно, не нужно — и мы не будем пытаться.
А вот если брать в работу несколько на неделю — можно через некоторое время запомнить довольно много.
На этой неделе три сочетания для быстрого ввода данных:
Скопировать значение из ячейки сверху
Ctrl + Shift + " (⌃ +⇧+ ")
Выбрать значение из раскрывающегося списка (это не проверка данных со списком — а просто появится список со всеми вариантами, которые есть в столбце над ячейкой)
Alt + ↓ (⌥ + ↓)
Мгновенное заполнение (заполнение по введенному вами шаблону на основе данных из всех смежных столбцов)
Ctrl + E (и на Маке Ctrl + E)
Друзья, предлагаю внедрять несколько горячих клавиш в неделю. Сугубо Excel-ных или общеофисных. Все сотни сочетаний запомнить невозможно, не нужно — и мы не будем пытаться.
А вот если брать в работу несколько на неделю — можно через некоторое время запомнить довольно много.
На этой неделе три сочетания для быстрого ввода данных:
Скопировать значение из ячейки сверху
Ctrl + Shift + " (⌃ +⇧+ ")
Выбрать значение из раскрывающегося списка (это не проверка данных со списком — а просто появится список со всеми вариантами, которые есть в столбце над ячейкой)
Alt + ↓ (⌥ + ↓)
Мгновенное заполнение (заполнение по введенному вами шаблону на основе данных из всех смежных столбцов)
Ctrl + E (и на Маке Ctrl + E)
❤21👍11🔥7
Power Query Beyond The User Interface: Solving Advanced Data Cleaning Problems Using M
Книга именно про M, а не интерфейс Power Query, то есть предполагается, что вы осуществляли какие-то относительно простые преобразования в PQ с помощью мышки (пользовательского интерфейса). Но не писали или почти не писали формулы и код самостоятельно и не особо ориентируетесь в списках-записях, функциях M и прочем.
Есть не самые банальные вещи, например про параметр GroupKind.Local в функции Table.Group и применение функций для группировки.
Простые, но адекватные примеры и последовательное объяснение с хорошими скриншотами с подписями-стрелочками.
Автор очень гордится придуманной им фразой "Кормите Power Query тем, что она ест", и повторяет ее в книге раз 15 :) Идея в том, что надо смотреть на то, какие данные ожидаются в качестве аргументов той или иной функции. Иногда это список, иногда таблица, иногда функция. Из-за этого иногда надо приводить к нужному типу с помощью простых или хитрющих манипуляций.
Я заказывал с Амазона, это нынче долго и дорого. В электронном формате на Амазоне не продается (Kindle), а купить на сайте издательства у меня не получилось. Не думаю, что вам есть смысл так возиться ради этой книги.
Вот что можно почитать/посмотреть взамен:
Есть изданная на русском "Power Query и язык M. Подробное руководство". Мощная книга. Но похожа на развернутую справку, местами тяжеловата, скучновата и скорее подойдет на роль справочника для опытных пользователей. Книга, о которой я рассказываю сегодня, доступнее, последовательнее, проще для новичка.
Книга с обезьянкой ("Приручи данные с помощью Power Query") и книга Николая Павлова "Скульптор данных". Они больше про интерфейс, но есть основы и языка M. И они куда круче других по содержательности и жизненным примерам.
Курс Михаила Музыкина "Power Query — язык M". Если перевариваете формат видео, то лучше ничего не найдете. Но про интерфейс и мышку ничего не будет :) Так что можно сначала прочитать и проработать одну из двух книг (Павлов или Пульс-Эскобар)
Ну а из этой я обязательно позже в формате текста / видео поделюсь парочкой примеров здесь.
228 страниц большого формата
Ссылка на Амазон
Книга именно про M, а не интерфейс Power Query, то есть предполагается, что вы осуществляли какие-то относительно простые преобразования в PQ с помощью мышки (пользовательского интерфейса). Но не писали или почти не писали формулы и код самостоятельно и не особо ориентируетесь в списках-записях, функциях M и прочем.
Есть не самые банальные вещи, например про параметр GroupKind.Local в функции Table.Group и применение функций для группировки.
Простые, но адекватные примеры и последовательное объяснение с хорошими скриншотами с подписями-стрелочками.
Автор очень гордится придуманной им фразой "Кормите Power Query тем, что она ест", и повторяет ее в книге раз 15 :) Идея в том, что надо смотреть на то, какие данные ожидаются в качестве аргументов той или иной функции. Иногда это список, иногда таблица, иногда функция. Из-за этого иногда надо приводить к нужному типу с помощью простых или хитрющих манипуляций.
Я заказывал с Амазона, это нынче долго и дорого. В электронном формате на Амазоне не продается (Kindle), а купить на сайте издательства у меня не получилось. Не думаю, что вам есть смысл так возиться ради этой книги.
Вот что можно почитать/посмотреть взамен:
Есть изданная на русском "Power Query и язык M. Подробное руководство". Мощная книга. Но похожа на развернутую справку, местами тяжеловата, скучновата и скорее подойдет на роль справочника для опытных пользователей. Книга, о которой я рассказываю сегодня, доступнее, последовательнее, проще для новичка.
Книга с обезьянкой ("Приручи данные с помощью Power Query") и книга Николая Павлова "Скульптор данных". Они больше про интерфейс, но есть основы и языка M. И они куда круче других по содержательности и жизненным примерам.
Курс Михаила Музыкина "Power Query — язык M". Если перевариваете формат видео, то лучше ничего не найдете. Но про интерфейс и мышку ничего не будет :) Так что можно сначала прочитать и проработать одну из двух книг (Павлов или Пульс-Эскобар)
Ну а из этой я обязательно позже в формате текста / видео поделюсь парочкой примеров здесь.
228 страниц большого формата
Ссылка на Амазон
👍21❤7🔥5
Получаем список с отдельными строками для каждой даты каждого этапа — одной формулой
Нужно получить то, что справа. Из того, что слева.
На сверхновых функциях это делается одной формулой:
Что тут происходит? Мы задаем функцию f.
На входе она получает один параметр — в нашей задаче это название этапа.
И делает следующее:
берет даты на столбец и на два правее от названия этапа (это делает функция СМЕЩ / OFFSET).
Превращает эти даты в последовательность дат от начала и до конца с помощью ПОСЛЕД / SEQUENCE.
Соединяет (ГСТОЛБИК / HSTACK) эти даты с названием этапа, повторенным столько раз, сколько в нем дат. Повторяем этап с помощью функции EXPAND / РАЗВЕРНУТЬ. Ну а число дат в последовательности считаем через старый добрый СЧЁТ / COUNT.
И далее эту функцию мы используем. В качестве первоначального аргумента в REDUCE мы отправляем заголовки, а далее накапливаем результат: пробегаемся по списку этапов, для каждого получаем таблицу с помощью написанной нами функции f, и добавляем полученные таблицы одна под другой с помощью ВСТОЛБИКа / VSTACK.
Нужно получить то, что справа. Из того, что слева.
На сверхновых функциях это делается одной формулой:
=LET(f; LAMBDA(x; LET(даты;ПОСЛЕД(СМЕЩ(x;0;2)-СМЕЩ(x;0;1)+1;;СМЕЩ(x;0;1));ГСТОЛБИК(РАЗВЕРНУТЬ(x;СЧЁТ(даты);;x);ТЕКСТ(даты;"ДД.ММ.ГГГГ"))));
REDUCE({"Название";"Дата"};Данные[Название];LAMBDA(acc;val; ВСТОЛБИК(acc; f(val)))))
Что тут происходит? Мы задаем функцию f.
На входе она получает один параметр — в нашей задаче это название этапа.
И делает следующее:
берет даты на столбец и на два правее от названия этапа (это делает функция СМЕЩ / OFFSET).
Превращает эти даты в последовательность дат от начала и до конца с помощью ПОСЛЕД / SEQUENCE.
Соединяет (ГСТОЛБИК / HSTACK) эти даты с названием этапа, повторенным столько раз, сколько в нем дат. Повторяем этап с помощью функции EXPAND / РАЗВЕРНУТЬ. Ну а число дат в последовательности считаем через старый добрый СЧЁТ / COUNT.
И далее эту функцию мы используем. В качестве первоначального аргумента в REDUCE мы отправляем заголовки, а далее накапливаем результат: пробегаемся по списку этапов, для каждого получаем таблицу с помощью написанной нами функции f, и добавляем полученные таблицы одна под другой с помощью ВСТОЛБИКа / VSTACK.
🔥17❤6
Курс "Магия табличных формул" подошел к отметке в 40 уроков 🖥
И каждую неделю добавляется новый!
Вот последний модуль про функции поиска и его уроки:
5.1 Великая и ужасная функция ВПР / VLOOKUP — 2 варианта поиска, символы подстановки, лишние пробелы в данных, разные форматы, поиск на разных листах
5.2 ПОИСКПОЗ / MATCH — старая функция и новый вариант XMATCH, ВПР + ПОИСКПОЗ, ИНДЕКС + ПОИСКПОЗ — вечная классика
5.3 ПОИСКПОЗ / MATCH: ищем числа — ищем ближайшее наименьшее число, наибольшее, просто ближайшее + про новые X-функции и старое доброе мгновенное заполнение
5.4 ПРОСМОТРX / XLOOKUP: все нюансы — поиск с символами подстановками и регулярками, в разных направлениях, получение столбцов и строк, горизонтальный и вертикальный поиск
5.5 Поиск по 2 и более условиям — три варианта на формулах и один через Power Query
5.6 Функция ПРОСМОТР / LOOKUP — поиск чисел с разными вариантами аргументов, поиск последнего значения в столбце (текст и числа), поиск слов в тексте
5.7 Функция ИНДЕКС / INDEX — часть 1 — поиск по строке и столбцу, ИНДЕКС, возвращающий строку/столбец целиком, ИНДЕКС по нескольким таблицам, ИНДЕКС как ссылка на ячейку в диапазоне
5.8 Функция ИНДЕКС / INDEX — часть 2 — случайный элемент из списка, самое частое текстовое значение с условием (например, какой товар чаще всего покупал каждый клиент), сумма по периоду, заданному в виде «01.06.24-01.09.25» в одной ячейке, склейка текста "от и до"
5.9 Функция СМЕЩ / OFFSET — Синтаксис функции, примеры, диаграмма с выбором периода и показателя
5.10 Функция СМЕЩ — часть 2. Универсальный поиск — поиск на разных листах с разной структурой (на каждом листе столбцы для поиска и столбцы с данными разные). На старых и новых (365) функциях.
https://sponsr.ru/excel_magic
И каждую неделю добавляется новый!
Вот последний модуль про функции поиска и его уроки:
5.1 Великая и ужасная функция ВПР / VLOOKUP — 2 варианта поиска, символы подстановки, лишние пробелы в данных, разные форматы, поиск на разных листах
5.2 ПОИСКПОЗ / MATCH — старая функция и новый вариант XMATCH, ВПР + ПОИСКПОЗ, ИНДЕКС + ПОИСКПОЗ — вечная классика
5.3 ПОИСКПОЗ / MATCH: ищем числа — ищем ближайшее наименьшее число, наибольшее, просто ближайшее + про новые X-функции и старое доброе мгновенное заполнение
5.4 ПРОСМОТРX / XLOOKUP: все нюансы — поиск с символами подстановками и регулярками, в разных направлениях, получение столбцов и строк, горизонтальный и вертикальный поиск
5.5 Поиск по 2 и более условиям — три варианта на формулах и один через Power Query
5.6 Функция ПРОСМОТР / LOOKUP — поиск чисел с разными вариантами аргументов, поиск последнего значения в столбце (текст и числа), поиск слов в тексте
5.7 Функция ИНДЕКС / INDEX — часть 1 — поиск по строке и столбцу, ИНДЕКС, возвращающий строку/столбец целиком, ИНДЕКС по нескольким таблицам, ИНДЕКС как ссылка на ячейку в диапазоне
5.8 Функция ИНДЕКС / INDEX — часть 2 — случайный элемент из списка, самое частое текстовое значение с условием (например, какой товар чаще всего покупал каждый клиент), сумма по периоду, заданному в виде «01.06.24-01.09.25» в одной ячейке, склейка текста "от и до"
5.9 Функция СМЕЩ / OFFSET — Синтаксис функции, примеры, диаграмма с выбором периода и показателя
5.10 Функция СМЕЩ — часть 2. Универсальный поиск — поиск на разных листах с разной структурой (на каждом листе столбцы для поиска и столбцы с данными разные). На старых и новых (365) функциях.
https://sponsr.ru/excel_magic
Site
Магия табличных формул. От A1 до LAMBDA | Sponsr
Видеоуроки по формулам Excel (и не только): все нюансы и правила для новичков, новые функции, файлы с данными для практики и готовыми примерами
❤13