• Архив

    «   Сентябрь 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    

Новое видео: Двумерный поиск в таблице (ВПР 2D)

Записал видеоурок к статье про двумерный поиск в таблице сразу по строчкам и по столбцам одновременно. Кто любит не читать, а смотреть-слушать - welcome :)

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

Новая статья: Создание базы данных в Excel

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



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

Подарочек к 1 сентября

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



Пишут, что восьмой(!) год подряд отхватил статус MVP по Excel.

На следующей неделе зовут в гости в московский офис Microsoft - дружить домами, пить и веселиться. Думаю, надо сходить. Где у меня там завалялся двухтомник пожеланий по следующим версиям Excel? :)

Планете 10 лет


30 августа 2006 года я зарегистрировал домен planetaexcel.ru под будущий сайт, так что у нас сегодня небольшой юбилей :)

Изначально цель сугубо утилитарной: выложить методические материалы к тренингам, которые я тогда вел в "Специалисте", и ответы на типовые вопросы, чтобы потом давать ссылочку своим слушателям. Дизайн был нарисован за две ночи, порезан на слайсы в Adobe Fireworks (кто-нибудь еще помнит такую программу?) и залит через FTP на хостинг. Три месяца по ночам после работы и в выходные я писал статьи в Приемы и другой контент. В декабре 2006 года сайт был запущен и открыт всем желающим.

Автор нового сайта похож на молодую маму. Помню, как распираемый гордостью, я написал Экслеру в надежде похвалиться и получить рекомендацию, но в ответ получил письмо с фразой: "А картинки где?". Оказалось, что из-за моего косяка верстки под FireFox не отображались картинки ни на одной странице, хотя IE отображал все нормально. Гордости поубавилось, опыта прибавилось - стал все тестировать в разных браузерах.

Ну, а потом пошло-поехало. Парочка редизайнов (сначала самодельных, потом профессиональных у веб-студий за деньги). Переезд с движка sBuilder на Bitrix и мучительное перетаскивание 40 тыс. тем старого форума на новый. Переезд с копеечного хостинга на недешевый, но зато мощный облачный выделенный сервер. Многочисленные допиливания, доработки и полировки, которых не перечесть и уже, наверное, не вспомнить. Несколько падений, благополучно закончившихся (бэкапы наше все), отбитых DDOS и попыток взлома (китайским хакерам пламенный привет).

Не все было идеально, конечно, но сайт до сих пор жив, относительно шустро бегает, принимает в будни >23 тыс. уникальных пользователей ежедневно и помогает людям находить решения своих задач при работе в Excel. А это уже немало :)

Новая статья: Преобразование чисел в текстовом формате в нормальные числа

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



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

Написал статью и записал видео о том, как бороться с этой бедой.

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


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

Расписание открытых тренингов на осень 2016 (Москва)

Сверстали расписание осеннего блока моих открытых тренингов по Microsoft Excel. Даты окончательные и двигаться туда-сюда уже не будут - можете смело планировать под них свое расписание, командировки, отпуска и т.п.
  • Тренинг "Расширенные возможности Microsoft Excel" (2 дня) - мест нет
    3 и 10 сентября 2016 г. (сб), с 10:00 до 17:30, Программа тренинга
  • Тренинг "Мастер Формул в Microsoft Excel" (1 день) - мест нет
    8 октября 2016 г. (сб), с 10:00 до 17:30, Программа тренинга
  • Тренинг "Визуализация данных в Microsoft Excel" (1 день) - осталось 4 места
    15 октября 2016 г. (сб), с 10:00 до 17:30, Программа тренинга
  • Тренинг "Программирование макросов на VBA в Microsoft Excel" (3 дня) - мест нет
    4-6 ноября 2016 г. (пт-вс), с 10:00 до 17:30, Программа тренинга
  • Тренинг "Прогнозирование и оптимизация в Microsoft Excel" (1 день)
    19 ноября 2016 г. (сб), с 10:00 до 17:30, Программа тренинга
Территориально все проходит в Москве, в современном компьютерном классе в 5 мин от метро Комсомольская или Красные Ворота. Все тренинги веду я лично.

Посмотреть стоимость, подробности и забронировать себе место можно на этой странице или написав письмо мне на info@PlanetaExcel.ru



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

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

Прощай, Билайн :(

Был их клиентом с 2002 года. Перевел на них всю семью, все телефоны, планшеты и прочие гаджеты.
До последнего времени все было более-менее нормально. Косяки, конечно, были (а у кого их не бывает?), но в пределах нормы.

Но последние пару месяцев оказались просто шедевральными:
  • Сильно ухудшилось качество связи. То есть каждый второй звонок (буквально!) плохо слышно, внезапные обрывы и перезвоны. Что еще хуже - люди не могут дозвониться мне и даже не приходит потом сообщение, что "вам звонил такой-то, но не дозвонился" как раньше. Просто звонки "в никуда".
  • По Москве и области где раньше был LTE теперь 3G, а иногда даже E. Тормоза ощутимые даже при проверке почты, спасаюсь переносной точкой Yota Many.
  • Пару раз в центре Москвы "нет сети". Нет сети, Карл! На улице!
  • Новый неотменяемый тариф за роуминг по 200 рублей в день, про который уже все написали - просто песня. В последнем отпуске потратили рекордную сумму на связь. Браво билайновским маркетолухам за гениальную задумку. Остап Бендер отдыхает.
  • Самовозникающие подписки на непонятные услуги по 20-30 рублей в день (к этому уже все привыкли, да?)
Я - человек терпеливый (работа такая), но всему есть предел.
Так что, прощай, Билайн. Понимаю, что никто из большой четверки не идеален, но это уже перебор. Ушел с сохранением номера в "Мегафон", тем более, что я их учу :)
Процесс перехода оказался совсем несложным: подписал заявление, оплатил 100 руб государству, получил новую симку. Через неделю пришла СМС-ка "смените симку" и все заработало. Обрыв связи с миром я даже не заметил, поскольку вел тренинг в этот момент, но он был явно не более часа. Также потребовался еще один звонок в "Альфа-Банк", чтобы обновить данные по новой симке для интернет-банкинга - 5 минут.

Такие дела.

Новая статья: Поиск и подсчет самых частых значений

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

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



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

Вебинар по функции ВПР на сайте "Финансовый Директор"

Провели с коллегами с сайта "Финансовый Директор" вебинар по Excel на платформе Webinar.ru для всех желающих. Получился небольшой личный рекорд - 1046 участников! (было бы символично, если 1024). Рассказал, фактически, кусочек тренинга "Мастер Формул" - про трюки и нюансы использования горячо всеми любимой функции ВПР (VLOOKUP):
  • Классический ВПР
  • Зачем нужен Интервальный просмотр = 1
  • Умные таблицы + ВПР
  • Как вычислять номер столбца для ВПР
  • Как "вылечить" несовместимость форматов
  • Как убрать лишние пробелы и невидимые символы, мешающие ВПР
  • Перехват ошибок #Н/Д
  • Приблизительный поиск с * и ? в ВПР
  • Как сделать "левый ВПР"
  • Как вытащить сразу все значения, а не только первое (Мульти-ВПР)
  • Как вытащить сразу несколько столбцов, а не один
В общем и целом, доволен - получилось компактно и продуктивно. Уложился в 1:10, плюс еще 20 мин ответов на вопросы.

Вел из дома. Изнутри это выглядело примерно так:



Запись обещали смонтировать и выложить в общий доступ.


Запись вебинара доступна тут, но только для подписчиков "ФД" (это не мое решение и от меня это не зависит, к сожалению). Можно бесплатно зарегистрироваться у них и попросить демо-доступ на 3 дня - тогда для просмотра доступны все материалы.

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

В Excel много мощных инструментов, которые все (по-крайней мере, большинство) пользователей знают: функции а-ля ВПР, сводные таблицы и т.д. Но есть несколько простых и весьма удобных инструментов, которые при этом почему-то обделены вниманием. Представления (Custom Views) - один из них.

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



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

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

Разбор использования функции ПРОСМОТР (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 построить диаграмму, где ширина столбцов будет не одинаковой (как обычно), а переменной величиной, т.е. наглядно демонстрировать еще один параметр:



В некоторых случаях такой тип диаграммы оказывается очень удобным.
Читать статью полностью
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 18 | След.