• Архив

    «   Сентябрь 2020   »
    Пн Вт Ср Чт Пт Сб Вс
      1 2 3 4 5 6
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28 29 30        

Новый онлайн-курс "Функции Excel - самое нужное"


Ура! Сделал и выложил новый дистанционный онлайн-курс "Функции Excel - самое нужное".

На сегодняшний день в Microsoft Excel 477 встроенных функций. Это число немного пугает :)

На самом деле, для решения 90% задач, возникающих при работе в Excel,  достаточно знать одну десятую от этого количества. Этот курс как раз и  посвящен подробному разбору этих 50 ключевых функций, без которых сейчас  невозможно представить ни один современный проект в Microsoft Excel.

Спектр изучения - от СУММ до ВПР-ИНДЕКС-ПОИСКПОЗ, т.е. те самые 20%, которые решают 80% проблем.

В этом курсе вы научитесь:

  • Быстро и легко вводить, редактировать и комбинировать между собой любые функции Excel разными способами.
  • Склеивать, резать и зачищать текст с помощью удобных текстовых функций Excel.
  • Выполнять любые операции с датами, считать сроки, длительности, стаж или возраст в календарных или рабочих днях
    с помощью функций дат и времени.
  • Задавать проверки и сложные условия для реализации хитрой логики в ваших расчетах.
  • Использовать мощные функции поиска и подстановки (ВПР, ИНДЕКС, ПОИСКПОЗ...) для связывания таблиц.
Курс состоит из 6 глав, 22 уроков и обучающих видео общей длительностью  более 3 часов. К каждому уроку приложен текстовый конспект, файл-пример и  ДЗ для самостоятельной проработки всех навыков.


Посмотреть страницу курса

Книжка по DAX от ДМК Пресс

Коллеги из издательства "ДМК Пресс" еще раз обрадовали новостью - ушла в печать переведенная на русский язык монументальная книжка самых известных в мире DAX экспертов Альберто Феррари и Марко Руссо - "Подробное руководство по DAX". 700 страниц глубочайшей проработки всех функций языка DAX и нюансов их применения на практике для всех, кто использует в работе Power Pivot в Excel или Power BI:



Ссылка на книгу на сайте издательства

По секрету делюсь промокодом на 30% скидку "для своих", любезно предоставленным мне издательством (спасибо им большое):
DAX_Pavlov_2020

Новая статья: Массовая замена текста формулами

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


К сожалению, в MS Excel нет простых встроенных инструментов для решения этой проблемы, поэтому придется привлекать относительно сложные формулы массива (но Ctrl+Shift +Enter жать не придется).

В следующей статье разберём, как реализовать подобное с помощью макросов и хитрого запроса Power Query.

Читать статью полностью

Новая статья: Производственный календарь в Excel

Как при помощи Power Query сделать в Excel "вечный" производственный  календарь - автоматически обновляющийся список нерабочих дней за все  годы для использования в своих расчетах.


Читать статью полностью

Новая статья: Регулярные выражения (RegExp) в Power Query

Пару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...



Читать статью полностью >>>

Новая статья: Обманчивая простота функции ПОСЛЕД (SEQUENCE)



Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Обновление Office 365, которое подарило Microsoft Excel новый  вычислительный движок с поддержкой динамических массивов, также добавило  к нашему арсеналу 7 новых функций, заточенных специально для работы с  массивами. Про три самых важных функции: СОРТ (SORT), ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже писал, а в этой статье хотел разобрать ещё одну весьма приятную обнову - функцию ПОСЛЕД (SEQUENCE).
Эта функция предназначена для совершенно, на первый взгляд, банальной  задачи - генерации числовых последовательностей (рядов, арифметических  прогрессий) с определённым шагом и от заданного стартового значения. Звучит скучно, но не ведитесь на первое обманчивое впечатление - этой функции есть, чем вас удивить!

Читать статью полностью

Новая статья: ВПР и числа-как-текст

Разбор классической проблемы с функцией ВПР (VLOOKUP), которая отказывается находить числовые значения, если они отформатированы как текст:



Читать статью полностью

Новая статья: План-факт анализ в сводной с Power Pivot и Power Query

Большой комплексный пример: пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.



Читать статью полностью

Первая книга по Microsoft Power BI на русском!

В текущей ситуации острой нехватки позитивных новостей, эта новость - приятна вдвойне.

Коллеги из издательства "ДМК Пресс" сообщили, что ушла в печать и уже доступна для заказа первая на русском языке книга по Microsoft Power BI и Power Pivot - "Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel":



Это перевод известной в узких кругах книжки "Analyzing Data with Power BI and Power Pivot in Excel" двух самых достойных экспертов в этой теме: Альберто Феррари и Марко Руссо, авторов проекта www.sqlbi.com.

Я эту книгу, помнится, читал еще на английском пару лет назад, купив её на Amazon (а теперь с удовольствием перечитаю на русском еще разок) - однозначный мастхэв для любого аналитика или просто продвинутого пользователя Excel. Большинство описанных в книге инструментов прекрасно работают не только в модном нынче Power BI, но и в классическом Power Pivot.

Также коллеги из "ДМК Пресс" попросили меня написать предисловие к русскому изданию (что я с удовольствием и сделал) и поделились промо-кодом на 10% скидку DMKPRESS_2020, которым я, в свою очередь, делюсь с вами.

Так что с чистой совестью рекомендую всем, кто в теме. Лучший способ провести это смутное время с пользой.

Страница книги на сайте издательства

P.S. Уточнение для озабоченных - издательство не платило мне ни копейки за рекламу. Все вышесказанное - сугубо мое личное мнение и рекомендация.

Новое видео: Выделение дубликатов цветом

Обновил старую статью про выделение дубликатов в списке цветом с помощью условного форматирования и добавил к ней видео с разбором 3 способов:
  • быстрое выделение дубликатов-ячеек
  • правило УФ на основе формулы для выделения всей строки
  • правило УФ с формулой массива(!), когда нет ключевого столбца с уникальными значениями и нужно делать склейку всех значений в строке



Читать статью полностью

Обновление надстройки PLEX (версия 2020.1 от 30 марта 2020 г.)

Благодаря самоизоляции, закончил и выложил очередное обновление моей надстройки PLEX для Microsoft Excel.
Список новых "плюшек" следующий:

Выделятор
Встроенные возможности Microsoft Excel по выделению ячеек по заданным условиям (Главная – Найти и выделить – Выделить группу ячеек) весьма скромны. Начиная с этой версии, в надстройке PLEX в группе Диапазоны появился новый инструмент Выделятор с гораздо более мощными инструментами в этом плане.  
Идея его работы проста – выделять ячейки по определенным условиям:



Список вариантов по критериям внушительный:
  • Числа: больше, меньше, (не) равно, (не) между.
  • Текст: (не) равен, (не) содержит, (не) начинается с, (не) заканчивается на, соответствует шаблону (можно использовать символы подстановки *?#), (не) совпадает с любой ячейкой из указанного списка, (не) содержит любое слово из указанного диапазона, содержит любой символ из списка, содержит цифры, содержит кириллицу (А-Я), содержит латиницу (A-Z), содержит лишние пробелы, содержит непечатаемые знаки.
  • Даты: равна, до, после, (не) между, попадает на рабочий день, попадает на выходной, попадает на определённый день недели, попадает в заданный год, квартал или месяц.
  • Формат: содержит полужирный текст, курсив, подчеркивание, зачёркнутый текст, есть любая заливка, любой цвет шрифта кроме чёрного, заливка как в ячейке-образце, цвет шрифта как в ячейке-образце.
  • Другое: (не) пустые ячейки, ячейки с формулами, с константами, со ссылками на другие листы/книги, с ошибками, (не) защищенные, с заданным уровнем группировки, с гиперссылками.
При нахождении ячейки удовлетворяющей условию, можно выделить только её или же всю строку/столбец, где она находится.

Склонение ФИО по падежам

По многочисленным просьбам добавлена пользовательская функция FIO, которая умеет:
  • Выстраивать ФИО в правильном порядке, если в исходных данных они стоят неправильно.
  • Склонять ФИО в родительном и дательном падежах.
  • Выводить, при необходимости, сокращенную форму (Фамилия И.О.)
Синтаксис функции:
=FIO(NameAsText; NameCase; ShortForm)
где:
  • NameAsText – ячейка с исходным ФИО (полным или без отчества)
  • NameCase – символьный код падежа ("Д" или "D" – дательный; "Р" или "R" – родительный). По умолчанию – без преобразования.
  • ShortForm – логическое значение, обозначающее нужно ли (1 или ИСТИНА) или нет (0 или ЛОЖЬ) преобразовывать ФИО в сокращенную форму (Фамилия И.О.). По умолчанию, принимается за 0 (без преобразования).
В самом простом варианте эту функцию можно использовать для выстраивания ФИО в правильном порядке:

Со вторым аргументом можно преобразовать ФИО в требуемый падеж:

Третий (необязательный) логический аргумент позволит выводить ФИО в нужном падеже и, дополнительно, в сокращенной форме:

Безусловно, найдутся зубодробительные ФИО, где эта функция сработает некорректно, ибо русский язык велик и могуч. Однако, с 99,9% процентами типичных российских имен все работает вполне успешно (тестировал на 5000+ ФИО реальных людей).

Определение пола по имени

Добавлена функция с романтическим названием GetSex для определения пола человека по имени (полному или частичному ФИО).  Возвращает 1 (женский), -1 (мужской) или 0 (не определен):



Опять же, предусмотреть все возможные несуразные имена, которые родители умудряются давать своим детям, невозможно в принципе. Но на большинстве наших с вами соотечественников (в том числе СНГ) работает вполне надёжно.

Конвертация условного форматирования в статическое


Условное форматирование – красивая, но весьма ресурсоёмкая штука. Чтобы избежать торможения на больших таблицах, можно преобразовать условное форматирование в статическое. Внешний вид ячейки сохранится, но не будет больше меняться при изменении данных (и, соответственно, тормозить).
Обратите внимание на 3 момента:
  • Обратное преобразование невозможно.
  • Такие виды условного форматирования, как гистограммы или значки нельзя преобразовать в статику.
  • Из-за ограничений самого Excel эта функция работает только начиная с 2010-й версии Microsoft Excel.
Исправление УФ-ада

Если у вас есть таблица с несколькими правилами условного форматирования и вы, работая с этой таблицей:
  • удаляете в ней строки
  • меняете строки местами
  • вырезаете-копируете фрагменты таблицы
… то очень быстро вы придёте к тому, что иногда называют "адом условного форматирования", когда исходные правила, размножаясь и копируясь вместе с ячейками, начинают десятками плодиться на вашем листе.
Команда Исправить УФ-ад призвана легко и быстро исправлять такие ситуации. Просто выделите исходный диапазон с правилами условного форматирования и выберите эту команду в списке Формат. Все дублирующиеся правила будут удалены, а правила с первой строки будут распространены на весь диапазон:


Пользовательские стили

Стили в Microsoft Excel - это замечательный и удобный инструмент, которым мало кто (почему-то) пользуется. Стиль представляет собой совокупность числового формата и визуального форматирования (заливки, цвета шрифта и т.д.) для ячейки. Если вы используете в дизайне своих отчетов одни и те же приёмы оформления, то стили могут сильно упростить и ускорить всю вашу работу.
В группе Диапазоны – выпадающий список Форматы – добавлены команды для подгрузки в текущую книгу набора пользовательских стилей из файла styles.xlsx, входящего теперь в состав надстройки (лежит в папке PLEX). После выполнения загрузки на вкладке Главная в группе Стили добавляется больше двух десятков новых стилей для удобного и наглядного форматирования различных типов данных (проценты, план-факт, градусы, квадратные метры и т.д.):


Как выглядят эти стили на практике можно посмотреть в том же файле styles.xlsx, просто открыв его:


При необходимости вы можете сами в будущем добавлять в этот файл свои стили через команду Главная – Стили – Создать стиль ячейки.

Исправления ошибок и мелкие улучшения
  • Исправлена ошибка в инструменте Курс валют, связанная с редизайном сайта ЦБ РФ.
  • В инструменте Текст разделено удаление букв, чисел, знаков препинания и пробелов для большего удобства и наглядности.
  • Большинство веб-запросов (курсы валют, ставки, онлайн-справка и т.д.) переведены на безопасный протокол HTTPS.
  • Исправлена ошибка в функциях SumByCellColor, SumByFontColor, CountByCellColor, CountByFontColor, возникающая при некоторых экзотических оттенках используемых цветов.
  • Добавлена возможность выполнять вычисления с дробными числами в мини-калькуляторе.
  • Исправлено более 30 мелких ошибок, улучшена стабильность и быстродействие.
Как обычно, новую версию можно скачать тут.
Обо всех замеченных ошибках можно смело писать мне на почту или в личку на форуме.
Берегите себя и своих близких, оставайтесь (по возможности) дома и будьте здоровы!

Новая статья: Нечеткий текстовый поиск с Power Query

Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.


Читать статью полностью

Новая статья: Самый быстрый ВПР

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

Я давно хотел сделать подобный тест и вот, наконец, дошли руки. Некоторые результаты, признаюсь, были для меня самого совершенно удивительными (особенно динамические массивы):


Читать статью полностью

Новая статья: Функция ПРОСМОТРХ как наследник ВПР

Написал подробную статью про возможности новой функции ПРОСМОТРX (XLOOKUP), недавно появившейся в Excel из Office 365. Эту функцию Microsoft позиционирует, как замену и наследницу для классической ВПР (VLOOKUP). В чем фишки, плюсы и минусы "новой легенды" - мы подробно разберем в этой статье и я расскажу в видео:


Читать статью полностью

Календарь 2020 в Excel



Выкладываю традиционный новогодний минипрезент в виде календаря на 2020 год в Excel. Кроме, собственно, календаря там есть:
  • все официальные праздники, переносы рабочих дней и т.д.
  • количество рабочих и выходных дней и часов при различных типах рабочей недели
  • все памятные даты, "дни взятия Бастилии" и прочие поводы отметить :)
  • возможность планировать свои дела с шагом 1 час на любой день года
  • календарик-пинарик
Внутри всё на макросах, так что не забудьте их разрешить при открытии файла.


Качайте

Новогодние поздравления и игра "Жизнь" в Excel на макросах

Последний в этом году пост и видео хотелось бы сделать не в привычном формате видеоурока. А именно, совместить приятное с полезным и развлечь вас написанием у вас на глазах за 15 минут самой настоящей компьютерной игры на VBA в Excel - известной игры "Жизнь" (LIFE) британского математика Джона Конвея:



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

Берегите себя, ведь вы - лучшие!

С Новым годом!

Обновление статьи: Выпадающий список с пополнением

Кардинально переписал статью про выпадающий список в Excel, куда можно оперативно добавлять новые элементы, причем в обе стороны - и в справочник, и в сам список непосредственно. Добавил новые трюки с умной таблицей вместо сложных именованных диапазонов :) Плюс записал видеоурок - многим, что ни говори, проще один раз увидеть:



Просвещайтесь!

Обновление статьи: Список файлов в папке

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

Тренинги в Астане 26-28 ноября 2019 г.


В конце ноября буду вести два открытых тренинга в Астане по самым востребованным сейчас у продвинутых пользователей Excel инструментам - Power Query и Power Pivot. Если давно хотели выйти в работе с Excel на качественно новый уровень - добро пожаловать.

Организаторами выступают мои проверенные казахские коллеги из учебного центра CareerCenter.kz
Телефон для записи +7 (7122) 755-140

Приходите, будет интересно :)
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 26 | След.
Наверх