Представьте, что вам часто приходится фильтровать одну и ту же таблицу по одному или (что гораздо хуже) нескольким столбцам, в попытках найти там частичное совпадение с заданным текстом.
Само собой, можно включить классический фильтр через Данные - Фильтр (Data - Filter), развернуть выпадающий список в соответствующем столбце и ввести искомый текст в поле поиска, но ведь потом придётся куда-то скопировать результаты, очистить фильтрацию и повторить поиск ещё раз в другом столбце. А если ...
Как реализовать правильное хранение пользовательских примечаний к результатам запроса Power Query - так, чтобы при обновлении запроса эти примечания оставались напротив тех ячеек, куда их вводили
Используем для этого интересную технику в Power Query - запрос, ссылающийся сам на себя (в отличие от формул, где это приводит к циклическим ссылкам, в Power Query - это вполне ОК).
Читать статью полностью
Николай Павлов
При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.
Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое ...
Разбираем пять способов получить количество уникальных значений в исходном диапазоне данных:
формулой массива
макросом на VBA
удалением дубликатов
простой сводной
сводной по Модели Данных
//www.youtube.com/embed/vcjtwfjsSzc?feature=oembed
Читать статью полностью
Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:
Создать сначала одиночный веб-запрос к любой странице из интересующего списка
Создать параметр для года и внедрить его в запрос
Преобразовать ...
3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.
//www.youtube.com/embed/FSv2F6yWuSk?feature=oembed
Читать статью полностью
Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.
//www.youtube.com/embed/d1sTmh24Hwk?feature=oembed
Читать статью полностью
Если отбросить новомодный ChatGPT, то на втором месте по по количеству хайпа в моем личном рейтинге будет Power BI с его нечеловеческой красоты интерактивными дашбордами. Причем за последние пару лет количество людей интересующихся этой темой (в том числе среди пользователей Excel, которых я часто вижу) - выросло кратно. С одной стороны, это хорошо, ибо по мощи, гибкости, "всеядности" и красоте визуализаций у Power BI сейчас конкурентов практически нет. С другой стороны, у многих людей ...
Уже несколько лет в Google Sheets существует функция IMAGE, позволяющая вставлять в ячейки листа картинки по ссылке из интернета. Что, впрочем, вполне естественно, поскольку Google-таблицы изначально заточены под работу онлайн - им сам бог велел такую возможность использовать.
У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога - новой функции ИЗОБРАЖЕНИЕ (IMAGE), также ...
Обновил старую статью про создание диаграммы Парето в Microsoft Excel - добавил пару новых способов и видеоурок:
//www.youtube.com/embed/A8yGyUuxCd4?feature=oembed
Читать статью полностью
Думаю, про ChatGPT вам уже, как минимум, слышали, а может даже и попробовали использовать. Вопрос в том, насколько полезен этот ИИ может быть для простого пользователя Excel.
Пробуем переложить на ChatGPT создание формул и макросов на Visual Basic и смотрим, что из этого получится:
//www.youtube.com/embed/uor4N7w6xu0?feature=oembed
Читать статью полностью
Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:
лишние пробелы
использование английских букв похожих на русские и наоборот (русская "эс& ...
Уж сколько было копий сломано на тему визуализации план-факта, но это вечная история и каждый решает её по-своему. Предлагаю рассмотреть ещё один вариант в вашу копилку:
https://www.planetaexcel.ru/upload/medialibrary/754/qg3z17pt2h0qi2r7pv96iqo40b974p4j/plan-fact-chart1.png
Делается достаточно легко и почти не требует подготовительных действий с таблицей.
Читать статью + видеоурок
Срезы в сводных таблицах можно использовать не только классическим образом - для фильтрации исходных данных, но и для переключения между различными типами вычислений в области значений:
https://www.planetaexcel.ru/upload/medialibrary/e8e/pdolaj1cwmqz1mw5k18hezwy08od4l52/pivot-select-cals-animated.gif
Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.
Читать статью полностью
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только
выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)
В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.
//www.youtube.com/embed/mtGbhDgPQU4?feature=oembed
Читать статью полностью
Разбор типичных "граблей" - о том, как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
//www.youtube.com/embed/ik0KWTV10S8?feature=oembed
Читать статью полностью
С завидной регулярностью (а в последнее время - всё чаще) мне пишут люди с просьбами помочь в различных вычислениях, связанных с лотереями. Кто-то хочет реализовать в Excel свой секретный алгоритм подбора выигрышных чисел, кто-то - найти закономерности в выпавших номерах прошедших тиражей, кто-то - подловить организаторов лотереи на нечестной игре.
В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие ...
Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill) в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю ...
Хотите, чтобы диаграмма строилась только по данным из текущей строки, т. е. строки, куда вы поставили активную ячейку? Примерно так:
https://www.planetaexcel.ru/upload/medialibrary/ec1/68f8e9innbxy95ubq0cubvs9ev0wyy27/active-cell-chart-gif.gif
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Читать статью полностью
Подробный и пошаговый разбор весьма типовой задачи, когда в качестве входных данных имеем Excel-файл, где на одном из листов расположены друг под другом несколько таблиц с данными по продажам следующего вида:
https://www.planetaexcel.ru/upload/medialibrary/1da/1vfnb43catgl6vmqnbjb3s39m3tp2atj/collect-and-unpivot1.png
А на выходе нам нужно собрать данные из всех таблиц в одну плоскую нормированную таблицу, удобную для последующего анализа и построения сводной, т.е. вот в такую: https://www. ...
https://www.planetaexcel.ru/upload/medialibrary/d5b/zo3mul76fs1kzlk1r36zpeoimvn3b3tb/running-totals1.png
Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами:
формулами,
в сводной таблице,
в запросе Power Query.
//www.youtube.com/embed/6TWvTQoMlbE?feature=oembed
Читать статью полностью
Подробный разбор новой оберточной суперфункции LAMBDA, недавно добавленной в Excel. Её синтаксис, варианты применения, нюансы и особенности - от простых формул до динамических массивов и рекурсии:
https://www.planetaexcel.ru/upload/medialibrary/959/0pbd3fr6d6ap754pfbcu6p2onxkdui0f/lambda6.png
Плюс подробное видео:
//www.youtube.com/embed/U9u1bWwvH40?feature=oembed
Читать статью полностью
Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"
Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в ...
Функцию ФИЛЬТР.XML можно использовать не только для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Есть ещё одно - неожиданное и красивое - применение этой функции для быстрого деления слипшегося текста по ячейкам:
https://www.planetaexcel.ru/upload/medialibrary/9db/00x2xujyrh149xj90c4iw3euql2xjm0t/split-text6.png
Подробный разбор механизма - в новой статье и видео:
//www.youtube.com/embed/pLk8DcebWG4?feature=oembed
Читать статью ...
Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):
//www.youtube.com/embed/nxPxp-CLIqA?feature=oembed
Читать статью полностью
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
В старых версиях Excel 2007-2013 это можно было сделать с помощью прямого веб-запроса.
Начиная с 2010 года это можно очень удобно делать с помощью надстройки Power Query.
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.
//www.youtube.com/ ...
Записал очередной видеоурок по созданию простой пользовательской функции на VBA для подсчета суммы, среднего или количества ячеек с заданным цветом заливки и/или шрифта:
//www.youtube.com/embed/zxhjLGX524E?feature=oembed
Читать статью полностью
Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.
https://www.planetaexcel.ru/upload/medialibrary/d69/ ...
Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала бегом сюда), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице. Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на ...
Поиск ключевых слов в исходном тексте - одна из очень распространенных задач при работе с данными. Давайте рассмотрим её решение несколькими способами на следующем примере: https://www.planetaexcel.ru/upload/medialibrary/fd8/g5gvz11zk5stkblizf7r11qtqpboq9h5/find-keywords1.png
Предположим, что у нас с вами есть список ключевых слов - названия автомобильных марок - и большая таблица всевозможных запчастей, где в описаниях иногда могут встречаться один или сразу несколько таких брендов, если ...
Сборка таблиц - классическая задача для любого пользователя Excel. Особенно "весело" она решается, если шапки в этих таблицах точно не совпадают, а имеют какие-либо различия.
Решить проблему можно весьма изящно - используя надстройку Power Query, которая умеет собирать таблицы даже с разной структурой:
//www.youtube.com/embed/WXWXHt_15AI?feature=oembed
Читать статью полностью
На практике весьма часто возникают случаи, когда нам с вами нужно найти ближайшее значение в наборе (таблице) по отношению к заданному числу. Это может быть, например:
Расчет скидки в зависимости от объема.
Вычисление размера бонусов в зависимости от выполнения плана.
Калькуляция тарифов на доставку в зависимости от расстояния.
Подбор подходящей тары для товара и т.д.
Причем окргуление может требоваться как в меньшую, так и в большую сторону - в зависимости от ситуации.
Есть несколько способов ...
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию.Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
//www.youtube.com/embed/iiW9ZGymH4w?feature=oembed
Читать статью полностью
Новая статья с видео о том, как реализовать одновременную фильтрацию сразу нескольких сводных таблиц одним общим срезом. Если сводные построены на основе одного источника, то всё просто. Если на основе разных, то придётся немного пошаманить с Power Pivot и Power Query :)
//www.youtube.com/embed/YGQtOTj2064?feature=oembed
Читать статью полностью
Простой, но красивый трюк с добавлением на диаграмму картинок в качестве подписей. Подойдет для визуализации логотипов компаний, брендов, флагов стран и т.п.
https://www.planetaexcel.ru/upload/medialibrary/31f/logo-on-chart5.png
Читать статью полностью
Новая статья и видео с подробным разбором плюсов и преимуществ построения сводных таблиц по Модели Данных Power Pivot по сравнению с классическими сводными в Excel.
//www.youtube.com/embed/-iA08ipGdWk?feature=oembed
Из самого сочного:
Связи между таблицами без ВПР.
Любые сложные вычисления в сводной на языке DAX.
Подсчёт количества уникальных значений.
Пользовательские наборы элементов по строкам и столбцам вместо фильтрации.
Конвертация сводной в функции кубов
... и т.д.
Читать статью ...
Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.
Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний ...
Сплошь и рядом встречаются сайты, где при попытке импортировать с них данные в Excel Power Query просто в упор не видит там таблиц с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, ...
Имеем список объектов (например, товаров) с пометкой, к какому набору (корзине) каждый из них относится. Необходимо разложить объекты по своим наборам, сформировав таблицу как на рисунке справа:
https://www.planetaexcel.ru/upload/medialibrary/401/divide-list-to-sets1.png
Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:
сотрудников по командам
водителей по маршрутам
клиентов по менеджерам
товары по корзинам и т.д.
В прошлом я уже ...
Одна из самых распространенных проблем (и частых вопросов на тренингах) в том, как построить сводную таблицу, если в качестве исходных данных тебе досталась вот такая "красота":
https://www.planetaexcel.ru/upload/medialibrary/bbd/pivot-multirow-header1.png
Выручить здесь может надстройка Power Query, при помощи которой можно за несколько минут превратить эту жесть в нормализованную плоскую таблицу, по которой строить сводную можно уже легко:
https://www.planetaexcel.ru/upload/ ...
Обновил старую статью о том, как создать в Microsoft Excel выпадающий список с показом выбранного изображения.
В новых версиях Excel описанный там подход уже не работает и нужен другой метод:
https://www.planetaexcel.ru/upload/medialibrary/d3f/dropdown-picture-animated.gif
Само-собой, к статье приложен и видеоурок:
//www.youtube.com/embed/hUf7c06LvqY?feature=oembedЧитать статью и смотреть видео
Стиль в Microsoft Excel - это сохраненная совокупность параметров форматирования ячейки. Единожды создав стиль, его затем можно многократно применять к другим ячейкам, моментально оформляя их нужным вам образом, что неимоверно ускоряет повседневную работу в Excel.
Главная прелесть стилей в том, что с их помощью можно не просто раскрашивать ячейки, а создавать крайне полезные в работе нестандарные форматы:
Стили для своих единиц измерения
Стили тысяч или миллионов
Стили с цветом и процентами для ...
Разбираем два способа построить вафельную диаграмму (waffle chart) в Excel - с помощью условного форматирования и отрихтованной линейчатой диаграммы (bar chart), чтобы получить вот такую красоту:
https://www.planetaexcel.ru/upload/medialibrary/193/waffle-chart-animated.gif
Отлично подойдет для отображения прогресса по любым задачам.
Читать статью полностью и смотреть видео
Продолжая начатую в прошлой статье тему про массовую замену текста, разбираемся с решением этой задачи уже в Power Query.
https://www.planetaexcel.ru/upload/medialibrary/c4f/pq-text-replace1.png
Использовать будем одну из не самых простых для понимания функций языка М - List.Accumulate:
//www.youtube.com/embed/oFK5ZBkq8gc?feature=oembed
Читать статью полностью
Выложил новую статью и видео про решение одной из очень распространенных задач - как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
//www.youtube.com/embed/Fxf2781Fk0w?feature=oembed
К сожалению, в MS Excel нет простых встроенных инструментов для решения этой проблемы, поэтому придется привлекать относительно сложные формулы массива (но Ctrl+Shift +Enter жать не придется).
В следующей статье разберём, как ...
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
//www.youtube.com/embed/VBlv6dfUvW8?feature=oembed
Читать статью полностью
https://www.planetaexcel.ru/upload/medialibrary/85e/pq-regexp8.pngПару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка ...
https://www.planetaexcel.ru/upload/medialibrary/24d/sequence4.png
Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Обновление Office 365, которое подарило Microsoft Excel новый вычислительный движок с поддержкой динамических массивов, также добавило к нашему арсеналу 7 новых функций, заточенных специально для работы с массивами. Про три самых важных функции: СОРТ (SORT), ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже ...
Разбор классической проблемы с функцией ВПР (VLOOKUP), которая отказывается находить числовые значения, если они отформатированы как текст:
https://www.planetaexcel.ru/upload/medialibrary/822/numbers-as-text-vlookup1.png
Читать статью полностью
Большой комплексный пример: пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.
https://youtu.be/U5fElrOtKEE
Читать статью полностью
Обновил старую статью про выделение дубликатов в списке цветом с помощью условного форматирования и добавил к ней видео с разбором 3 способов:
быстрое выделение дубликатов-ячеекправило УФ на основе формулы для выделения всей строкиправило УФ с формулой массива(!), когда нет ключевого столбца с уникальными значениями и нужно делать склейку всех значений в строке
https://youtu.be/BVfUq-8o32M
Читать статью полностью
Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.
https://www.youtube.com/watch?v=t20MXP3ST5Y
Читать статью полностью
Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, то важным фактором становится время. И выбрать правильный - самый быстрый! - способ уже критически важно.
Я давно хотел сделать подобный тест и вот, наконец, дошли руки. Некоторые результаты, признаюсь, были для меня ...
Написал подробную статью про возможности новой функции ПРОСМОТРX (XLOOKUP), недавно появившейся в Excel из Office 365. Эту функцию Microsoft позиционирует, как замену и наследницу для классической ВПР (VLOOKUP). В чем фишки, плюсы и минусы "новой легенды" - мы подробно разберем в этой статье и я расскажу в видео:
https://www.youtube.com/watch?v=KmWZTEFj55k
Читать статью полностью
Последний в этом году пост и видео хотелось бы сделать не в привычном формате видеоурока. А именно, совместить приятное с полезным и развлечь вас написанием у вас на глазах за 15 минут самой настоящей компьютерной игры на VBA в Excel - известной игры "Жизнь" (LIFE) британского математика Джона Конвея:
https://www.youtube.com/watch?v=nXusyCsXm2o
И, пользуясь случаем, хотелось бы от всей души поздравить всех пользователей "Планеты" - старожилов, новичков и просто заглянувших ...
Кардинально переписал статью про выпадающий список в Excel, куда можно оперативно добавлять новые элементы, причем в обе стороны - и в справочник, и в сам список непосредственно. Добавил новые трюки с умной таблицей вместо сложных именованных диапазонов :) Плюс записал видеоурок - многим, что ни говори, проще один раз увидеть:
https://www.youtube.com/watch?v=mL-zmZxa_Es
Просвещайтесь!
Ощутимо перелопатил и дополнил статью о том, как получить список файлов из заданной папки в Excel. Добавил самый универсальный, я считаю, метод через Power Query и записал уже традиционное видео с демонстрацией всех трёх способов - от скрытой функции до макроса и мегаудобного запроса в PQ с последующим подсчетом статистики в сводной таблице:
https://youtu.be/u-0mOUmSgRE
Выложил две статьи (обе с видео) про революционное, прямо скажем, обновление вычислительного движка Excel, добавляющее поддержку динамических массивов (Dynamic Arrays) и новые функции для работы с ними:
Динамические массивы в ExcelФункции динамических массивов: СОРТ, ФИЛЬТР и УНИК
Это очень-очень круто и даже немного жаль, что таких функций не было лет 10 назад - сколько времени можно было бы сэкономить! :)
Приходилось ли вам когда-нибудь по несколько минут ждать пока в вашей книге Excel отработает макрос, обновится запрос Power Query или пересчитаются тяжелые формулы? Можно, конечно, заполнить случившуюся паузу чайком-кофейком на вполне законных основаниях, но наверняка вам приходила в голову и другая мысль: а не открыть ли рядом другую книгу Excel и не не поработать ли пока с ней?
https://www.youtube.com/watch?v=PIC-HZ8NBsI
Есть несколько способов это сделать в зависимости от вашей версии Excel ...
Обновил старую, но не теряющую актуальность статью про конвертацию формул в значения. Дописал пару способов + запилил короткое видео:
https://www.youtube.com/watch?v=trryh1xSGss
Читать статью полностью
Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):
https://www.planetaexcel.ru/upload/medialibrary/c4a/dupes-in-cell1.png
Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать, а именно:
Как быстро найти все ячейки, ...
Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.
Механизм работы этого инструмента прост: если у вас есть один или ...
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt+Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - ...
Классика жанра - вопрос из серии "как мне собрать таблицы со всех листов моей книги на один?". Представьте, что у вас есть файл с больше, чем полусотней листов-отчетов по городам. Всё это счастье нужно собрать в одну таблицу для построения в будущем, допустим, сводной. Выручить могут два основных подхода: макросы и Power Query.
Давайте рассмотрим их подробно:
https://www.youtube.com/watch?v=pfPqVKGy8gc
Читать статью полностью
Недавно ко мне обратился один знакомый с просьбой помочь с генерацией всех возможных фраз, состоящих из набора заданных слов. Подобного рода задачи могут возникать при составлении списков ключевых слов и фраз для интернет-рекламы и SEO-продвижения, когда нужно перебрать все возможные варианты перестановок слов в поисковом запросе:
https://www.planetaexcel.ru/upload/medialibrary/9d5/phrase-generator1.png
В математике такая операция называется декартовым произведением. Официальное определение ...
Записал и выложил новое видео к старой статье по заполнению пустых ячеек соседними значениями. Разобрал все основные способы: формулы, макросы, с помощью Power Query и даже PLEX.
https://www.youtube.com/watch?v=pwp0-otBiZc&t=2s
Читать статью полностью
Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса.
У вас на компьютере всё работает прекрасно, но если вы решите отправить ...
Весьма частый случай на практике: вам нужно запускать один или несколько ваших макросов в заданное время или с определенной периодичностью. Например, у вас есть большой и тяжелый отчет, который обновляется полчаса и вы хотели бы запускать обновление за полчаса до вашего прихода на работу утром. Или у вас есть макрос, который должен делать автоматическую рассылку сотрудникам с заданной периодичностью. Или, работая со сводной таблицей, вы хотите, чтобы она обновлялась "на лету" каждые 10 ...
С летними обновлениями 2018 года Excel 2016 получил революционно новую возможность добавления в ячейки данных нового типа - биржевой (Stocks) и географической информации (Geography). Соответствующие иконки появились на вкладке Данные (Data) в группе Типы данных (Data types):
https://www.planetaexcel.ru/upload/medialibrary/300/new-data-types1.png
Что это такое и с чем это едят? Как это можно использовать в работе? Какая часть этого функционала применима для нашей российской действительности? ...
https://www.planetaexcel.ru/upload/medialibrary/3d9/my-addin19.png
Запилил большую и подробную статью + аж получасовое видео с пошаговым подробным разбором всего процесса создания своей собственной надстройки в Microsoft Excel. Основные этапы:
создание файла надстройкинаполнение его макросами и пользовательскими функциямиразличные способы запуска макросов надстройкисоздание отдельной вкладки с кнопками для надстройки
Так что теперь вы можете запросто сделать свой вариант PLEX ;)
Читать статью ...
Сильно обновил (по факту, практически полностью переписал статью) про 3 способа создания сводной таблицы на основе нескольких диапазонов данных. Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:
https://www.planetaexcel.ru/upload/medialibrary/c3c/pivot-from-multiple-tables.png
Читать статью полностью
P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад ...
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.
https://www.planetaexcel.ru/upload/medialibrary/7d9/powerbidesktop1.png
Читать статью полностью
Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:
https://www.planetaexcel.ru/upload/medialibrary/19a/flow-block-import1.png
То есть мы заранее не можем ...
Еще с 2011 года для Excel существует крайне полезная (и совершенно бесплатная!) надстройка Fuzzy Lookup от Microsoft, которая умеет искать ближайшие похожие текстовые строки в двух списках, т.е. работает как известная функция ВПР (VLOOKUP), но при неточном совпадении названий:
https://www.planetaexcel.ru/upload/medialibrary/037/fuzzy-lookup1.png
В некоторых ситуациях (например, при поиске похожих, но не точно совпадающих адресов) эта надстройка совершенно незаменима. Давайте рассмотрим подробнее ...
Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:
https://www.youtube.com/watch?v=Y6evpN9nGU0
Читать статью полностью
Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
формулымакросыобновляемый запрос через Power Query
https://www.planetaexcel.ru/upload/medialibrary/ ...
Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel :)
https://www.planetaexcel.ru/upload/medialibrary/bf0/personal-macro-workbook3.png
Читать статью полностью
Эта статья родилась после вопроса одного из моих слушателей с тренинга "Скульптор Данных в Microsoft Excel с Power Query". Вопрос был о том, как из вот такой таблицы по проектам:
https://www.planetaexcel.ru/upload/medialibrary/36b/pq-gantt1.png
... получить вот такую:
https://www.planetaexcel.ru/upload/medialibrary/9ba/pq-gantt2.png
Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, ...
Если вам часто приходится работать с текстом, анализируя и разбирая его на отдельные фрагменты по заданным шаблонам и правилам, то определенно имеет смысл внедрить в свою работу регулярные выражения (RegExp или, в народе, "регулярки";) - супермощный инструмент обработки текста, давно применяемый в современных языках программирования и текстовых редакторах. Excel не поддерживает регулярные выражения по умолчанию, но это можно легко исправить с помощью простой функции на VBA.
Спектр ...
Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...
Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.
/upload/medialibrary/812 ...
Как просчитать прибыльность инвестиционного портфеля криптовалюты, загрузив в Excel с помощью Power Query обновляемый курс покупки с сайта обменника, биржи или торговой площадки. Посвящяется тем 100500 человекам, что за последние пару месяцев написали мне в почту или в личку на форуме этот вопрос :)
http://www.planetaexcel.ru/upload/medialibrary/25d/bitcoin14.png
Ничего особо сложного или выдающегося тут нет - для Power Query это "семечки", делается за пару минут. Кроме того, подобным ...
Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
функцией ВПР (VLOOKUP) - классика жанрас помощью сводной таблицы - хороший вариант для больших таблицс помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и ...
Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:
http://www.planetaexcel.ru/upload/medialibrary/6ad/transform-to-table1.png
Давайте разберем несколько способов сделать это быстро и красиво, а именно:
формулами и, в частности, функцией ДВССЫЛ (INDIRECT)через Power Queryмакросом из надстройки PLEX
Читать ...
Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):
http://www.planetaexcel.ru/upload/medialibrary/e0e/winning-streak1.png
Решить проблему можно с помощью изящной формулы массива, но чтобы разобраться как именно и ...
На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:
http://www.planetaexcel.ru/upload/medialibrary/739/autosave8.png
Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом ...
Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
все столбцы со значениями выше среднеготри самых больших (Top-3)минимальное и максимальное значениестолбец, выбранный в выпадающем списке в ячейке листа и т.д.
На самом деле, все очень несложно
http://www.planetaexcel.ru/ ...
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
http://www.planetaexcel.ru/upload/medialibrary/976/generate-dupes1.png
Сделать это можно двумя основными способами: при помощи макросов и через Power Query.
Читать статью полностью
Если вам нужно визуализировать на диаграмме множество элементов сгруппированных по смыслу (товары по категориям, города по странам, месяцы за несколько лет и т.п.), то может здорово помочь секционная диаграмма:
http://www.planetaexcel.ru/upload/medialibrary/3ac/section-chart4.png
Каждая группа здесь, как видите, отображается своим цветом автоматически.
Строить такую штуку, на самом деле, очень просто - главное правильно подготовить таблицу с исходными данными.
Читать статью полностью + видеоурок
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов - подсчет количества и вывод совпадающих значений в двух списках. Подробно разберем три основных подхода для решения этой распространенной задачи:
формулымакросыPower Query
http://www.planetaexcel.ru/upload/medialibrary/084/compare2lists1.png
Читать статью полностью
По мотивам одноименной статьи записал видео решения задачи по созданию сводной с текстом через PowerQuery:
https://www.youtube.com/watch?v=v_ukejZ7cuU
Читать статью целиком
Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):
http://www.planetaexcel.ru/upload/medialibrary/4ad/merge-2-lists-without-dupes1.png
Давайте подробно разберем три принципиально разных способа:
удаление дубликатовсводная таблицаформулу массивачерез Power Query
Читать статью полностью
Как наглядно отобразить движение по заданному маршруту с привязкой ко времени на географической карте в Excel? Подробный разбор на реальном примере поезда "Москва - Самара"
http://www.planetaexcel.ru/upload/medialibrary/bb3/train8.gif
Читать статью полностью
Здорово обновил содерижмое статьи про склейку текста по условию, когда нужно собрать из столбца только определенные ячейки и склеить их с заданным символом-разделителем:
http://www.planetaexcel.ru/upload/medialibrary/217/concatenate-text-by-condition1.png
Дописал еще два способа: реализацию этой задачи формулами и через Power Query. Плюс сделал видеоурок по всем способам:
https://www.youtube.com/watch?v=QLhHpgQvRDc
Читать статью полностью
Ну, вот мы с вами и добрались до последней 30-й статье в нашем марафоне "30 дней = 30 функций". По факту, получилось чуть больше и дней и функций, но это не со зла :)
А замыкающей у нас будет редкая, но весьма полезная функция БДСУММ (DSUM).
Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д. Еще одним, относительно экзотическим, но весма мощным инструментом является ...
Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий - неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них - это функция ПРЕДСКАЗ (FORECAST), которая умеет считать прогноз по линейному тренду.
http://www.planetaexcel.ru/upload/medialibrary/f6d/forecast2.png
Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:
http://www.planetaexcel.ru/upload/medialibrary/85b/n1.png
Простенько, но со вкусом. Пользуйтесь :)
В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Помогут новые функции, появившиеся в Excel 2016 - МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS):
http://www.planetaexcel.ru/upload/medialibrary/1df/min-if10.png
Если у вас (или тех, кто будет потом ...
Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция РАБДЕНЬ (WORKDAY), которая умеет учитывать нерабочие дни в таких случаях:
http://www.planetaexcel.ru/upload/medialibrary/d46/round-to-workday4.png
На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.
http://www.planetaexcel.ru/upload/medialibrary/a98/indirect1.png
Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например:
для создания несбиваемых ссылокдля сбора данных с нескольких листов или книгдля суммирования по интервалу-окну на листедля ...
Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.
http://www.planetaexcel.ru/upload/medialibrary/715/substitute1.png
Часто возникает необходимость склеивать текст из нескольких ячеек в одну длинную фразу. Причем иногда нужно дополнительно вставлять между фрагментами еще и заданный символ-разделитель (пробел, запятую и т.д.)
Microsoft Excel в подобной ситуации может помочь тремя функциями:
СЦЕП (CONCAT)СЦЕПИТЬ (CONCATENATE)ОБЪЕДИНИТЬ (TEXTJOIN)
Подробная статья про все варианты в очередной порции нашего марафона по функциям Excel.
http://www.planetaexcel.ru/upload/medialibrary/e04/concat_text_functions2.png
Если сводная таблица для вас еще не финал, и нужно извлечь из нее данные для дальнейших расчетов, анализа или прогнозирования, то пригодится функция с длинным названиемПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которую мало кто (по моему опыту) использует правильно.
А она может быть очень и очень полезной:
http://www.planetaexcel.ru/upload/medialibrary/1da/getpivotdata6.png
Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов - в начале, в конце или внутри между словами.
Лечится эта проблема очень легко - специальной функцией СЖПРОБЕЛЫ (TRIM). Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:
http://www.planetaexcel.ru/upload/medialibrary/303/clean-text2.png
Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX), позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL).
С их помощью можно не только найти k-й по счету элемент списка, но и, если нужно:
отсортировать таблицу формулами "на лету"построить рейтинг или Топ10вытащить из списка все числовые значения по ...
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты.
В Microsoft Excel есть основная функция НОМНЕДЕЛИ (WEEKNUM) и ее аналог для стандарта ISO - функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO):. Плюс ко всему, можно, конечно же, вычислить номер недели формулой "в ...
Продолжаем наш марафон. День 18-й.
При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы "от и до" (в статистике их называют карманы). Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY).
http://www.planetaexcel.ru/upload/medialibrary/e3f/frequency1.png
Если вы используете фильтрацию или скрываете некоторые строки на листе (вручную или группировкой), то может возникнуть желание подсчитывать итоги только по оставшимся (видимым) строчкам. В такой ситуации может помочь либо классическая функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), либо ее более совершенный преемник в последних версиях Excel - функция АГРЕГАТ (AGGREGATE):
http://www.planetaexcel.ru/upload/medialibrary/1ea/sum-visible-only2.png
Разбор применения редкой функции ПРОСМОТР (LOOKUP) для решения задачи поиска последней занятой ячейки в строке или столбце (по тексту и числам):
http://www.planetaexcel.ru/upload/medialibrary/2c2/last-in-row-column2.png
При внешней простоте, функция ГИПЕРССЫЛКА (HYPERLINK) на практике оказывается весьма полезной и позволяет создавать ссылки на всевозможные объекты:
ячейки и диапазоны внутри текущей книгивнешние файлы, расположенные на жестком диске или сервереименованные диапазоныфрагменты умных таблицвеб-страницызаполненные бланки электронной почты
Все варианты применения - в очередной статье нашего марафона - просвещайтесь ;)
http://www.planetaexcel.ru/upload/medialibrary/8d1/hyperlink8.png
Это одна из тех функций, про которые, при первом знакомстве, обычно говорят "и что?". На самом деле функция ВЫБОР (CHOOSE) умеет не только выбирать заданный элемент из набора по номеру, а гораздо больше:
выдавать ссылку на диапазон из наборасклеивать диапазоны прямо в формуле в любом порядкеработать с вложенными функциями
http://www.planetaexcel.ru/upload/medialibrary/819/choose5.png
Если нужно конвертировать вертикальный диапазон в горизонтальный или наоборот, то есть много способов, кроме очевидной специальной вставки. Один из них - использовать функцию ТРАНСП (TRANSPOSE), введя ее как формулу массива.
http://www.planetaexcel.ru/upload/medialibrary/032/03296a17062de86ddbf20df58430acf9.gif
Большинство функций Excel не различают строчные и прописные буквы. Если же нужно искать точно (с учетом регистра), то пригодится функция СОВПАД (EXACT), умеющая сравнивать две текстовых строки с абсолютной точностью:
http://www.planetaexcel.ru/upload/medialibrary/391/exact1.png
Обычно, большинство пользователей, прочитав справку по функции СУММПРОИЗВ, бывают слегка, скажем так, разочарованы. На самом деле, использование этой функции по прямому назначению - для вычисления суммы произведений в нескольких диапазонах - самое скучный вариант ее применения. Она умеет намного больше!
работать с массивамипроверять одно или несколько условий со связками И и ИЛИсуммировать по условию данные из закрытых(!) книг
https://www.youtube.com/watch?v=VCVtXdDLfc0
Семь футов под килем - это сколько в метрах? Можно, конечно, искать ответ в Яндексе и Гугле, но иногда проще и быстрее спросить Excel - воспользоваться специальной функцией ПРЕОБР (CONVERT), умеющей конвертировать результаты из одной системы мер в другую:
http://www.planetaexcel.ru/upload/medialibrary/f50/f50119e8910b4f9a29c032aa52f1e2e8.gif
Подробная статья с видео о том, как правильно использовать одну из самых нужных и многогранных функций в Excel - функцию ИНДЕКС (INDEX) для:
извлечения данных из одномерных и двумерных диапазоновизвлечения данных из нескольких таблицсоздания динамических ссылок на целые столбцы или строкисоздания автоподстраивающихся диапазонов
http://www.planetaexcel.ru/upload/medialibrary/1e0/index1.png
... разбор ее параметров и практические приемы применения в очередной статье нашего ...
Продолжаем наш ликбез по функциям. Сегодня простая, но нужная функция СИМВОЛ (CHAR), умеющая выводить нестандартные символы по их коду. С ее помощью можно делать простые, но наглядные визуализации роста или падения каких-либо параметров, количества проданных товаров и т.д.
http://www.planetaexcel.ru/upload/medialibrary/5d2/char3.png
Одна из самых известных функций Excel - ВПР (VLOOKUP). Как ее использовать для поиска и подстановки значений из одной таблицы в другую:
http://www.planetaexcel.ru/upload/medialibrary/b13/b1366e5562bfcec6a1a7d07f8b74d1b9.gif
Как перехватывать ошибки в формулах и заменять их на что-то более полезное с помощью функции ЕСЛИОШИБКА (IFERROR):
http://www.planetaexcel.ru/upload/medialibrary/793/iferror2.png
Как исправить кривую текстовую дату в разных форматах и привести ее в нормальный вид с помощью функции ДАТАЗНАЧ (DATEVALUE):
http://www.planetaexcel.ru/upload/medialibrary/7e9/datevalue1.png
Как вычислить разницу между двумя датами (стаж, возраст) в годах, месяцах или днях с помощью скрытой функции Excel РАЗНДАТ (DATEDIF):
http://www.planetaexcel.ru/upload/medialibrary/c35/datediff1.png
Бывают ситуации, когда заранее не известно какие именно ячейки на листе нужно подсчитывать и нужно ссылаться на динамическое "окно" на листе заданных размеров. В этом случае поможет функция СМЕЩ (OFFSET):
http://www.planetaexcel.ru/upload/medialibrary/fe2/offset4.png
Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции элемента в списке. Как использовать приблизительный поиск (в большую или меньшую сторону), поиск ближайших дат, поиск первой или последней текстовой ячейки в строке-столбце, связку функций ИНДЕКС и ПОИСКПОЗ и т.д.
http://www.planetaexcel.ru/upload/medialibrary/c68/match1.png
... специально созданной категории ФУНКЦИИ в ПРИЕМАХ и в моем Twitter с ...
После статьи, где я подробно разбирал несколько способов отделять друг от друга наборы строк линией, несколько человек написали с просьбой реализовать похожее, но используя заливку. Не вопрос - написал статью с разбором трех способов для воплощения такого эффекта:
http://www.planetaexcel.ru/upload/medialibrary/b9c/highlight-rows-blocks4.png
С макросами и вспомогательным столбцом все более-менее очевидно, а вот второй способ (формула массива в условном форматировании) должен порадовать даже ...
Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно - группировка одной сводной влияет на все остальные и наоборот. Как отвязать сводную от общего кэша? Подробно разобрал три варианта:
Построить сводную с помощью Мастера, кнопку для которого нужно сначала вытащить в интерфейсОтвязать уже имеющуюся сводную от общего кэша рукамиИспользовать готовый макрос
http://www.planetaexcel.ru/upload/medialibrary/3cc/independent- ...
Что делать, если нужно быстро собрать данные из большого количества файлов Excel в одну таблицу? Вариантов тут, обычно, несколько:
тупо копипастить вручную (с кучей ошибок в процессе)писать макрос для сбора данных или заказывать его знакомому VBA-программистуделегировать задачу подчиненным :)
На самом деле, начиная с Excel 2013 есть еще один путь - сильно проще и на порядок удобнее - надстройка Power Query. Для Excel 2013 ее можно совершенно бесплатно скачать и подключить, а в Excel 2016 она уже ...
Что делать, если нужно найти в таблице заданное значение, скажем, в пятом столбце, а извлечь результат из той же строки, но 3-его столбца? Т.е. реализовать "левый ВПР"?
http://www.planetaexcel.ru/upload/medialibrary/118/left-vlookup1.png
Написал статью о том, как решить эту распространенную проблему несколькими способами:
в лоб (ручной перестановкой столбцов)виртуальной перестановкой столбцов функцией ВЫБОРсвязкой функций ИНДЕКС и ПОИСКПОЗфункцией выборочного суммирования ...
Написал статью и записал видео про одну из самых неоднозначных, многоплановых и полезных функций Microsoft Excel - функцию ИНДЕКС (INDEX):
http://www.planetaexcel.ru/upload/medialibrary/f7b/index6.png
Подробно разобраны все варианты ее применения:
поиск данных в столбцепоиск данных в двумерной таблицепоиск в нескольких таблицахссылка на строку-столбецссылка на ячейку
Читайте, смотрите, прокачивайте навыки в сложных формулах.
Записал видео к старой статье про редизайн кросс-таблиц в плоские с помощью макросов и надстройки Power Query из Excel 2013-2016:
https://www.youtube.com/watch?v=SdmAjxAsKug
Всем хороши сводные таблицы - и считают быстро, и настраиваются гибко, и дизайн можно накрутить в них нарядный, если требуется. Но есть и несколько ложек дегтя, в частности, невозможность создать сводную, где в области значений должны быть не числа, а текст. Написал статью, где разобрал пару способов обойти это ограничение:
http://www.planetaexcel.ru/upload/medialibrary/7ad/pivot-with-text1.png
Читать статью полностью
Если вам приходится строить пузырьковые диаграммы, то вы оцените красивый и быстрый способ их анимировать с помощью стандартной Microsoft'овской надстройки Power View из пакета бизнес-анализа (BI):
http://www.planetaexcel.ru/upload/medialibrary/6d6/animated-bubbles.gif
Читать статью полностью и смотреть видео
Стандартнейшая задача: есть список имен и надо проставить М или Ж напротив каждого. Желательно - не руками. Если есть отчества, то все проще, а если их нет? Или в списке есть экспаты?
Написал статью с подробным разбором каждого варианта и записал видео:
https://www.youtube.com/watch?v=OE9BvI4tvlI
Читать статью полностью
Записал видеоурок к статье про двумерный поиск в таблице сразу по строчкам и по столбцам одновременно. Кто любит не читать, а смотреть-слушать - welcome :)
https://www.youtube.com/watch?v=O0YOGJ7q-aQ
Если вам хочется автоматизировать свой бизнес, но ваша компания пока не уровня "Газпрома", то можно попробовать использовать для этой цели Microsoft Excel. После небольшой доработки напильником из него вполне возможно создать небольшую, но полноценную БД: с отчетами, связанными таблицами, формами ввода и т.д. Потребуется немного времени, десяток формул и один небольшой макрос.
http://www.planetaexcel.ru/upload/medialibrary/671/db3.png
Читать статью полностью
Иногда Excel начинает числа в некоторых ячейках воспринимать как текст (обычно такие ячейки помечаются зеленым уголком, но не всегда):
http://www.planetaexcel.ru/upload/medialibrary/42f/nums-as-text1.png
Это бывает по разным причинам, многие из которых от нас не зависят (так выгружается из корпоративной базы данных, например). Вопрос в том, как превратить такие числа в полноценные, т.к. с "псевдочислами" Excel нормально работать не может: многие формулы будут выдавать ошибки или ...
Необходимость поиска наибольших и наименьших значений в любом бизнесе очевидна: самые прибыльные товары или ценные клиенты, самые крупные поставки или партии и т.д.
Но наравне с этим, иногда приходится искать в данных не топовые, а самые часто встречающиеся значения, что хоть и звучит похоже, но, по факту, совсем не то же самое. Применительно к магазину, например, это может быть поиск не самых прибыльных, а самых часто покупаемых товаров или самое часто встречающееся количество позиций в заказе ...
В Excel много мощных инструментов, которые все (по-крайней мере, большинство) пользователей знают: функции а-ля ВПР, сводные таблицы и т.д. Но есть несколько простых и весьма удобных инструментов, которые при этом почему-то обделены вниманием. Представления (Custom Views) - один из них.
Давно хотел написать статью о нем и вот, наконец, дошли руки. Записал даже видео, которое здесь нагляднее всего показывает прелести Представлений на практике:
http://www.planetaexcel.ru/upload/medialibrary/646/ ...
Разбор использования функции ПРОСМОТР (LOOKUP) для быстрого поиска последней непустой ячейки в строке или столбце - отдельно для ситуаций с числами и текстом в ячейках:
http://www.planetaexcel.ru/upload/medialibrary/2c2/last-in-row-column2.png
Читать статью полностью
Написал статью в ответ на весьма частый вопрос на тренингах: "Если при вычислении сроков нужная дата выпадает на выходные, то как найти ближайший рабочий день?"
Решений тут два - классическое (с вложенными проверками через ЕСЛИ и ДЕНЬНЕД) и красивое (с помощью функции РАБДЕНЬ). Можно даже праздники учитывать.
http://www.planetaexcel.ru/upload/medialibrary/d46/round-to-workday4.png
Читать статью полностью
Переработал старую статью и записал большое 25-минутное видео с подробным разбором типовых проблем, приводящих к "распуханию" файлов в Excel до нескольких мегабайт и их торможению:
https://www.youtube.com/watch?v=y-WY_gBlivE
Читать статью полностью
Как на скорую руку сделать годовой календарь проекта (тренингов, отпусков) с наглядным отображением интервалов дат, пересечений и выходных. Всего-то три правила условного форматирования, а сколько пользы :)
http://www.youtube.com/watch?v=CbTIzpHT1VY
Читать статью полностью
Написал статью про различные способы решения банальной, на первый взгляд, задачи - размещения элементов в списке в обратной последовательности (реверс). Сортировкой, простыми формулами, формулами массива, на динамических списках, макросами и т.д.
http://planetaexcel.ru/upload/medialibrary/95b/reverse3.png
Читать статью полностью
Обновил (фактически - переписал) статью в Приемах про поиск различий и сравнение ...
Периодически сталкиваюсь с вопросом: "как подсчитать, сколько пятниц (четвергов, вторников...) попадает в календаре на заданный интервал дат? Например, у вашей компании платежные дни во вторник и четверг. Или вы ходите в баню по пятницам. Или вы, наконец, купили абонемент в качалку по средам и субботам и нужно подсчитать сколько этих дней недели попадает до конца года.
Вот, дошли руки написать статью на эту тему. Формула в статье, на первый взгляд, короткая, но, на самом деле, весьма хитрая ...
В преддверии отчетности в конце года записал обучающее видео и написал статью о том, как построить в Excel диаграмму-торнадо (она же диаграмма-бабочка или "горшок").
http://planetaexcel.ru/upload/medialibrary/f42/tornado1.png
Очень удобный вид диаграммы для наглядного сравнения двух наборов данных по нескольким параметрам, например:
мы-конкуренты по основным товараммужчины-женщины по возрастным группамэтот и прошлый год по месяцам и т.д.
Читать статью и смотреть видеоурок
В очередной раз кадровики спросили на тренинге как именно посчитать количество дней пересечения между двумя интервалами дат в графике отпусков или тренингов, и я решил написать на эту тему короткую статью. Вместо классического подхода из кучи вложенных в друг друга проверок функциями ЕСЛИ используем статистическую функцию МЕДИАНА - с ней все будет компактно и красиво.
http://planetaexcel.ru/upload/medialibrary/7fe/date-intersection3.png
Всем, кто периодически составляет графики отпусков, ...
Если, по какой-то причине, вам необходимо запретить вывод на печать информации из книги Excel, то можно воспользоваться простым макросом.
http://planetaexcel.ru/upload/medialibrary/4c0/print-taboo2.png
Читать статью полностью
По мотивам общения со знакомыми менеджерами по персоналу написал коротенькую статью про то, как правильно сортировать даты рождения в списке сотрудников или клиентов, чтобы люди распределялись не по возрасту, а по месяцам ДР:
http://planetaexcel.ru/upload/medialibrary/63b/sort-birthdates5.png
Трюк, на самом деле, очень простой, но задачу решает эффективно. Надеюсь, кому-то он жизнь облегчит ;)
Читать статью полностью
В продолжение статьи о двумерном ВПР написал о том, как реализовать трехмерный вариант ВПР, когда нужно сначала найти нужный лист, а потом вытащить с него данные с пересечения заданных строки и столбца.
http://planetaexcel.ru/upload/medialibrary/1c0/vlookup3d-banner2.png
Читать статью полностью
По мотивам последнего тренинга "Мастер Формул" записал видеоурок к статье про вытаскивание из списка сразу всех значений по условию, т.е. что-то типа ВПР (VLOOKUP), но с извлечением не только первого встретившегося значения, а сразу всех.
http://www.youtube.com/watch?v=3vhZcF6oL0E
Периодически в Excel сталкиваюсь с необходимостью сгенерировать набор целых случайных чисел в заданном интервале. Причем - уникальных, т.е. без повторений. Стандартные встроенные функции типа СЛУЧМЕЖДУ (RANDBETWEEN) тут не подходят, т.к. запросто могут выдать повторения.
Написал свежую статью о том, как же, все-таки, это реализовать.
Пока вся страна занимается умножением и делением на 80 и 70, запилил очередную статью и видеоурок про одну из "темных лошадок" Microsoft Excel - функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ или GETPIVOTDATA в английском варианте. Эта функция автоматом вставляется при попытке сделать ссылку на ячейку в сводной и большинство пользователей шарахаются от этого как от огня, не очень понимая зачем это вообще и как это использовать.
А штука-то, на самом деле, мегаполезная! Так что берите на ...
Записал к статье обучающий видеоурок с тремя способами создания связанных выпадающих списков в ячейках листа Excel, т.е. списков, содержимое которых динамически формируется в зависимости от выбора пользователя в предыдущих списках:
https://www.youtube.com/watch?v=rUgW65pIYqo
Захотелось собрать в одном месте ссылки и описания на полезные программки, надстройки и утилиты для программиста на Visual Basic в Excel. То, чем пользуюсь сам или применяют мои коллеги.
http://planetaexcel.ru/upload/medialibrary/34b/vba-must-have-utils.png
Читать статью полностью
Функция ВПР - штука хорошая, но ищет и подставляет данные только по одному столбцу и по одному параметру.
А если их несколько?
В новой статье подробно расписал как минимум 3 способа это сделать + записал видеоурок для наглядности.
https://www.youtube.com/watch?v=jFf3Zq8DZX0
Читать статью полностью
Обычно в строке состояния Excel в левом нижнем углу висит слово "ГОТОВО". Иногда там после фильтрации отображается количество оставшихся строк. Ну, еще пару случаев можно придумать, поднатужившись. А может отображать в ней что-то более полезное? Ну, хотя бы, адреса выделенных диапазонов и количество ячеек в них:
/upload/medialibrary/ad3/status-bar5.png
Если интересно как это сделать - почитайте статью.
Установил себе на днях новый Windows 10 Preview в паре с Office 2016 Preview и хорошенько их поизучал. В итоге родилась статья с подробным разбором изменений и нововведений, ожидающих нас с вами в очередной версии Excel 2016.
http://planetaexcel.ru/upload/medialibrary/019/new-in-excel2016.png
Читать статью полностью.
Выложил большой обзор возможных способов визуализации числовых данных из Excel на географических картах. Разобраны:
Bing MapsPower ViewPower MapПузырьковые диаграммыНадстройки сторонних разработчиков
http://planetaexcel.ru/upload/medialibrary/5c3/geo13.png
Если вам такое уже приходилось делать или есть шанс нарваться на такую задачу в будущем - welcome!
Читать статью полностью
Всегда радовал двойной щелчок для автоматического протягивания формулы вниз. И всегда же бесило отсутствие похожего действия для протягивания формулы вправо. Да и вниз, порой, протягивается не до конца таблицы, а до первой пустой ячейки в соседнем столбце или до первой заполненной. Попытался учесть это все в макросе для автоматического умного протягивания вниз или вправо в очередной статье.
http://www.planetaexcel.ru/upload/medialibrary/dd4/smart-autofill.gif
Читать статью полностью
Записал дополнительно видеоурок для статьи про фильтрацию сводных таблиц Срезами (Slicers) и Временной Шкалой (Timeline). Быстрый и наглядный способ показывать в ваших отчетах только те данные, которые нужны в данный момент - гораздо более удобная альтернатива стандартным фильтрам:
http://www.youtube.com/watch?v=VFwHlfGXVPQ
Смотреть эти и другие видео на моем канале YouTube
Написал статью про разные способы подсчета (суммы, среднего, количества и т.д.) только видимых ячеек с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Бывает полезно такое уметь, когда у вас на листе есть скрытые строки, включены фильтры, есть свернутые группировкой строки/столбцы и т.п.
http://www.planetaexcel.ru/upload/medialibrary/54f/sum-visible-only1.png
Читать статью полностью
Написал статью и записал видеоурок по простому, но весьма полезному трюку, позволяющему "зашить" подстановочную таблицу для функций типа ВПР, ИНДЕКС, ПОИСКПОЗ и им подобных прямо внутрь формулы. Основная идея - использование массива констант:
http://www.planetaexcel.ru/upload/medialibrary/c22/lookup-from-array-constants5.png
Если запихнуть этот массив еще в именованный диапазон, то выглядит, буквально, как подстановка "из ниоткуда". Магия Excel :)
Читать статью и смотреть ...
Систематизировал и пошагово описал в новой статье самые удобные способы создания динамических автоподстраивающихся диапазонов. Косвенно они упоминались на сайте уже несколько раз в разных разделах, но совершенно однозначно давно заслуживали отдельной подробной статьи.
Если в вашей работе часто встречаются таблицы размеры которых "плавают", и вам постоянно приходится об этом помнить, думать и корректировать формулы, ссылки и т.д., то эта статья определенно для вас :)
http://www. ...
Решил пробежаться по классике и записал видео по проверке одного и нескольких условий с помощью функций ЕСЛИ, И, ИЛИ, ВПР в таблицах Excel. Заполнить пробелы и сложить паззл в картинку, так сказать :)
Смотреть видео на YouTube
http://www.youtube.com/watch?v=AsaWB3w0nJo
Если вы когда-нибудь мучились вопросом, как перенести настройки печати (параметры страницы) с одного листа на другие в книге Excel, а не воспроизводить их вручную для каждого листа, то самое время прочитать эту короткую статью. Там делов-то на полминуты, если знать как :)
http://www.planetaexcel.ru/upload/medialibrary/2e3/page-setup-copy3.png
Чуть сложнее придется, если нужно скопировать области печати и сквозные строки-столбцы - потребуется простой макрос, который тоже приведен в статье.
Читать ...
Думаю, подавляющее большинство читателей этого сайта и блога сталкивались с одной из самых частых задач в повседневной работе любого пользователя Excel - превращение формул в ячейках в значения. Выложил статью с описанием 4 способов это сделать - выбирайте на свой вкус :)
http://www.planetaexcel.ru/upload/medialibrary/947/formulas-to-values4.gif
Читать статью целиком
Выложил статью и записал видеоурок про весьма типичный случай в ежедневной практике любого менеджера - построение наглядной диаграммы "План-Факт" (Actual vs Budget) для сравнения достигнутых реальных значений по сравнению с запланированными. К сожалению, весьма часто такие диаграммы строят, особо не парясь, с помощью гистограмм, что убивает всю наглядность на корню. В статье подробно разбирается несколько способов сделать это более изящно - с помощью полос повышения-понижения:
http:// ...
Вопрос надежности парольной защиты в Excel мучил меня давно. Честно говоря, я не очень понимаю позицию Microsoft в этом вопросе. Какой смысл тратить на проект месяц работы и пятизначную сумму, чтобы потом его вскрыли с помощью 300-рублевой программки тупым подбором паролей за 15 минут?
Не так давно мне попался в руки "Криптономикон" Нила Стивенсона - восхитительная книжка, которую я проглотил запоем и где важную роль в сюжете играли шифры и их взлом. Потом дружественная вселенная ...
Написал статью про то, как бороться с ошибкой "Слишком много различных форматов" в Excel и со "стилевым адом", когда после многочисленного копирования фрагментов из других книг в ваш файл в списке стилей накапливается стилевой "мусор":
http://www.planetaexcel.ru/upload/medialibrary/ff4/too-many-formats2.png
Такие вещи здорово замедляют вашу книгу и утяжеляют ее, но их можно легко победить с помощью небольшого макроса.
Читать статью полностью
Если у вас бывают большие таблицы, то для с помощью условного форматирования можно легко провести разделительные линии между отсортированными предварительно наборами строк:
http://www.planetaexcel.ru/upload/medialibrary/891/separate-line-between-rows2.png
Наглядно, не правда ли?
Читать полную статью
Написал новую статью о том, как опустить линии проекции на оси X и Y от точек с данными в диаграмме Excel, используя нестандартную настройку планок погрешностей:
http://www.planetaexcel.ru/upload/medialibrary/b96/projection-lines-in-chart1.png
Мелочь, а наглядно.
Читать статью
На одном из тренингов недавно спросили, как можно сохранять историю изменения отдельных ячеек на листе. Встроенный в Excel глючный режим совместного доступа с отслеживанием изменений всех ячеек был не нужен, так что пришлось импровизировать и реализовать все небольшим макросом:
http://www.planetaexcel.ru/upload/medialibrary/5a6/logs-in-comment.png
Кому интересно - почитайте.
Написал статью с видеоуроком про то, как несколькими способами можно сложить ячейки через одну, через две и т.е. просуммировать каждую 2-ю или 3-ю или, в общем случае, n-ю ячейки не перебирая их мучительно вручную в формуле поштучно. Рассмотрел несколько способов: формула массива, функции СУММЕСЛИ, БДСУММ и т.д. Подобный подход можно использовать и для подсчета среднего арифметического, выбора максимального или минимального и т.д.
http://www.planetaexcel.ru/upload/medialibrary/bf2/sum-every-N- ...
Выложил короткую, но забавную статью про то, как можно с помощью пользовательского формата и специального стиля быстро и удобно скрывать любые данные на листе от постороннего взгляда. Не в смысле "отформатируйте белый на белом", конечно, а изящнее:
http://www.planetaexcel.ru/upload/medialibrary/803/hide-cell-content-demo.gif
Это, само собой, не полноценная защита с паролем, а просто удобный способ скрывать какие-то приватные данные от беглого взгляда через плечо, когда вы работаете.
Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу - как склеивать текст из нескольких ячеек при выполнении определенного заданного условия. Что-то вроде СУММЕСЛИ, но только для текста, чтобы получить на выходе что-то похожее на:
http://www.planetaexcel.ru/upload/medialibrary/217/concatenate-text-by-condition1.png
Читать статью целиком
Выложил новую статью с коротким видеоуроком о том, как построить в Excel диаграмму-шкалу или диаграмму-термометр, как ее иногда называют в русскоязычной интерпретации. Если вы часто строите в Excel отчеты с финансовыми показателями (KPI), то вам должен понравится этот экзотический тип, наглядно отображающий цели vs результаты:
http://www.planetaexcel.ru/upload/medialibrary/97e/bullet-chart1.gif
Само-собой, это нестандартный тип диаграммы в Excel и создать его можно только своими руками, особым ...
Выложил статью с видеоуроком про то, как в Excel рассчитать все основные виды кредитов: простой с периодическими платежами (аннуитетный), кредит с досрочным погашением (с уменьшением срока или суммы выплаты), с нерегулярными платежами. Готовые модели расчетов можно, как обычно, скачать в виде файла-примера и сразу использовать, подставив в них ваши данные и особо не вдаваясь в детали. А если они вам все же интересны, то приготовьтесь разбираться с финансовыми функциями Excel, которые отвечают за ...
Написал статью про то, как готовить данные и строить по ним известную многим диаграмму Парето для наглядного отображения ключевых факторов, влияющих на бизнес. Что-то не было ничего давно про визуализацию - так что держите :)
http://www.planetaexcel.ru/upload/medialibrary/ba3/pareto1.png
Написал статью про то, как с помощью простого макроса попарно подсвечивать дубликаты в выделенном диапазоне. Т.е. вместо стандартного условного форматирования, которое тупо зальет их все одним и тем же цветом, получить заливку каждой пары (тройки...) дублей своим оттенком:
http://www.planetaexcel.ru/upload/medialibrary/c69/duplicates-coloring2.png
Если кто часто сталкивается с поиском повторяющихся значений в больших списках - налетайте ;)
Написал статью про то, как превратить громоздкий и неудобный расширенный фильтр в Excel в мощный и удобный инструмент для фильтрации больших списков по нескольким сложным критериям. Немного "доработки напильником" с помощью простого макроса - и вуаля:
http://www.planetaexcel.ru/upload/medialibrary/547/advanced-filter-work.gif
Как говорил мой знакомый автослесарь: "Скрепки, скотч и суперклей - это наш метод!" ;)
Написал статью про то, как отслеживать время-дату входа-выхода и фиксировать имена всех пользователей, которые открывают вашу книгу Excel с важными данными. Посвящается всем, кто когда-либо обнаруживал свой файл на сетевом диске изуродованным коллегами по офису и другими добрыми людьми. На отдельном скрытом листе с помощью пары макросов будет вестись лог, где всегда можно найти виноватого:
http://www.planetaexcel.ru/upload/medialibrary/493/blackbox3.png
Как говорится: "Если у вас мания ...
Записал видеообзор и сделал статью про полный и подробный разбор и тестирование возможностей нового Excel для iPad. Какие функции поддерживаются, что работает и что нет по сравнению с десктопной версией.
http://www.youtube.com/watch?v=iFoHMb7CYIE
P.S. Все же очень полезно иногда менять CEO :)
Записал большой видеоурок про то, как реализовать выборочные вычисления с помощью формул СУММЕСЛИ СЧЁТЕСЛИ, СРЗНАЧЕСЛИ, БДСУММ и др.. Если вам приходится подсчитывать итоги (сумму, среднее или количество) по одному или нескольким условиям в ваших таблицах - добро пожаловать:
http://www.youtube.com/watch?v=mmS709XIKf8
Написал очередную статью и видеоурок про то, как подсвечивать в больших таблицах строки с прошлыми, будущими и текущими датами разными цветами. Пригодится всем, кто ведет реестры договоров, отгрузок, продаж и хочет наглядно цветом подкрашивать уже сделанные прошлые, будущие и сегодняшние элементы.
/upload/medialibrary/bb8/dates-in-color7.png
http://www.youtube.com/watch?v=9mQJtEsOzvY
Написал статью про то, как подсвечивать ячейки с лишними пробелами с помощью условного форматирования. Делается за полминуты в любой версии Excel. Незаменимая фишка для форм ввода и борьбы с "шаловливыми ручками" креативных пользователей, которым один пробел между словами кажется недостаточным:
/upload/medialibrary/7a3/highlight-bad-spaces.gif
Написал статью в Приемы и видеоурок о том, как ...
Написал статью и записал видео про 4 способа решения весьма распространенной задачи в Excel - копирования формул с относительными ссылками так, чтобы ссылки не смещались на другие ячейки.
http://www.youtube.com/watch?v=oAUubQWusoY
Читаем, смотрим, просвещаемся ;)
Уже несколько раз натыкался в разных источниках на разные варианты реализации "вечного календаря" с помощью формул. В итоге решил выложить самый удобный и короткий (относительно) способ. С помощью всего одной формулы массива создается календарь на любой месяц любого года:
/upload/medialibrary/5ac/universal-calendar2.png
Немного громоздко, зато - универсально!
Update:
Как всегда, пришел МСН и все улучшил! :)
Обновил содержимое и коды макросов в статье про пометку элементов в списке для удобного их отбора:
/upload/medialibrary/8c5/checkboxes-animate.gif
Добавил к мультивыбору галочками макрос для выбора "один-из" жирной точкой:
/upload/medialibrary/d11/checkboxes2.png
Обновленный файл с примером прилагается.
Несколько способов считать в Microsoft Excel, не используя классические формулы и функции (специальная вставка, строка состояния, калькулятор).
Записал очередной видеоурок про настройку вычислений в сводных таблицах Microsoft Excel. Если вам нужно подсчитывать не только банальную сумму, а что-то посерьезнее (среднее, доли разного вида, динамику, ранжирование и т.д.), то прошу:
http://www.youtube.com/watch?v=TwtCCNIlBso
Записал и выложил видеоурок по созданию обычного и параметрического веб-запросов для импорта в Excel курса любой нужной валюты на заданную дату:
http://www.youtube.com/watch?v=8KQchkmFGsI
... новый раздел ДУБЛИКАТЫ в классификаторе приемов по работе с повторами, уникальными ...
Запилил давно обдумываемую статью про то, как реализовать автоматическую раскраску столбцов или долек любой диаграммы по цветам из ячеек с ее исходными данными.
/upload/medialibrary/687/chart-colors-from-cells1.png
Весьма полезный в некоторых ситуациях трюк, пользуйтесь!
Выложил статью с видеоуроком про то, как с помощью Excel проанализировать выгрузку детализации мобильных разговоров на примере Билайна. Пригодится для выбора подходящего оператора, тарифа и допуслуг. Все делается с помощью нескольких формул и парочки сводных таблиц.
http://www.youtube.com/watch?v=TzCIVCUK-cs&feature=player_detailpage#t=14s
Написал и выложил статью про разные способы реализации выпадающего списка, из которого можно выбирать не один, а несколько элементов. С накоплением вправо по строке, вниз по столбцу и в той же ячейке через запятую:
/upload/medialibrary/2c1/dropdown-multi-select3.gif
Написал и выложил в Приемы статью о том, как правильно подбирать числа из набора, чтобы получить заданную сумму на выходе. Подробно разбирается два способа: надстройка Поиск решения (Solver) и макрос перебора.
Всем игрокам в блэкджек посвящается...
/upload/medialibrary/1b4/adjust0.png
Записал и выложил видеоурок по трем способам сборки данных из нескольких таблиц (одинаковых по виду и разных) в одну итоговую:
http://www.youtube.com/watch?v=8ouvhYlsza4
Записал и выложил в Приемы новое видео по защите данных в Excel
http://www.youtube.com/watch?v=OUv8NhCdyTs
Написал и выложил статью про то, как определять имя/логин пользователя и отображать все листы в книге только определенным людям. Удобно использовать для скрытия вспомогательных листов с промежуточными данными, расчетами от чужих глаз.
Выложил статью про то, как с помощью отступов и парочки функций делать сложные вложенные формулы более наглядными и простыми для понимания. Тем, кому надоело считать количество открывающих-закрывающих скобок и долго врубаться в сложную формулу (которую сам же и написал, но - полгода назад):
http://www.planetaexcel.ru/upload/medialibrary/375/comfort-formulas4.png
Читать статью полностью
Написал и выложил статью про создание редкого вида диаграмм в управлении проектами - временной шкалы (ленты) проекта, чаще называемой в английском варианте Project Timeline или "календарь проекта". Служит для отображения ключевых этапов проекта на оси времени:
/upload/medialibrary/b72/timeline1.png
Записал видеоурок по основным принципам создания и использования макросов и пользовательских функций на VBA в Excel. Для гуру и экспертов в программировании ничего нового не будет, но для новичков в этом вопросе, думаю, полезно.
Полностью переписал макрос для редизайна обычных таблиц в плоские - более удобные для построения сводных и фильтров-сортировок. Теперь макрос может работать и с многострочными шапками и с многостолбцовыми подписями в столбцах.
Выложил новую статью с видеороликом про решение частой проблемы - как вставить данные только в отфильтрованные (видимые) ячейки после применения Автофильтра.
Выложил статью про то, как быстро создать с помощью облачного сервиса www.skydrive.com любой опрос и автоматически подгружать его результаты к себе в файл Excel.
/upload/medialibrary/b0b/skydrive-survey7.png
Как выглядит опрос вживую можно посмотреть тут
Выложил видеоурок по выполнению слияния - процедуры, позволяющей с помощью связки Word-Excel организовать массовое создание однотипных писем с подставленными в них именами-фамилиями-адресами-компаниями для почтовой или электронной рассылки.
Выложил новую статью с видео про то, как отфильтровать именно те строки в исходных данных, которые участвуют в вычислении заданной ячейки результата сводной таблицы. Всем подгоняльщикам оптимизаторам посвящается ;)
Обновил и заново выложил статью про различные способы выборочного суммирования по одному или нескольким критериям для разных версий Excel.
Записал и выложил видеоурок по использованию инструмента Текст по столбцам (Text to Columns) для разделения "слипшегося" в один столбец текста - на несколько.
Записал обучающее видео к старенькому, но приятному уроку по созданию выпадающего списка с картинками.
Как при помощи простого макроса сделать необычный подарок своей половине на День Святого Валентина :)
/upload/medialibrary/f36/3d.gif
Если еще остался кто-то, кто не слышал про сводные таблицы (pivot tables) в Microsoft Excel - милости прошу. Выложил видеоурок по созданию и базовой настройке основных функций отчета сводной таблицы.
Записал и выложил видеоурок по использованию динамических Таблиц (я их называю "умными") в Microsoft Excel. Что это такое, как их создавать, какие "плюшки" и бонусы они дают.
Как в ячейке листа Excel создать выпадающий список с наполнением, который будет динамически формироваться по заданному диапазону. Причем при дописывании-удалении данных в диапазон, все изменения тут же будут отображаться в выпадающем списке.
/upload/medialibrary/756/dynamic-dropdown.gif
Читать тут
Выложил в Приемы очередную статью - про то, как строить в Excel диаграмму "водопад" (waterfall), часто называемую также "мостом" (bridge).
Выложил новую статью - Сумма ячеек по цвету. Как просуммировать или подсчитать количество ячеек с определенным цветом заливки или шрифта.
Немного перегруппировал статьи в Приемах - сделал новую категорию для операций с книгами и листами.
Выложил туда новую статью - Сохранение листов книги как отдельных файлов.
Выложил новую статью в Приемы - Новые возможности диаграмм в Excel 2013
Выложил новую статью в разделе Приемы - Новые возможности сводных таблиц Excel 2013.
Представьте, что вы подготовили какой-либо расчёт в файле Excel (например, бюджет или отчет по продажам) и отправили его своим коллегам на согласование и правку. Файл прошел по цепочке рецензентов и вернулся к вам уже в изменённом виде. Возникает простой и закономерный вопрос - что именно и где тут изменилось?Предположим, с ходу видно, что внутри внесено несколько правок, но все их быстро идентифицировать и проконтролировать весьма непросто, тем более, что они могут быть неочевидными - например, ...
В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я ...
Отсортировано по релевантности | Сортировать по дате
