11517
Николай Павлов с сайта PlanetaExcel.ru
Уж сколько я встречал (хороших и не очень) визуализации на тему план-факта и даже сам делал парочку, но всегда можно придумать что-то ещё. Например, вот такое как выше на картинке. Скромненько и со вкусом, да и делается за пару минут.
Ссылка на видеоурок на YouTube: https://www.youtube.com/watch?v=BzqiMODc440
Ссылка на статью и файл-пример, который можно скачать и использовать готовую диаграмму, подставив в неё ваши данные: https://www.planetaexcel.ru/techniques/4/27098/
Ловите обнову.
Выпустил досрочно-внеплановое обновление моей надстройки PLEX v2022.3 для Microsoft Excel - третье в этом году. В этой версии изменения коснулись, в основном, импорта курсов валют.
Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату дополнительно для 5 новых стран:
✅ Грузии
✅ Азербайджана
✅ Таджикистана
✅ Туркменистана
✅ Молдовы
Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.
Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.
Одновременно с добавлением новых стран (нац.банков) в диалоговом окне Курсы валют, были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:
=NBM(Дата;Валюта) - вставка курсов Нац.Банка Молдовы
=NBTJ(Дата;Валюта) - вставка курсов Нац.Банка Таджикистана
=NBTN(Дата;Валюта) - вставка курсов Нац.Банка Туркменистана
=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана
=NBG(Дата;Валюта) - вставка курсов Нац.Банка Грузии
У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов: ячейки с датой, для которой нужно получить курс (если не указана, то берётся текущая дата) и трёхбуквенного кода валюты в кавычках ("USD", "EUR" и т.д. если не указано, то по умолчанию берётся доллар США).
Плюс, как всегда, попутно исправлено несколько ошибок и выполнена незаметная снаружи, но важная внутренняя оптимизация.
Как обычно, скачать последнюю версию можно из раздела PLEX у меня на сайте. Только не забудьте после скачивания разблокировать файлы, а то заботливая Windows не даст запустить вам макросы.
Как говорится: "Хочешь рассмешить Бога - расскажи ему о своих планах" 😁 Прогнозы - дело неблагодарное, конечно, но - куда без них? Даже если вы далеки от темы и статистика для вас - темный лес, то вполне можно, тем не менее, составить вполне приличный, математически обоснованный прогноз по модели экспоненциального сглаживания, используя инструмент Лист Прогноза, появившийся в Excel начиная с 2016-й версии. Ловите свежий видеоурок и статью о том, как это сделать.
https://youtu.be/4po9_d5T1aQ
Подвезли из типографии второе издание "Скульптора данных в Excel c Power Query". По сравнению с первым изменения не принципиальные, ибо с момента его выпуска не так много времени прошло. Однако, всё же добавил пару-тройку глав по новым функциям и инструментам, исправлены опечатки и т.д. по мелочи.
Бумажную (чб) или электронную версию (цветной PDF), как обычно, можно купить-скачать-заказать тут.
Если вы уже покупали ранее электронную, то обновленный вариант вместе с примерами можно скачать из личного кабинета бесплатно 😉
P.S. Собака к книге не прилагается 😁
Приятная (надеюсь) новость - наконец-то приехало из типографии 2-е издание моей первой книги - сборника приёмов и готовых решений по всем типовым проблемам при работе с Microsoft Excel.
В новом издании ещё больше фишек и плюшек, актуализированы скриншоты и формулы на последнюю версию Excel, исправлены опечатки и наведён внешний блеск и лоск в виде модной вёрстки. Единственный минус - бумажная версия стала заметно дороже, ибо овёс нынче дорог бумага и печать сильно подорожали за последние пару месяцев.
Заказать бумажную (374 стр. ч/б формата А4) или купить-скачать электронную (цветной PDF с файлами примеров) версии можно тут.
БОНУС! Если вы раньше уже покупали PDF 1-го издания, то заново покупать 2-е издание не нужно - просто зайдите на сайт под своим логином и скачайте из личного кабинета обновленную версию книги и файлы-примеры к ней совершенно бесплатно 😉
Сегодня у нас 7 мая - День Радио 📻, если кто не в курсе. Хороший повод в очередном уроке научиться реализовывать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.
Статья и файл-пример https://www.planetaexcel.ru/techniques/11/24289/
Видео на YouTube https://www.youtube.com/watch?v=GfUu_GlqltE
Обычно срезы в сводной таблице используют для фильтрации, но путём небольших ухищрений (пара формул и крохотная доп.таблица) можно заставить кнопки среза переключать внутри сводной ВЫЧИСЛЕНИЯ.
Ловите новую статью и видеоурок про эту красоту:
Ролик на YouTube https://youtu.be/VL0nbPR-DEs
Статья в блоге https://www.planetaexcel.ru/techniques/8/24077/
Если выделить несколько ячеек на листе Excel, то внизу в строке состояния отобразится их количество, сумма, среднее и т.д. В последнем обновлении добавили простую, но гениальную вещь - теперь при щелчке мышью по этим итогам, они копируются в Буфер Обмена!
Но что делать тем, у кого пока нет и не предвидится такой версии? Или нужна не сумма? Или не всех ячеек, а только удовлетворяющих заданному условию?
Разбираем несколько вариантов решения в новой статье и видеоуроке 🤓 Просвещайтесь.
Статья https://www.planetaexcel.ru/techniques/3/23740/
Видео https://youtu.be/mtGbhDgPQU4
Хорошая новость, которых так сейчас не хватает: 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/). Так что если очень нужно, то можно уже сейчас 😉
Наконец-то дошли руки выложить записанное ещё в конце января видео и статью о том, как просчитывать в Excel различные моменты, связанные с игрой в лотереи. К моему удивлению, этой темой интересуется достаточно много людей (боюсь подумать, сколько денег они там оставляют). Так что с помощью различных функций и инструментов Excel будем считать вероятности выигрыша, оценивать частоты выпадения шаров, находить "холодные шары" и даже смоделируем полноценную лотерею с помощью несложного макроса. Можете даже придумать свою личную стратегию выигрыша и опробовать ее на реальных данных.
Если вы не игрок, то рекомендую посмотреть тоже, т.к. в процессе срывания покровов разбираем множество фишек и приемов, полезных и для рабочих задач.
https://www.planetaexcel.ru/techniques/3/23124/
Приветствую, коллеги! Тут буду 2-3 раза в неделю публиковать анонсы моих статей, тренингов и видеоуроков и всяческие полезные материалы (шпаргалки, приемы, лайфхаки) для всех, кто работает в Microsoft Excel, Power Query, Power Pivot, VBA, DAX и т.д.
P.S. Большая просьба не устраивать в комментах обсуждение своих рабочих задач и посторонних тем - буду чистить.
Вот, буквально, каждый раз очищая папку спама в Outlook, вспоминаю незлым тихим словом 😁 человека, который додумался до такого "перевода" (в оригинале это были Clean и Empty - по функциональности очень разные команды, если что).
Читать полностью…
Всем школьникам и студентам (и не только) посвящается 🙂 Как решить систему уравнений в Microsoft Excel на примере задачи про биту и мяч. Разбираем два принципиально разных подхода: с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ для систем линейных уравнений и подбором в надстройке Поиск решения (Solver) - для любых, в т.ч. и нелинейных, случаев.
Ловите, как обычно, ссылочку на видеоурок на YouTube и статью с файлом-примером для скачивания и экспериментов.
Отпуск закончен, назад к работе и любимому Excel :) Ловите короткую, но полезную статейку и видео про то, как вывести в ячейке листа путь к текущей книге с помощью формул. Такое часто бывает нужно при использовании макросов или запросов Power Query для сбора данных для параметризации пути к исходным файлам. Бонусом разбираем новую "обёрточную" функцию LET из Office 365, которая позволяет на лету создавать внутри вычиcлений переменные, чтобы упростить и ускорить громоздкие формулы-переростки.
Видео на YouTube https://www.youtube.com/watch?v=nnvCyIW_rnc + статья с файлом-примером на сайте
Возможно вы замечали, что подавляющее большинство возможностей и инструментов Microsoft Excel заточены, в первую очередь, под "вертикальные" таблицы, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Однако же с регулярной периодичностью слушатели на тренингах меня спрашивают: "Что делать если в работе попалась таблица с горизонтальной смысловой ориентацией?"
И, в частности, как в такой таблице реализовать, например, фильтрацию? То есть фильтровать по горизонтали - не привычные строки, а столбцы?
В Microsoft Excel такого инструмента нет, но когда нас это останавливало, правда? 😁 "Скрепки, скотч и суперклей - это наш метод!" (с)
Так что давайте разберем несколько костылей способов всё же реализовать такое, а именно: макросом на VBA, через Power Query и с помощью новой функции ФИЛЬТР (FILTER).
Ловите очередное видео на YouTube https://youtu.be/efK_jeE8vkQ и статью с файлом-примером для скачивания https://www.planetaexcel.ru/techniques/2/24245/
Microsoft Excel всегда с подозрением 😠 относился к файлам с макросами - особенно если они получены из какого-нибудь ненадежного источника типа интернета. И это вполне оправданно и понятно - макровирусы никто не отменял.
Однако с недавних (обновления марта-апреля 2022 года) пор всё стало более сурово 😡 - и макросы в скачанных файлах блокируются уже без возможности включить их обратно.
Вот два быстрых способа вернуть их к жизни - https://www.planetaexcel.ru/techniques/3/24383/
А вы знали, что горизонтальную прокрутку широкой таблицы в Excel очень удобно делать колёсиком мыши, но удерживая при этом Ctrl и Shift? 🤓
P.S. Эта фича появилась начиная с 2019 версии, если я правильно помню.
Провёл для сотрудников компании Xylem кастомный тренинг по спецпрограмме - строго по новым возможностям Office 365, на который они недавно перешли. Детально разобрали новые динамические массивы, их функции ( ФИЛЬТР, УНИК, СОРТ и т.д.), новую ПРОСМОТРХ вместо ВПР и другие радости.
Порадовал вопрос в конце: “зачем нам теперь Power Query?”
Люблю свою работу 😜
31 марта, кстати, Всемирный День Бэкапа. Очень надеюсь, что все вы - тёртые бойцы и уже давно дважды, а лучше трижды забэкапили все свои важные файлы в разные места (диски, облака, флешки). Если нет, то самое время сделать это хотя бы сегодня.
И ненавязчиво напоминаю, на всякий случай, что случайно закрытый без сохранения файл Excel, который вы пилили до этого полночи, в большинстве случаев можно восстановить командой Файл - Сведения - Управление книгой - Восстановить несохраненные книги 😉
Как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы. Новая статья + видеоурок https://www.planetaexcel.ru/techniques/24/23412/
Читать полностью…
Вот вам, для затравки, свежая шпаргалка по горячим клавишам в Excel - как раз на А4 с двух сторон, компактно и удобно.
Читать полностью…