Приветствую, коллеги! Тут буду 2-3 раза в неделю публиковать анонсы моих статей, тренингов и видеоуроков и всяческие полезные материалы (шпаргалки, приемы, лайфхаки) для всех, кто работает в Microsoft Excel, Power Query, Power Pivot, VBA, DAX и т.д.

P.S. Большая просьба не устраивать в комментах обсуждение своих рабочих задач и посторонних тем - буду чистить.

Вот вам, для затравки, свежая шпаргалка по горячим клавишам в Excel - как раз на А4 с двух сторон, компактно и удобно.

Наконец-то дошли руки выложить записанное ещё в конце января видео и статью о том, как просчитывать в Excel различные моменты, связанные с игрой в лотереи. К моему удивлению, этой темой интересуется достаточно много людей (боюсь подумать, сколько денег они там оставляют). Так что с помощью различных функций и инструментов Excel будем считать вероятности выигрыша, оценивать частоты выпадения шаров, находить "холодные шары" и даже смоделируем полноценную лотерею с помощью несложного макроса. Можете даже придумать свою личную стратегию выигрыша и опробовать ее на реальных данных.

Если вы не игрок, то рекомендую посмотреть тоже, т.к. в процессе срывания покровов разбираем множество фишек и приемов, полезных и для рабочих задач.
https://www.planetaexcel.ru/techniques/3/23124/

Как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы. Новая статья + видеоурок https://www.planetaexcel.ru/techniques/24/23412/

Хорошая новость, которых так сейчас не хватает: Microsoft выкатила мега-обновление, добавляющее в Excel аж 14 (!) новых функций для работы с динамическими массивами:

• TEXTBEFORE - выдергивает из исходного текста подстроку до заданного символа (типа ЛЕВСИМВ)
• TEXTAFTER - то же самое, но выдергивает текст после заданного символа
• TEXTSPLIT - это по сути текст по столбцам, но с помощью формулы - выдает все порезанные текстовые кусочки сразу массивом в разные ячейки
• VSTACK - склеивает два диапазона в один по вертикали (один под другим)
• HSTACK - то же самое, но склеивает по горизонтали (один рядом с другим)
• TOROW - трансформирует (вытягивает) двумерную таблицу в строку
• TOCOL - то же самое, но в столбец
• WRAPROWS - сворачивает строку в двумерную таблицу
• WRAPCOLS - сворачивает столбец в двумерную таблицу
• TAKE - извлекает из динамического массива N-е количество строк или столбцов от начала или конца, т.е. позволяет отрезать от массива нужный кусок
• DROP - то же самое, что предыдущая, но не извлекает, а удаляет часть массива
• CHOOSEROWS - извлекает строки из массива по их номерам
• CHOOSECOLS - извлекает столбцы из массива по их номерам
• EXPAND - расширяет массив до заданных размеров

Подробнее можно почитать в блоге у руководителя отдела разработки Excel в Microsoft - Joe McDaid https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066
Или можно посмотреть видео замечательной Leila Gharani с быстрым обзором всех этих функций (на английском, но там все и так понятно) - https://www.youtube.com/watch?v=VWcLM6_Q_00
Если же вы не знакомы с динамическими массивами в принципе, то можно глянуть мой ролик двухлетней давности https://www.planetaexcel.ru/techniques/2/9112/

Пока что это счастье доступно только инсайдерам (ограниченный круг добровольных тестировщиков) на версии Beta Channel, Version 2203 (Build 15104.20004), но через некоторое время, надеюсь, станет доступно и нам с вами. Как только доедет до меня - запишу для вас видео с подробным разбором, само-собой 👌

P.S. Не сочтите за рекламу, но некоторые из этих возможностей, кстати, уже давно есть в моей надстройке PLEX. Например, макрос для ресайза любых таблиц (https://www.planetaexcel.ru/plex/features/17/260/) и функции TextSplit, TextLeft, TextRight, TextMid (https://www.planetaexcel.ru/plex/features/23/). Так что если очень нужно, то можно уже сейчас 😉

31 марта, кстати, Всемирный День Бэкапа. Очень надеюсь, что все вы - тёртые бойцы и уже давно дважды, а лучше трижды забэкапили все свои важные файлы в разные места (диски, облака, флешки). Если нет, то самое время сделать это хотя бы сегодня.
И ненавязчиво напоминаю, на всякий случай, что случайно закрытый без сохранения файл Excel, который вы пилили до этого полночи, в большинстве случаев можно восстановить командой Файл - Сведения - Управление книгой - Восстановить несохраненные книги 😉

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

Разбираем несколько вариантов решения в новой статье и видеоуроке 🤓 Просвещайтесь.

Статья https://www.planetaexcel.ru/techniques/3/23740/
Видео https://youtu.be/mtGbhDgPQU4

Провёл для сотрудников компании Xylem кастомный тренинг по спецпрограмме - строго по новым возможностям Office 365, на который они недавно перешли. Детально разобрали новые динамические массивы, их функции ( ФИЛЬТР, УНИК, СОРТ и т.д.), новую ПРОСМОТРХ вместо ВПР и другие радости.

Порадовал вопрос в конце: “зачем нам теперь Power Query?”
Люблю свою работу 😜

Обычно срезы в сводной таблице используют для фильтрации, но путём небольших ухищрений (пара формул и крохотная доп.таблица) можно заставить кнопки среза переключать внутри сводной ВЫЧИСЛЕНИЯ.

Ловите новую статью и видеоурок про эту красоту:
Ролик на YouTube https://youtu.be/VL0nbPR-DEs
Статья в блоге https://www.planetaexcel.ru/techniques/8/24077/

А вы знали, что горизонтальную прокрутку широкой таблицы в Excel очень удобно делать колёсиком мыши, но удерживая при этом Ctrl и Shift? 🤓

P.S. Эта фича появилась начиная с 2019 версии, если я правильно помню.