Как объединять несколько таблиц в Excel?
Вот основные варианты:
1 Формулы
2 Объединение (merge) в Power Query
3 Связи в модели данных Power Pivot
А также их преимущества и недостатки — вторая таблица из книги "Современная аналитика данных в Excel" Джорджа Маунта
Вот основные варианты:
1 Формулы
2 Объединение (merge) в Power Query
3 Связи в модели данных Power Pivot
А также их преимущества и недостатки — вторая таблица из книги "Современная аналитика данных в Excel" Джорджа Маунта
❤19👍6
This media is not supported in your browser
VIEW IN TELEGRAM
Найти и заменить: меняем форматы, а не значения
У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.
И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.
Вызываем окно "Найти и заменить" — Ctrl + H
Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть
Нажимаем "Заменить все". Готово!
У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.
И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.
Вызываем окно "Найти и заменить" — Ctrl + H
Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть
Нажимаем "Заменить все". Готово!
👍25❤8👏3
This media is not supported in your browser
VIEW IN TELEGRAM
Слегка экзотическое применение формул массива: защищаем строки от удаления
Выделяете много строк где-нибудь далеко справа.
И вводите в них формулу массива, возвращающую ничего
Или какое-нибудь число, как в видео (там ноль) (чтобы потом не искать пустые ячейки с формулой, если захотите удалить)
И вводите формулу сочетанием клавиш Ctrl + Shift + Enter.
Это старые формулы массива, работающие во всех версиях.
Готово — теперь удалить отдельную строчку будет нельзя, ибо в этих строках формула массива, которую можно удалить только целиком.
Выделяете много строк где-нибудь далеко справа.
И вводите в них формулу массива, возвращающую ничего
=""
Или какое-нибудь число, как в видео (там ноль) (чтобы потом не искать пустые ячейки с формулой, если захотите удалить)
=0
И вводите формулу сочетанием клавиш Ctrl + Shift + Enter.
Это старые формулы массива, работающие во всех версиях.
Готово — теперь удалить отдельную строчку будет нельзя, ибо в этих строках формула массива, которую можно удалить только целиком.
🔥17👍7❤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)))🔥12❤5
Вот это хорошая новость, друзья! ⬇️
Когда я готовил курс по визуализации, собирал и читал все более-менее значимые книги по теме, вот эта — одна из лучших, и здорово, что она выйдет на русском.
В канале у Александра можно еще поучаствовать в опросе по поводу приемлемой цены на новую книгу, возможно, и она выйдет на русском тоже.
Когда я готовил курс по визуализации, собирал и читал все более-менее значимые книги по теме, вот эта — одна из лучших, и здорово, что она выйдет на русском.
В канале у Александра можно еще поучаствовать в опросе по поводу приемлемой цены на новую книгу, возможно, и она выйдет на русском тоже.
❤9👍1
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), жмете на кнопку Оформить предзаказ на книги (если у вас обновленная версия бота, кнопка будет называться Уведомить о выходе книги, т.к. хочется подчеркнуть, что никаких денег за предзаказ вы не платите) и выбираете нужную вам книгу. И тогда по выходу книги вам придет сообщение от моего бота.
Давайте все оформим уведомление на книгу, чтобы в издательстве поняли, как мы любим биай и хотим читать новые и новые книги!)) И ждите опроса по новой книге, а на следующей неделе будет новый анонс, в работе есть и вторая книга!
❤19
Горячие клавиши по понедельникам 🔥
Друзья, предлагаю внедрять несколько горячих клавиш в неделю. Сугубо Excel-ных или общеофисных. Все сотни сочетаний запомнить невозможно, не нужно — и мы не будем пытаться.
А вот если брать в работу несколько на неделю — можно через некоторое время запомнить довольно много.
На этой неделе три сочетания для быстрого ввода данных:
Скопировать значение из ячейки сверху
Ctrl + Shift + " (⌃ +⇧+ ")
Выбрать значение из раскрывающегося списка (это не проверка данных со списком — а просто появится список со всеми вариантами, которые есть в столбце над ячейкой)
Alt + ↓ (⌥ + ↓)
Мгновенное заполнение (заполнение по введенному вами шаблону на основе данных из всех смежных столбцов)
Ctrl + E (и на Маке Ctrl + E)
Друзья, предлагаю внедрять несколько горячих клавиш в неделю. Сугубо Excel-ных или общеофисных. Все сотни сочетаний запомнить невозможно, не нужно — и мы не будем пытаться.
А вот если брать в работу несколько на неделю — можно через некоторое время запомнить довольно много.
На этой неделе три сочетания для быстрого ввода данных:
Скопировать значение из ячейки сверху
Ctrl + Shift + " (⌃ +⇧+ ")
Выбрать значение из раскрывающегося списка (это не проверка данных со списком — а просто появится список со всеми вариантами, которые есть в столбце над ячейкой)
Alt + ↓ (⌥ + ↓)
Мгновенное заполнение (заполнение по введенному вами шаблону на основе данных из всех смежных столбцов)
Ctrl + E (и на Маке Ctrl + E)
❤23👍11🔥9
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 страниц большого формата
Ссылка на Амазон
👍22❤11🔥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.
🔥19❤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 (и не только): все нюансы и правила для новичков, новые функции, файлы с данными для практики и готовыми примерами
❤24👍3
Горячие клавиши по понедельникам 🔥
Сегодня играем на Alt'е!
Просто Alt (или F10 или /) дает доступ к ленте и панели быстрого доступа с клавиатуры.
На Mac — Option, но только в новом Excel, раньше этой опции на маковских Excel'ях не было вообще, увы.
Чем это ценно? Тем, что вы можете создать свое собственное и довольно короткое сочетание для любой команды. И это единственная возможность создавать свои сочетания (если без макросов). Для этого достаточно добавить ее на панель быстрого доступа — и готово, сочетание Alt + цифра для этой команды уже есть.
Подробнее про добавление любой команды на панель быстрого доступа — по ссылке
*
Ну а Alt + F11 или F12 открывает один из редакторов — макросы или Power Query. На чьей стороне вы?
*
Бонус: Alt + F1 — создание диаграммы (внедренной, а если нужно на отдельном листе — просто F11).
Сегодня играем на Alt'е!
Просто Alt (или F10 или /) дает доступ к ленте и панели быстрого доступа с клавиатуры.
На Mac — Option, но только в новом Excel, раньше этой опции на маковских Excel'ях не было вообще, увы.
Чем это ценно? Тем, что вы можете создать свое собственное и довольно короткое сочетание для любой команды. И это единственная возможность создавать свои сочетания (если без макросов). Для этого достаточно добавить ее на панель быстрого доступа — и готово, сочетание Alt + цифра для этой команды уже есть.
Подробнее про добавление любой команды на панель быстрого доступа — по ссылке
*
Ну а Alt + F11 или F12 открывает один из редакторов — макросы или Power Query. На чьей стороне вы?
*
Бонус: Alt + F1 — создание диаграммы (внедренной, а если нужно на отдельном листе — просто F11).
❤17👍10🔥8🍌1
Список с отдельными строками для каждой даты каждого этапа — Power Query
И та же задача, что в предыдущем посте, через Power Query.
Формула на языке M:
За "Источником" скрывается запрос к таблице с этапами и датами начала / окончания (сверху на скриншоте)
Table.AddColumn — добавляем столбец, в котором будут списки всех дат.
Списки дат генерируем с помощью функции List.Dates.
Этот столбец затем разворачиваем, чтобы для каждой даты в списке получить отдельную строку — функция Table.ExpandListColumn.
И если нужно удалить исходные столбцы с началом и окончанием — используем Table.RemoveColumns.
P.S.
Файл с двумя вариантами решения по ссылке. Формулу вы в нем найдете сами (голубая ячейка), а для попадания в Power Query можно нажать Alt + F12.
И та же задача, что в предыдущем посте, через Power Query.
Формула на языке M:
= Table.RemoveColumns( Table.ExpandListColumn(Table.AddColumn(Источник, "Дата", each List.Dates(Date.From([Начало]),Duration.Days([Окончание]-[Начало])+1,#duration(1,0,0,0))), "Дата"), {"Начало", "Окончание"})За "Источником" скрывается запрос к таблице с этапами и датами начала / окончания (сверху на скриншоте)
Table.AddColumn — добавляем столбец, в котором будут списки всех дат.
Списки дат генерируем с помощью функции List.Dates.
Этот столбец затем разворачиваем, чтобы для каждой даты в списке получить отдельную строку — функция Table.ExpandListColumn.
И если нужно удалить исходные столбцы с началом и окончанием — используем Table.RemoveColumns.
P.S.
Файл с двумя вариантами решения по ссылке. Формулу вы в нем найдете сами (голубая ячейка), а для попадания в Power Query можно нажать Alt + F12.
👍10❤3
Как разделить текст по нескольким разделителям?
Например, по косой черте и дефису, как в примере.
В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:
Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:
А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи:
или
Например, по косой черте и дефису, как в примере.
В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:
{"первый разделитель"; "второй"; ... }
Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:
"/-"
А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи:
=SPLIT(A2; "/-")
или
=SPLIT(A2; "/-"; 1)👍18❤6
Горячие клавиши по понедельникам 🔥
На этой неделе у нас сочетания для создания, закрытия, открытия и сохранения книг.
На что обращаем внимание:
— Если книга новая, то "Сохранить" и "Сохранить как" будут одним и тем же — сначала нужно сохранить книгу первый раз под каким-то именем, чтобы потом просто сохранять
— Для сохранения есть и еще одно сочетание — Alt + цифра (а какая — зависит от того, какой по счету у вас дискетка на панели быстрого доступа). Конечно, и любую другую команду, в том числе "Открыть" или "Создать" можно добавить на панель быстрого доступа, чтобы получить возможность вызывать команду по сочетанию Alt + цифра.
Как изменить шаблон новой книги (которая создается по Ctrl + N)?
Читайте здесь.
На этой неделе у нас сочетания для создания, закрытия, открытия и сохранения книг.
На что обращаем внимание:
— Если книга новая, то "Сохранить" и "Сохранить как" будут одним и тем же — сначала нужно сохранить книгу первый раз под каким-то именем, чтобы потом просто сохранять
— Для сохранения есть и еще одно сочетание — Alt + цифра (а какая — зависит от того, какой по счету у вас дискетка на панели быстрого доступа). Конечно, и любую другую команду, в том числе "Открыть" или "Создать" можно добавить на панель быстрого доступа, чтобы получить возможность вызывать команду по сочетанию Alt + цифра.
Как изменить шаблон новой книги (которая создается по Ctrl + N)?
Читайте здесь.
❤16👍9
This media is not supported in your browser
VIEW IN TELEGRAM
Версия совместимости функций
Во все времена разработчики Excel дорабатывали функции, если что-то работало не так — но обычно им приходилось создавать новую функцию, а не менять старую.
Ведь если изменить поведение старой функции, это затронет миллионы рабочих книг, в которых она уже работает в формулах.
Вот и появлялась ОКРВВЕРХ.МАТ (CEILING.MATH) в дополнение к ОКРВВЕРХ (CEILING).
А что теперь? Теперь есть два режима — поведение по умолчанию (вариант 1) и последняя версия функций.
Получается, что теперь разработчики смогут менять поведение существующих функций, исправляя какие-то проблемы. В старых рабочих книгах можно будет оставлять старое поведение функций. А в новых использовать новые функции.
Смотрим, как это уже происходит воочию на примере ДЛСТР / LEN, которая определяет число символов в текстовой строке.
В старом исполнении она считает один эмодзи за два символа (так называемая суррогатная пара в Unicode). Это технически верно, но мы воспринимаем эмодзи за один символ.
И теперь функция считает именно так — смотрим в прикрепленном видео на несколько секунд без звука.
Где находится новый переключатель?
Формулы — Параметры вычислений — Версия совместимости — Версия 1 / Версия 2
Formulas — Calculation Options — Compatibility Versions — Version 1 / Version 2
Во все времена разработчики Excel дорабатывали функции, если что-то работало не так — но обычно им приходилось создавать новую функцию, а не менять старую.
Ведь если изменить поведение старой функции, это затронет миллионы рабочих книг, в которых она уже работает в формулах.
Вот и появлялась ОКРВВЕРХ.МАТ (CEILING.MATH) в дополнение к ОКРВВЕРХ (CEILING).
А что теперь? Теперь есть два режима — поведение по умолчанию (вариант 1) и последняя версия функций.
Получается, что теперь разработчики смогут менять поведение существующих функций, исправляя какие-то проблемы. В старых рабочих книгах можно будет оставлять старое поведение функций. А в новых использовать новые функции.
Смотрим, как это уже происходит воочию на примере ДЛСТР / LEN, которая определяет число символов в текстовой строке.
В старом исполнении она считает один эмодзи за два символа (так называемая суррогатная пара в Unicode). Это технически верно, но мы воспринимаем эмодзи за один символ.
И теперь функция считает именно так — смотрим в прикрепленном видео на несколько секунд без звука.
Где находится новый переключатель?
Формулы — Параметры вычислений — Версия совместимости — Версия 1 / Версия 2
Formulas — Calculation Options — Compatibility Versions — Version 1 / Version 2
1❤10🔥5👍4
This media is not supported in your browser
VIEW IN TELEGRAM
Автозавершение функции в Power Query
Есть одна раздражающая вещь в PQ — это автозавершение функций
Вот написали вы Text..
Нашли в списке нужную вам функцию Text.Proper, допустим
Нажали Tab...
И получили
А-А-А-А-А 😵
И что еще печальнее, эта гадость все портит, когда у вас вложенные функции. Старая функция стирается.
Как быть?
Начинайте вводить с точки — как в прикрепленном видео (без звука).
Будет работать и в настраиваемом столбце, и в строке формул, и в расширенном редакторе.
Есть одна раздражающая вещь в PQ — это автозавершение функций
Вот написали вы Text..
Нашли в списке нужную вам функцию Text.Proper, допустим
Нажали Tab...
И получили
TextText.Proper
А-А-А-А-А 😵
И что еще печальнее, эта гадость все портит, когда у вас вложенные функции. Старая функция стирается.
Как быть?
Начинайте вводить с точки — как в прикрепленном видео (без звука).
Будет работать и в настраиваемом столбце, и в строке формул, и в расширенном редакторе.
🔥15
Кавычки в формулах
Вам нужно склеить текстовые строки и добавить к ним кавычки.
Просто кавычки обозначают границы текста в рамках формулы. То есть следующее выражение — это просто пустая текстовая строка:
А следующая формула вернет текст "Лемур" без кавычек:
А если вам нужны кавычки — есть два варианта — ввести пару внутри внешних кавычек:
Такая формула вернет слово "Лемур" в кавычках.
Или использовать функцию СИМВОЛ / CHAR (в VBA — Chr) и код кавычек 34. Следующая формула добавит кавычки вокруг текста из ячейки A2:
Вам нужно склеить текстовые строки и добавить к ним кавычки.
Просто кавычки обозначают границы текста в рамках формулы. То есть следующее выражение — это просто пустая текстовая строка:
=""
А следующая формула вернет текст "Лемур" без кавычек:
="Лемур"
А если вам нужны кавычки — есть два варианта — ввести пару внутри внешних кавычек:
="""Лемур"""
Такая формула вернет слово "Лемур" в кавычках.
Или использовать функцию СИМВОЛ / CHAR (в VBA — Chr) и код кавычек 34. Следующая формула добавит кавычки вокруг текста из ячейки A2:
=СИМВОЛ(34)&A2&СИМВОЛ(34)
❤16👍13