• Архив

    «   Июнь 2016   »
    Пн Вт Ср Чт Пт Сб Вс
        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      

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

Разбор использования функции ПРОСМОТР (LOOKUP) для быстрого поиска последней непустой ячейки в строке или столбце - отдельно для ситуаций с числами и текстом в ячейках:



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

Новая статья: Поиск ближайшего рабочего дня

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



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

Новые функции Excel 2016 в апреле

Наконец-то и до меня дошла волна обновлений и в моем Excel 2016 по подписке Office 365 Pro Plus появились новые функции для работы с текстом и логикой. Давайте я пробегусь по ним на примерах:

Функция СЦЕП (CONCAT)

Многим, думаю, известна функция СЦЕПИТЬ (CONCATENATE), которую можно использовать для склеивания фрагментов текста из нескольких ячеек в одно целое. На практике, часто также используется спецсимвол & для аналогичного действия. Но оба этих способа предполагают указание каждой ячейки с текстовым фрагментом отдельно, что, при большом количестве ячеек, начинает напрягать:



В апрельском обновлении Excel 2016 добавили функцию СЦЕП, которая работает совершенно аналогично, но позволяет задать сразу целый диапазон (даже двумерный), всё содержимое ячеек которого будет склеено в единое целое:



Функция ОБЪЕДИНИТЬ (TEXTJOIN)

В предыдущем примере видно, что для красивого объединения мне пришлось предусмотреть отдельные ячейки с символами-разделителями. Новая функция ОБЪЕДИНИТЬ работает аналогично СЦЕП, но автоматически добавляет еще заданный символ между разными фрагментами:



Также обратите внимание на второй аргумент этой функции - он определяет, нужно ли игнорировать пустые ячейки (ИСТИНА) или нет (ЛОЖЬ).

В реальной практике такая функция представляется весьма полезной. Например, для склейки адресных строк это будет весьма компактным и красивым решением:



Функция УСЛОВИЯ (IFS)

Любой пользователь Excel сталкивается в работе с необходимостью проверять несколько условий и делать потом различные действия, в зависимости от выполнения одного из них. Обычно это реализуется с помощью вложенных друг в друга функций ЕСЛИ (IF) и выглядит при большом количестве условий не очень понятно (новичков, почему-то, особо пугает куча закрывающих скобок в конце - от всех ЕСЛИ):



Теперь же можно ощутимо упростить формулу, если использовать новую функцию УСЛОВИЯ (IFS). В её аргументах попарно перечисляются условия и то, что нужно вывести при их выполнении:



Получается не сильно компактнее, но заметно понятнее, не правда ли?

Если будете ее использовать, то обратите особое внимание на два важных момента:
  • Функция проверяет условия в порядке их указания и при выполнении какого-либо условия уже не проверяет оставшиеся. Поэтому порядок условий играет роль: в приведенном выше примере проверка нарушений скорости идет от большего к меньшему, а не наоборот.
  • Если нужно задать результат, который должен выводиться, если ни одно из условий не выполнилось (как Else в VBA), то в конце списка условий можно задать условие ИСТИНА. В нашем примере - это сумма штрафа (0), если ни одного из нарушений скорости не было.
Функция ПЕРЕКЛЮЧ (SWITCH)

Некий аналог предыдущей функции или конструкции Select Case в языке программирования Visual Basic, если вам знакомы макросы. Сравнивает ячейку с набором заданных значений и выдает один из заданных в наборе результатов. Например:



Очень похоже на предыдущую функцию УСЛОВИЯ, но в ней условия можно задавать гибко (использовать знаки <>= и т.д.), а здесь проверяется только точное совпадение. Последним аргументом можно задать то значение, которое должно выводиться, если ни одно из предыдущих условий не выполнилось. Раньше, чтобы реализовать что-то подобное приходилось шаманить с функцией ИНДЕКС (INDEX) и массивом констант в фигурных скобках внутри формулы:



Функции МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

Совсем недавно писал статью и делал видео в Приемах на тему поиска максимального и минимального значений в таблице при выполнении одного или нескольких условий. Не суперсложная техника, но без формул массива или функций баз данных не обойтись. Теперь в Excel 2016 появились новые функции, которые все делают красиво:



Как видно, все по аналогии с СУММЕСЛИМН, СЧЁТЕСЛИМН и т.д. - переучиваться не нужно.

P.S.

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

И помните о том, что все эти приятные фишки будут работать только у тех, кто установил себе последнюю версию Excel 2016 и регулярно ее обновляет (сейчас это происходит, в основном, автоматически). В противном случае в списке доступных функции вы их не найдете, а другие пользователи увидят ошибку #ИМЯ на ячейках, где вы их использовали.

Файл со всеми вышеприведенными примерами новых функций можно скачать ниже:

Новое видео: Как уменьшить размер файла и ускорить его в Excel

Переработал старую статью и записал большое 25-минутное видео с подробным разбором типовых проблем, приводящих к "распуханию" файлов в Excel до нескольких мегабайт и их торможению:

Загрузка плеера

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

Обновление надстройки PLEX версия 2.6 для Microsoft Excel

Выпустил обновление для моей надстройки PLEX для Microsoft Excel - версия 2.6. Список изменений:

Новый интерфейс
Раньше в разных версиях Excel (2007, 2010, 2013, 2016) иконки для одних и тех же инструментов надстройки могли выглядеть по-разному, т.к. использовались типовые картинки из встроенного в Office набора. Теперь у каждого макроса своя авторская пиктограмма и во всех версиях Excel надстройка выглядит одинаково хорошо :)



Выбор банка в курсах валют

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



Так и не смог найти нормального источника данных курсов НацБанка Казахстана - те, что есть жутко тормозные и глючные, потока XML-данных на официальном сайте ЦБ Казахстана нет. Так что в этот раз добавлены только НацБанки Украины и Беларуси.

Разделение таблицы на несколько листов
Сильно доработан (фактически- заново написан) макрос для разделения одной таблицы на несколько листов:



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



Редизайн кросс-таблицы в плоскую

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



Умеет работать и с обычными и с «умными» таблицами, выводить данные с сохранением форматирования (заливка, шрифт и т.д. кроме рамок), выгружать на отдельный лист или в заданную ячейку.

Сохранение листов как отдельных файлов в PDF
Инструмент, разбирающий книгу на отдельные листы и сохраняющий их потом в отдельные файлы теперь умеет это делать в PDF:



Добавление недостающих пробелов в слипшийся текст
Добавлена новая функция TextSplit, которая умеет разделять слова в слипшемся тексте без пробелов. Ориентируется на смену регистра, знаки препинания, буквы-цифры и т.д.



Округление времени с заданной точностью

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



У этой функции следующие аргументы:


  • InputTime – исходная ячейка с временем, которое нужно округлить
  • MinsToRound – шаг округления в минутах
  • RoundType – тип округления (0 – стандартно, 1 – вверх, -1 – вниз)
Исправления ошибок и мелкие улучшения
  • Немного улучшен интерфейс календаря.
  • Преобразование формул в значения теперь можно делать теперь на всех выделенных предварительно листах (c Ctrl или Shift).
  • Исправлен баг конвертирования формул в значения при выделении нескольких диапазонов или только видимых ячеек.
  • Точное копирование формул опять может копировать из книги в книгу. И можно выделять только одну ячейку в диапазоне вставки, а не точно такой же по размеру диапазон.
  • Исправлен баг, не позволявший фильтровать данные в сводных таблицах, если в исходных данных очень много столбцов.
Покупатели полной версии уже могут скачать последнюю версию 2.6 со всеми улучшениями и новыми функциями в разделе PLEX (желтая ссылка сверху, как обычно). Демо-версия тоже доступна, но традиционно отстает - она еще 2.2 и таковой пока останется.

Открыли раздел онлайн-курсов по Excel

Прикрутили к движку сайта модуль онлайн-обучения. Видеоуроки + текстовое описание с иллюстрациями, тесты для самопроверки - все как положено. Теперь даже проживающие далеко от славной столицы не будут обделены нашим вниманием. Впрочем и многим здешним жителям формат дистанционного обучения, возможно, окажется удобнее. Ехать никуда не надо, сервис доступен 24/7 - так что и "совы" и "жаворонки" будут довольны.

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

Общая структура курса выглядит так:


В курсе 6 глав, 25 уроков, 10 обучающих видео, 45 контрольных вопросов.

Посмотреть курс

В планах добавление онлайн-курсов по сводным таблицам, визуализации, поиску и подстановке данных в Excel и т.д.
Если будет интерес, то можем делать и курсы по другим программам пакета Microsoft Office - мы тут не только по Excel специалисты. Буду рад увидеть ваши пожелания в комментариях.

Новая статья: Гистограмма с переменной шириной столбцов

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



В некоторых случаях такой тип диаграммы оказывается очень удобным.
Читать статью полностью

Новая статья: Календарь этапов проекта в Excel

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

Загрузка плеера


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

Новая статья: Обратный порядок элементов в списке

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



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

Обновление: Как сравнить два списка в Excel

Обновил (фактически - переписал) статью в Приемах про поиск различий и сравнение двух списков в Microsoft Excel. Добавил несколько способов (включая формулу массива для вывода различий отдельным списком) и записал обучающее видео для наглядности.

Загрузка плеера


Просвещайтесь ;)

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

Новая статья: Сколько пятниц в январе 2016 года

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

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



Пользуйтесь на здоровье ;)

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

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



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

Поэтому пишу как есть.

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

Ну, а мы, со своей стороны, постараемся тоже сделать все правильно и поднимать ваш КПД в работе с Microsoft Office на недосягаемую высоту :)

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

Итоги 2015 года

По традиции, подвожу итоги этого непростого года - для истории и отслеживания тенденций:
  • Провели с коллегами 131 тренинг (77 из них вел я лично) общей длительностью 201 тренинговый день (из них 135 - моих), где обучили чуть больше 1500 человек
  • Провели тренинги в компаниях: Xylem, ФармСтандарт, Федеральная Перевозочная Компания, КазТрансГаз Аймак, Damate, Atlantic Group, Роснефть, Фениче Рус, Альфастрахование, Согаз, Раздолье, Эггер, Навиконс, Friesland Campina, Arysta, Альфа Лаваль, Видео Интернейшнл, Reima, Valeant, Mercedes Benz, Первая Грузовая Компания, ГК Ренова, Российские Космические Системы, Газпромнефть МНПЗ, Globus, Нижфарм, Райффайзен Банк, Takeda, i-media, Multon, Perfetti Van Melle, Лукойл, Blue Line, KIA, Стрела Телеком, Coca-Cola Hellenic, НИИ Стандартизации, ВТБ, Шлюмберже (вроде никого не забыл).
  • Съездил в 9 командировок в Астану, Нижний Новгород, Тюмень, Гагарин.
  • Написал 19 полноценных статей в Приемы и обновил контент по мелочи еще в нескольких, написал 43 статьи в Блог.
  • Записал 13 видеоуроков для моего канала YouTube, который в этом году пробил планку в 2.6 млн. просмотров и 18500 подписчиков.
  • Выпустил 3 обновления моей надстройки PLEX для Excel
  • Выполнил 3 проекта автоматизации бизнес-задач с помощью VBA и Excel для компаний Xylem и Р-Фарм. С учетом тренинговой нагрузки берусь сейчас за такие проекты только "для своих" или когда встречается интересная задача. Ну, или когда очень много платят :)
  • Сделал новый тренинг "Мастер Формул" для продвинутых пользователей.
  • Выпустил вдогон к бумажному еще и электронный вариант своей книги, который закупили для своих электронных библиотек система "Финансовый Директор" и издательский дом "Актион". Неожиданно понял, что на книгах могут что-то ощутимое зарабатывать не только Бушков и Дашкова. Начал писать следующую книгу по сложным формулам - 50% done!
  • Провел проект "30 сочетаний клавиш Excel за 30 дней" и почти удвоил свою аудиторию в твиттере.
  • Главное личное событие этого года: купил квартиру и сделал в ней полный ремонт. Сын есть. Осталось посадить дерево.
  • По сравнению с 2014 уменьшилось количество прочитанных книг и посещений спортзала, но это из-за ремонта в последние пару месяцев - он сожрал все мои временнЫе и энергоресурсы. После НГ планирую вернуться в прежний ЗОЖ :)
  • Снова начал рисовать
На "Планете" за этот год:
  • побывало 3.5 млн. пользователей
  • было 14 млн. просмотров
  • средняя нагрузка составила 9,5 тыс. пользователей и 38 тыс. просмотров в день, что примерно на треть лучше, чем в прошлом году
  • максимальная нагрузка была 2 декабря - больше 25 тысяч человек в день


Такие дела.

Делать то, что нравится - это свобода. Любить то, что делаешь - это счастье.

Новая статья: Диаграмма-торнадо в Microsoft Excel

В преддверии отчетности в конце года записал обучающее видео и написал статью о том, как построить в Excel диаграмму-торнадо (она же диаграмма-бабочка или "горшок").



Очень удобный вид диаграммы для наглядного сравнения двух наборов данных по нескольким параметрам, например:
  • мы-конкуренты по основным товарам
  • мужчины-женщины по возрастным группам
  • этот и прошлый год по месяцам и т.д.
Читать статью и смотреть видеоурок

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

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



Всем, кто периодически составляет графики отпусков, тренингов, смен и т.д. должно пригодиться.

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

Новая статья: Запрет распечатки книги Excel

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



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

Новая статья: Сортировка дней рождений

По мотивам общения со знакомыми менеджерами по персоналу написал коротенькую статью про то, как правильно сортировать даты рождения в списке сотрудников или клиентов, чтобы люди распределялись не по возрасту, а по месяцам ДР:



Трюк, на самом деле, очень простой, но задачу решает эффективно. Надеюсь, кому-то он жизнь облегчит ;)

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

Новая статья: Трехмерный поиск по нескольким листам или ВПР 3D

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



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

Новое видео: Мульти-ВПР

По мотивам последнего тренинга "Мастер Формул" записал видеоурок к статье про вытаскивание из списка сразу всех значений по условию, т.е. что-то типа ВПР (VLOOKUP), но с извлечением не только первого встретившегося значения, а сразу всех.

Загрузка плеера

С Днем Рождения, Excel!


30 лет назад, 30 сентября 1985 года вышла первая официальная версия Microsoft Excel.
В день рождения принято вспоминать былые заслуги и творческий путь именинника. Давайте попробуем вкратце это сделать.

Excel 1.0


Эта версия работала только на компьютерах Apple. На тот момент, интерфейс был революционным среди подобных программ, поскольку активно использовал мышь, меню, кнопки и т.д.

Excel 2.0

Вторая версия Excel была предназначена уже для Windows

Excel 3.0

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

Excel 4.0

Четвертая версия стала продолжением идей предыдущей: добавились возможности настройки панелей инструментов, новые сочетания клавиш, впервые появился маркер автозаполнения (тонкий черный крестик для копирования)

Excel 5.0

В этой версии появились листы и поддержка VBA.

Excel 7.0 или Excel 95

Шестой номер версии Microsoft пропустили, чтобы подровнять номера версий и сделать их одинаковыми для всех приложений пакета Office. "Семерка" стала первой 32-битной версией Excel. Не особо изменившись внешне, она, тем не менее, получила много внутренних улучшений и работала более быстро и стабильно.

Excel 8.0 или Excel 97

В этой версии в Excel добавили проверку данных (выпадающие списки и т.д.) и знаменитого помощника-скрепку. Также был усовершенствован интерфейс разработки Visual Basic. Интерфейс стал более плоским и современным.

Excel 9.0 или Excel 2000

В этой версии впервые был представлен многоместный Буфер Обмена.

Excel 10.0 или Excel 2002

Эта версия была частью пакета Office XP. В ней Excel научился фоновой проверке формул (зеленый уголок на ячейке) и автоматическому восстановлению книг в случае непредвиденного завершения работы. Также в этой версии впервые появились боковые области задач (task panes).

Excel 11.0 или Excel 2003

Эта версия входила в пакет Office 2003 и получила поддержку XML, списков (предшественник "умных таблиц";) и смарт-теги.

Excel 12.0 или Excel 2007

В 12-й версии, вышедшей в 2007 году впервые был представлен новый ленточный интерфейс и новые форматы файлов (xlsx и др.). Также были сильно расширены возможности условного форматирования (гистограммы, цветовые шкалы, иконки и др.), средства удаления дубликатов и "умные таблицы".

Excel 14.0 или Excel 2010

Пропустив, на всякий случай, несчастливый номер, Microsoft выпустила в 2010 году улучшенный вариант предыдущей версии в составе пакета Office 2010. В этой версии Excel получил улучшенные инструменты редактирования изображений, минидиаграммы-спарклайны, улучшения в работе со сводными таблицами (срезы) и возможность редактирования ленты интерфейса. Также в дополнение к этой версии впервые появилась бесплатная надстройка Power Pivot для анализа данных и построения сводных таблиц из различных источников данных.

Excel 15.0 или Excel 2013

Эта версия получила модный плоский дизайн интерфейса, более 50 новых функций и целую пачку надстроек для анализа и визуализации данных, таких как Power View, Power Map и др. Также в этой версии был впервые добавлен интеллектуальный заполнитель - функция FlashFill.

Excel для iOS и Android

Параллельно с полноценной версией для ПК в 2014 году появилась и начала развиваться ветка приложений для смартфонов и планшетов. Возможности этих версий позволяли удобное редактирование, ввод и форматирование для созданных на ПК книг.


Excel 16.0 или Excel 2016

Эта версия вышла совсем недавно - 23 сентября 2015 г. В ней в Excel впервые добавлены 6 новых типов диаграмм и несколько новых статистических функций для прогнозирования. Надстройки Power Query, Power Pivot и Power Map для импорта, обработки и анализа данных (BI) в этой версии встроены по умолчанию.

Славный путь! :)

Новое видео: Инструменты для сводных таблиц в надстройке PLEX для Excel

Записал небольшое видео с наглядной демонстрацией возможностей моей надстройки PLEX при работе со сводными таблицами в Microsoft Excel. На примере разбираем:
  • переключение сразу всех полей сводной в сумму, среднее или количество
  • автоматический перенос числовых форматов исходных данных в сводную
  • отвязывание сводной от исходных данных (автоматическое копирование сводной на новый лист как значений)
  • фильтрацию исходных данных по текущей ячейке
Как говорится, лучше один раз увидеть...

Загрузка плеера

Новая статья: Случайные числа без повторов

Периодически в Excel сталкиваюсь с необходимостью сгенерировать набор целых случайных чисел в заданном интервале. Причем - уникальных, т.е. без повторений. Стандартные встроенные функции типа СЛУЧМЕЖДУ (RANDBETWEEN) тут не подходят, т.к. запросто могут выдать повторения.

Написал свежую статью о том, как же, все-таки, это реализовать.
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 17 | След.