Билл Джелен. Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel

Рубрика: 7. Полезняшки Excel

Билл Джелен – автор 24 книг по Excel (см., например, Сводные таблицы в Microsoft Excel 2013, Всё о ВПР: от первого применения до экспертного уровня), ведущий сайта http://mrexcel.com/. На самом деле эта книга была написана тысячами людей, участниками форума сайта MrExcel.com. Во многих случаях новые идеи возникали в процессе обсуждения – кто-то задавал вопрос, кто-то отвечал, затем решение шлифовалось, предлагались альтернативы. Сегодня на форуме более 100 000 зарегистрированных участников, сотни завсегдатаев, и шесть миллионов уникальных посетителей в год. Сообщество MrExcel.com дает ответы на 30 000 вопросов в год. А в архиве имеется около 365 000 ответов. Так что вполне вероятно, что там можно найти ответ на почти любой вопрос.

Bill Jelen. Excel Gurus Gone Wild: Do the IMPOSSIBLE with Microsoft Excel. – Holy Macro! Books, 2009 – 248р.

Билл Джелен. Откройте новые возможности в Excel. Обложка

Введение. Эта книга родилась в британском пабе. Я был в Англии в 2007 году, чтобы провести пару семинаров. В ночь перед семинаром в Саутгемптоне группа людей из сообщества MrExcel собрались на обед. Эти были гуру Excel, и они хорошо отзывались о сайте, отметив, что это настоящий кладезь знаний по Excel и VBA. И я задумал написать эту книгу. Она, мягко говоря, является нишевой, и не рассчитана на широкие массы. Темы в этой книге весьма заумные. Наверное, лишь 0,1% пользователей найдет в ней что-нибудь привлекательное (на мой взгляд, автор преуменьшает. – Прим. Багузина). Если вы один из этих людей, тогда эта книга для вас.

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

Оригинальные файлы, используемые в книге доступны для скачивания по ссылке.

Раздел 1. ФОРМУЛЫ

Найти первое непустое значение в строке

Рассчитать количество рабочих дней

Создать именованный диапазон из праздничных дней (см. Имена диапазонов)

Сумма каждой второй или каждой третьей строки (идеями Билла Джелена я дополнил ранее написанную заметку Excel. Суммирование значений в четных (или кратных) строках (столбцах))

Превращение логических значений ИСТИНА/ЛОЖЬ в числа с помощью операции минус минус

Функция СУММПРОИЗВ

Булева логика: ЛОЖЬ = 0, И = *, ИЛИ = +, все остальное = ИСТИНА (подробнее см. Булева логика: критерии И, ИЛИ)

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

ДВССЫЛ. Ссылка на ячейку, чей адрес основан на вычислениях (см. также Примеры использования функции ДВССЫЛ)

Ссылка на другой лист с помощью ДВССЫЛ

ДВССЫЛ. Ссылка на лист, имя которого содержит дату

Использование ДВССЫЛ для получения данных из диапазона ячеек

Оператор пересечения множеств

Сумма одной и той же ячейки на нескольких листах

Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Ссылки R1C1

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

Сортировка с помощью формулы

Даты до 1900 года

ВПР для получения N-го вхождения и возврата из левого столбца

Запись текста в обратном порядке на основе формулы с циклической ссылкой

Определение промежуточного значения методом линейной интерполяции

Сумма цифр содержимого ячейки

Как извлечь уникальные значения из списка

Автонумерация строк и столбцов в базе данных Excel

Раздел 2. ПРИЕМЫ

Автофильтр в сводной таблице

Сортировка промежуточных итогов

Копирование точной формулы с помощью клавиатурного сокращения Ctrl+’

Альтернативное меню перетаскивания (Alternate Drag and Drop menu)

Быстрое создание меню гиперссылок

Быстрое создание большого числа имен диапазонов

Добавление формул в рисунки SmartArt

Создание сводной таблицы на основе нескольких листов

Задание координат объекта DataLabel (подписи данных на диаграмме)

Масштабирование элементов диаграмм для отображения правильного соотношения сторон

Раздел 3. МАКРОСЫ

Создание личной книги макросов

Назначение макросу сочетания клавиш

Запуск макроса кнопкой

Запуск макроса иконкой

Создание обычного макроса

Создание макроса обработчика событий

Извлечение адреса электронной почты из ячейки, содержащей другой текст

Нахождение наиболее близкого значения

Использование таймера для сравнения производительности формул

Как узнать путь к папке TEMP

Использование функции VBA Evaluate вместо цикла

Переименуйте листы на основе значения в ячейке А1

Примените пользовательскую функцию вместо ДВССЫЛ в закрытой книге

Определите в VBA количество активных листов

Создайте имена листов, используя маркер заполнения

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

Добавьте фильтр для выбора функциональности

Используйте макрос для выделения активной ячейки

Удалите заголовок пользовательской формы

Держите кнопку всё время на виду

Добавьте контекстное меню для пользовательской формы

Задайте денежный или процентный формат текстового поля пользовательской формы

Удаление записей в VBA

Подпись макросов при совместной работе

Увеличение области экрана

Список комбинаций N элементов из M

Приложение. Алфавитный справочник наиболее употребляемых функций

Комментарии: 5 комментариев

Спасибо.
А вот так excel может?
Научить excel самому находить ИНН по названию организации:
Пройтись по столбцу содержащему названия
Для каждой ячейки этого столбца запустить поиск браузера по содержимому ячейки
Найти в выдаче поиска 10значные цифровые последовательности,
Ту которая, чаще всех встречается, вбить в ячейку справа от названия

Михаил, теоретически Excel такое может. У меня недавно спрашивали про скрапинг с помощью VBA — Функция VBA для выполнения веб-запроса (Web Query) Но, на мой взгляд, на выходе будет мусор. Существует масса компаний с похожими названиями и различными ИНН. Не пробовали сервис nalog.ru?

egrul.nalog.ru — там капча

А есть ли какие то материалы по VBA для новичков, сжатые в 200-300 страниц, желательно на английском? У меня есть талмуд Уолкенбаха, но там всё чересчур объёмно.

Попробуйте Steve Cummings. VBA for Dummies В книжке около 400 страниц, включая картинки.


Прокомментировать