• Архив

    «   Май 2018   »
    Пн Вт Ср Чт Пт Сб Вс
      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 31      

Новая статья: Последнее слово

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



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

Новая статья: Личная Книга Макросов в Microsoft Excel

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



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

Новая статья: Диаграмма Ганта в Power Query

Эта статья родилась после вопроса одного из моих слушателей с тренинга "Скульптор Данных в Microsoft Excel с Power Query". Вопрос был о том, как из вот такой таблицы по проектам:


... получить вот такую:


Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, макросами - сложно, вручную - лениво, а вот бесплатная надстройка Power Query для Excel в такой ситуации как раз блещет всей своей мощью и красотой.

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

Новая статья: Анализ текста регулярными выражениями (RegExp) в Excel

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

Спектр задач, которые могут решать регулярки - очень широк. Разберем несколько примеров:
  • извлекаем город из адреса
  • номер телефона из описания контакта
  • ИНН из реквизитов контрагента
  • автомобильный номер
  • сумму и номер счета из банковской выписки и т.д.

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

Новая статья: Расчет средневзвешенного значения в Excel (формулами и в сводной)

Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...

Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.


Для пущей наглядности записал еще и видео вдогон:


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

Новая статья: Импорт курса биткойна в Excel через Power Query

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



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

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

Новая статья: Сравнение двух таблиц в Excel

Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
  • функцией ВПР (VLOOKUP) - классика жанра
  • с помощью сводной таблицы - хороший вариант для больших таблиц
  • с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:



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

Новая статья: Трансформация столбца в таблицу

Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:


Давайте разберем несколько способов сделать это быстро и красиво, а именно:
  • формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
  • через Power Query
  • макросом из надстройки PLEX
Читать статью полностью

Новая статья: Самая длинная победная серия

Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):



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

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

Новая статья: Совместное редактирование и Автосохранение в Excel 2016

На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:


Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом верхнему углу окна на панели быстрого доступа:


Наравне с плюсами, Автосохранение имеет и много неприятных минусов, о которых стоит рассказать подробно.

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

Новая статья: Подсветка заданных столбцов диаграммы

Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
  • все столбцы со значениями выше среднего
  • три самых больших (Top-3)
  • минимальное и максимальное значение
  • столбец, выбранный в выпадающем списке в ячейке листа и т.д.
На самом деле, все очень несложно




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

Новая статья: Генерация дубликатов строк

В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!


Сделать это можно двумя основными способами: при помощи макросов и через Power Query.

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

Новая статья: Секционные диаграммы

Если вам нужно визуализировать на диаграмме множество элементов сгруппированных по смыслу (товары по категориям, города по странам, месяцы за несколько лет и т.п.), то может здорово помочь секционная диаграмма:



Каждая группа здесь, как видите, отображается своим цветом автоматически.
Строить такую штуку, на самом деле, очень просто - главное правильно подготовить таблицу с исходными данными.

Читать статью полностью + видеоурок

Новая статья: Поиск совпадений в двух списках

Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов - подсчет количества и вывод совпадающих значений в двух списках. Подробно разберем три основных подхода для решения этой распространенной задачи:
  • формулы
  • макросы
  • Power Query


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

Новое видео: Сводная таблица с текстом в значениях в Power Query

По мотивам одноименной статьи записал видео решения задачи по созданию сводной с текстом через PowerQuery:



Читать статью целиком

Новая статья: Слияние двух списков без дубликатов

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



Давайте подробно разберем три принципиально разных способа:
  • удаление дубликатов
  • сводная таблица
  • формулу массива
  • через Power Query
Читать статью полностью

Новая статья: Визуализация движения по маршруту в Excel

Как наглядно отобразить движение по заданному маршруту с привязкой ко времени на географической карте в Excel? Подробный разбор на реальном примере поезда "Москва - Самара"




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

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

Здорово обновил содерижмое статьи про склейку текста по условию, когда нужно собрать из столбца только определенные ячейки и склеить их с заданным символом-разделителем:


Дописал еще два способа: реализацию этой задачи формулами и через Power Query. Плюс сделал видеоурок по всем способам:


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

Марафон "30 дней = 30 функций" #30: Выборочное суммирование по нескольким условиям с БДСУММ (DSUM)

Ну, вот мы с вами и добрались до последней 30-й статье в нашем марафоне "30 дней = 30 функций". По факту, получилось чуть больше и дней и функций, но это не со зла :)
А замыкающей у нас будет редкая, но весьма полезная функция БДСУММ (DSUM).

Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д. Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database). При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой И-ИЛИ условиям и подсчитывает сумму найденных записей по заданному столбцу.

Марафон "30 дней = 30 функций" #29: Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий - неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них - это функция ПРЕДСКАЗ (FORECAST), которая умеет считать прогноз по линейному тренду.

Марафон "30 дней = 30 функций" #28: Скрытый комментарий к формуле функцией Ч(N)

Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:



Простенько, но со вкусом. Пользуйтесь :)

Марафон "30 дней = 30 функций" #27: Поиск минимального и максимального по условию с МИНЕСЛИ и МАКСЕСЛИ

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Помогут новые функции, появившиеся в Excel 2016 - МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS):



Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами:
  • писать формулу массива
  • использовать функцию ДМИН
  • делать сводную таблицу и вытаскивать данные из нее
Все способы - в очередной статье нашего марафона-ликбеза.

Марафон "30 дней = 30 функций" #26: Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)

Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция РАБДЕНЬ (WORKDAY), которая умеет учитывать нерабочие дни в таких случаях:

Марафон "30 дней = 30 функций" #25: Подробный разбор на примерах функции ДВССЫЛ (INDIRECT)

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.



Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например:
  • для создания несбиваемых ссылок
  • для сбора данных с нескольких листов или книг
  • для суммирования по интервалу-окну на листе
  • для транспонирования формулами
  • ...
Подробный разбор всех нюансов + видео - в очередной статье нашего марафона-ликбеза по ключевым функциям Microsoft Excel.

Марафон "30 дней = 30 функций" #24: Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Марафон "30 дней = 30 функций" #23: Как склеивать текст функциями СЦЕП, СЦЕПИТЬ и ОБЪЕДИНИТЬ

Часто возникает необходимость склеивать текст из нескольких ячеек в одну длинную фразу. Причем иногда нужно дополнительно вставлять между фрагментами еще и заданный символ-разделитель (пробел, запятую и т.д.)
Microsoft Excel в подобной ситуации может помочь тремя функциями:
  • СЦЕП (CONCAT)
  • СЦЕПИТЬ (CONCATENATE)
  • ОБЪЕДИНИТЬ (TEXTJOIN)
Подробная статья про все варианты в очередной порции нашего марафона по функциям Excel.

Страницы: 1 | 2 | 3 | 4 | 5 | ... | 9 | След.