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
Media is too big
VIEW IN TELEGRAM
Выделяем только видимые ячейки
Длительность: 5 мин
Выделяем только видимые ячейки (разными способами), чтобы потом:
— скопировать только их (например, только итоги) и вставить на другой лист
— отформатировать их (применить заливку только к итогам, а не ко всем строкам)
— вставить только в эти ячейки формулы — например, чтобы изменить шаблонные заголовки промежуточных итогов.
Это же видео на Kinescope (доступно в России без ограничений)
И оно же на Youtube
Оно же и десятки других бесплатных видео на сайте
А для подписчиков на Sponsr есть расширенная версия этого урока, как и десятки других подробных видео (и каждую неделю новое!)
Длительность: 5 мин
Выделяем только видимые ячейки (разными способами), чтобы потом:
— скопировать только их (например, только итоги) и вставить на другой лист
— отформатировать их (применить заливку только к итогам, а не ко всем строкам)
— вставить только в эти ячейки формулы — например, чтобы изменить шаблонные заголовки промежуточных итогов.
Это же видео на Kinescope (доступно в России без ограничений)
И оно же на Youtube
Оно же и десятки других бесплатных видео на сайте
А для подписчиков на Sponsr есть расширенная версия этого урока, как и десятки других подробных видео (и каждую неделю новое!)
1👍13❤4
10 лайфхаков Excel: вы точно найдете тут что-то новое для себя!
Длительность: 12 мин
— Ссылка на несколько листов
— Мгновенное заполнение
— Быстрое удаление столбцов
— Выделение пустых ячеек или отличий по строкам
— Быстрая специальная вставка
— И другое!
Смотреть видео на Youtube
Или на сайте (доступно в России) — там есть и десятки других видеоуроков
Длительность: 12 мин
— Ссылка на несколько листов
— Мгновенное заполнение
— Быстрое удаление столбцов
— Выделение пустых ячеек или отличий по строкам
— Быстрая специальная вставка
— И другое!
Смотреть видео на Youtube
Или на сайте (доступно в России) — там есть и десятки других видеоуроков
YouTube
10 лайфхаков Excel: вы точно найдете что-то новое для себя
— Ссылка на несколько листов
— Мгновенное заполнение
— Быстрое удаление столбцов
— Выделение пустых ячеек или отличий по строкам
— Быстрая специальная вставка
— Повтор последнего действия
— И другое!
— Мгновенное заполнение
— Быстрое удаление столбцов
— Выделение пустых ячеек или отличий по строкам
— Быстрая специальная вставка
— Повтор последнего действия
— И другое!
❤15👍6
Горячие клавиши по понедельникам 🔥
Сегодня у нас все, что связано с именами.
Ctrl + F3 — это диспетчер имен. Можно посмотреть, какие именованные диапазоны у вас в книге есть. Удалять их, менять, создавать новые.
Когда имена уже имеются, их можно вставлять в формулу, нажимая F3 — откроется окно "Вставка имени", где будет список имен.
Ctrl + Shift + F3 — окно "Создать из выделенного". Это когда у вас есть заголовки в столбцах / строках и вы хотите данные сделать именованными диапазонами, используя эти заголовки в качестве имен. Удобно, чтобы массово присвоить имена, а не делать по одному.
Сегодня у нас все, что связано с именами.
Ctrl + F3 — это диспетчер имен. Можно посмотреть, какие именованные диапазоны у вас в книге есть. Удалять их, менять, создавать новые.
Когда имена уже имеются, их можно вставлять в формулу, нажимая F3 — откроется окно "Вставка имени", где будет список имен.
Ctrl + Shift + F3 — окно "Создать из выделенного". Это когда у вас есть заголовки в столбцах / строках и вы хотите данные сделать именованными диапазонами, используя эти заголовки в качестве имен. Удобно, чтобы массово присвоить имена, а не делать по одному.
👍9❤4🔥3
Как найти работу в IT с помощью аналитики данных?
Когда чувствуете усталость от постоянного плаванья в Excel на работе, советуем обратить внимание на работу в настоящем IT. Оптимальным выбором для старта в АйТи является именно аналитика данных:
- вход в профессию проще, чем в разработку
- спрос на специалистов растет каждый год
- первое приглашение на работу можно получить всего за несколько месяцев обучения
При этом вы наверняка слышали про толпы желающих попасть в аналитику? Это не совсем так, в реальности конкуренция из хороших кандидатов очень маленькая. Поэтому если вы думаете о начале карьеры в IT и не хотите утонуть в изучении информации о профессии и о том как стоит выстроить обучение, для вас классная новость:
Ребята из Simulative проведут бесплатный вебинар вместе с Денисом Ивановым - опытным аналитиком, который помог более чем 100 новичкам войти в профессию.
Что будет на вебинаре:
🟠 Кто такие аналитики и как они меняют бизнес
🟠 На что влияет аналитик и почему это востребовано в любой сфере
🟠 Пошаговый план перехода - что учить, как избежать ошибок новичка
🟠 Навыки и инструменты, которые реально нужны, чтобы найти первую работу
🟠 Реальные кейсы трудоустройства - что делать, чтобы трудоустроиться быстро
🕖 В конце вебинара все зрители получат бонус - инструмент, который поможет ускорить рост в аналитике.
😶 Регистрируйтесь на бесплатный вебинар
Когда чувствуете усталость от постоянного плаванья в Excel на работе, советуем обратить внимание на работу в настоящем IT. Оптимальным выбором для старта в АйТи является именно аналитика данных:
- вход в профессию проще, чем в разработку
- спрос на специалистов растет каждый год
- первое приглашение на работу можно получить всего за несколько месяцев обучения
При этом вы наверняка слышали про толпы желающих попасть в аналитику? Это не совсем так, в реальности конкуренция из хороших кандидатов очень маленькая. Поэтому если вы думаете о начале карьеры в IT и не хотите утонуть в изучении информации о профессии и о том как стоит выстроить обучение, для вас классная новость:
Ребята из Simulative проведут бесплатный вебинар вместе с Денисом Ивановым - опытным аналитиком, который помог более чем 100 новичкам войти в профессию.
Что будет на вебинаре:
🕖 В конце вебинара все зрители получат бонус - инструмент, который поможет ускорить рост в аналитике.
Please open Telegram to view this post
VIEW IN TELEGRAM
❤2👎1
Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)
Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:
Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.
Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:
Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:
Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк:
Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:
=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.
Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:
НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:
--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк:
=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))👍9❤5🔥4
This media is not supported in your browser
VIEW IN TELEGRAM
Повторное применение фильтра
Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.
Просто нажимайте Ctrl + Alt + L . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).
Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.
Просто нажимайте Ctrl + Alt + L . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).
❤11👍5🍌2
Друзья, искренне рекомендую руководство по функции ФИЛЬТР.XML от Михаила Музыкина ⤵️В формате книги Excel ниже в отдельном его посте.
Это уже не такая новая функция, она доступна начиная с Excel 2013, и может заменить функции для работы с регулярками, которые доступны только в последней версии. Основное ее назначение — импорт XML из сети, но можно использовать ее и для магических манипуляций с текстовыми строками.
Это уже не такая новая функция, она доступна начиная с Excel 2013, и может заменить функции для работы с регулярками, которые доступны только в последней версии. Основное ее назначение — импорт XML из сети, но можно использовать ее и для магических манипуляций с текстовыми строками.
👍6❤4
Forwarded from Для тех, кто в танке (Mikhail Muzykin)
xpath_buch_tutorial.xlsx
26.8 KB
ФИЛЬТР.XML - xpath
#ExcelFunctions
Всем привет!
Все думаю знают, что я упоротый эксельщик, поэтому периодически буду складывать сюда полезности, про которые "в книжках не пишут".
Во вложении мой краткий мануал по использованию аргумента xpath в функции ФИЛЬТР.XML - это конечно не замена регулярок, но позволяет многое.
Пы.Сы. Не пугайтесь - M остается основной тематикой канала. Вечером обязательно будет привычный пост - сегодня разберем Folder.Contents.
Надеюсь, будет полезно.
Всех благ!
@buchlotnik
#ExcelFunctions
Всем привет!
Все думаю знают, что я упоротый эксельщик, поэтому периодически буду складывать сюда полезности, про которые "в книжках не пишут".
Во вложении мой краткий мануал по использованию аргумента xpath в функции ФИЛЬТР.XML - это конечно не замена регулярок, но позволяет многое.
Пы.Сы. Не пугайтесь - M остается основной тематикой канала. Вечером обязательно будет привычный пост - сегодня разберем Folder.Contents.
Надеюсь, будет полезно.
Всех благ!
@buchlotnik
👍10❤4🏆2
Функция LAMBDA в Google Таблицах — видео
(для Excel — нового — тоже актуально, ибо принципы работы максимально похожи)
— Зачем нужна LAMBDA
— MAP и столбец / двумерный массив / несколько массивов
— BYROW: обработка строк
— LAMBDA vs старые формулы массива
— MAP + INDIRECT: обработка данных с нескольких листов по списку
Ссылка на таблицу со всеми примерами
Это мой кусок сегодняшнего вебинара на канале "Google Таблицы".
Там были технические сложности, так что перезаписал с нуля с хорошим звуком. Даже больше, чем было)
Наслаждайтесь:
Kinescope (доступно в России)
Ютуб
(для Excel — нового — тоже актуально, ибо принципы работы максимально похожи)
— Зачем нужна LAMBDA
— MAP и столбец / двумерный массив / несколько массивов
— BYROW: обработка строк
— LAMBDA vs старые формулы массива
— MAP + INDIRECT: обработка данных с нескольких листов по списку
Ссылка на таблицу со всеми примерами
Это мой кусок сегодняшнего вебинара на канале "Google Таблицы".
Там были технические сложности, так что перезаписал с нуля с хорошим звуком. Даже больше, чем было)
Наслаждайтесь:
Kinescope (доступно в России)
Ютуб
❤10🔥7👏3