Вот вам, для затравки, свежая шпаргалка по горячим клавишам в 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 версии, если я правильно помню.

😱 Еще не читал.

Сегодня у нас 7 мая - День Радио 📻, если кто не в курсе. Хороший повод в очередном уроке научиться реализовывать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.

Статья и файл-пример https://www.planetaexcel.ru/techniques/11/24289/
Видео на YouTube https://www.youtube.com/watch?v=GfUu_GlqltE

Microsoft Excel всегда с подозрением 😠 относился к файлам с макросами - особенно если они получены из какого-нибудь ненадежного источника типа интернета. И это вполне оправданно и понятно - макровирусы никто не отменял.

Однако с недавних (обновления марта-апреля 2022 года) пор всё стало более сурово 😡 - и макросы в скачанных файлах блокируются уже без возможности включить их обратно.

Вот два быстрых способа вернуть их к жизни - https://www.planetaexcel.ru/techniques/3/24383/

Приятная (надеюсь) новость - наконец-то приехало из типографии 2-е издание моей первой книги - сборника приёмов и готовых решений по всем типовым проблемам при работе с Microsoft Excel.

В новом издании ещё больше фишек и плюшек, актуализированы скриншоты и формулы на последнюю версию Excel, исправлены опечатки и наведён внешний блеск и лоск в виде модной вёрстки. Единственный минус - бумажная версия стала заметно дороже, ибо овёс нынче дорог бумага и печать сильно подорожали за последние пару месяцев.

Заказать бумажную (374 стр. ч/б формата А4) или купить-скачать электронную (цветной PDF с файлами примеров) версии можно тут.

БОНУС! Если вы раньше уже покупали PDF 1-го издания, то заново покупать 2-е издание не нужно - просто зайдите на сайт под своим логином и скачайте из личного кабинета обновленную версию книги и файлы-примеры к ней совершенно бесплатно 😉

Возможно вы замечали, что подавляющее большинство возможностей и инструментов Microsoft Excel заточены, в первую очередь, под "вертикальные" таблицы, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Однако же с регулярной периодичностью слушатели на тренингах меня спрашивают: "Что делать если в работе попалась таблица с горизонтальной смысловой ориентацией?"
И, в частности, как в такой таблице реализовать, например, фильтрацию? То есть фильтровать по горизонтали - не привычные строки, а столбцы?

В Microsoft Excel такого инструмента нет, но когда нас это останавливало, правда? 😁 "Скрепки, скотч и суперклей - это наш метод!" (с)
Так что давайте разберем несколько костылей способов всё же реализовать такое, а именно: макросом на VBA, через Power Query и с помощью новой функции ФИЛЬТР (FILTER).

Ловите очередное видео на YouTube https://youtu.be/efK_jeE8vkQ и статью с файлом-примером для скачивания https://www.planetaexcel.ru/techniques/2/24245/

Подвезли из типографии второе издание "Скульптора данных в Excel c Power Query". По сравнению с первым изменения не принципиальные, ибо с момента его выпуска не так много времени прошло. Однако, всё же добавил пару-тройку глав по новым функциям и инструментам, исправлены опечатки и т.д. по мелочи.

Бумажную (чб) или электронную версию (цветной PDF), как обычно, можно купить-скачать-заказать тут.

Если вы уже покупали ранее электронную, то обновленный вариант вместе с примерами можно скачать из личного кабинета бесплатно 😉

P.S. Собака к книге не прилагается 😁

Отпуск закончен, назад к работе и любимому Excel :) Ловите короткую, но полезную статейку и видео про то, как вывести в ячейке листа путь к текущей книге с помощью формул. Такое часто бывает нужно при использовании макросов или запросов Power Query для сбора данных для параметризации пути к исходным файлам. Бонусом разбираем новую "обёрточную" функцию LET из Office 365, которая позволяет на лету создавать внутри вычиcлений переменные, чтобы упростить и ускорить громоздкие формулы-переростки.

Видео на YouTube https://www.youtube.com/watch?v=nnvCyIW_rnc + статья с файлом-примером на сайте

Как говорится: "Хочешь рассмешить Бога - расскажи ему о своих планах" 😁 Прогнозы - дело неблагодарное, конечно, но - куда без них? Даже если вы далеки от темы и статистика для вас - темный лес, то вполне можно, тем не менее, составить вполне приличный, математически обоснованный прогноз по модели экспоненциального сглаживания, используя инструмент Лист Прогноза, появившийся в Excel начиная с 2016-й версии. Ловите свежий видеоурок и статью о том, как это сделать.
https://youtu.be/4po9_d5T1aQ

Выложил очередное видео и статью о том, как реализовать в сводных таблицах динамический топ-N, когда N самых крупных элементов отображаются сверху, а все остальные автоматически собираются в группу "Другие". Причем это самое N желательно изменять на лету и делать по ситуации Топ-3, Топ-5, Топ-10 и т.п.

По умолчанию, сводные в Excel так не умеют - нужно либо грузить всё в Power Pivot (Power BI) и писать сложные меры на DAX с параметрами, либо (наш метод!) немножко допилить исходники с помощью Power Query 😉

До меня наконец-то добрались обновления Office 365 с 15-ю новыми функциями, которые разработчики Excel анонсировали еще в марте. Так что в ближайших материалах ждите детальный разбор всех этих прелестей на живых примерах и рабочих задачах.

Первым номером идёт пара функций для склейки таблиц - VSTACK и HSTACK, получивших в русском варианте странные названия ВСТОЛБИК и ГСТОЛБИК (Горизонтальный Столбик, Карл!) Особенно красиво эти функции сочетаются с трёхмерными ссылками, позволяя моментально собирать в единое целое таблицы с любого количества листов. Просто песня.

Ловите видео и статью со всеми подробностями и (если у вас Office 365) трясите своих айтишников, чтобы накатили вам все обновления 😊

Допилил и выложил второе в этом году обновление своей надстройки PLEX для Microsoft Excel - версия 2022.2. Добавлено несколько "вкусных" инструментов, которые, я надеюсь, многие оценят:
◾️ Прицел - выделение цветом текущей строки и столбца в пределах рабочей области листа. Очень удобно при работе с большими таблицами на больших мониторах.
◾️ Экспорт диапазона ячеек в виде картинки (PNG, GIF, JPG, BMP), PDF, текстового файла (CSV и TXT), универсальных форматов (XML, JSON), гипертекста (HTML).
◾️ Свёртка - сводная таблица с текстом в области значений (аналог Pivot Column из Power Query).
◾️ Новые функции OutlineLevel и IndentLevel для определения уровней группировки и количества отступов в ячейке - очень полезно при создании иерархий.
◾️ Новые инструменты работы с текстом: удаление N-го слова, удаление всех символов кириллицы или латиницы, удаление всех символов из запрещенного списка.

Смотреть детальный список улучшений и качать новую версию уже можно тут.