Перейти к содержимому

7. Полезняшки Excel

Расширяем возможности макросов в Excel за счет средств Visual Basic

Заметка написана Андреем Макаренко

Всем, кому приходится использовать функции Excel, на определенном этапе сталкиваются с проблемой ограниченности предлагаемого функционала. Первый выход из этой ситуации — все более глубокое изучение встроенреных возможностей (знакомясь с этой темой, не устаешь поражаться как богатству предлагаемых Excel возможностей, так и неординарности решений их использования). Однако нужно признать, что имеющаяся ограниченность объективна, т.к. «нельзя объять необъятное» с одной стороны, и изучение специфических функций Excel нигде кроме Excel не потребуется с другой.

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b8%d0%b0%d0%bb%d0%be%d0%b3%d0%be%d0%b2%d0%be%d0%b5-%d0%be%d0%ba%d0%bd%d0%be-%d1%81%d0%be%d1%80%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%ba%d0%b8-%d0%bf%d1%80%d0%b5%d0%b4%d0%be

Рис. 1. Диалоговое окно сортировки предоставляет большой выбор параметров; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Расширяем возможности макросов в Excel за счет средств Visual Basic

Рекомендуемые диаграммы в Excel

Одна из новых возможностей Excel 2013 называется рекомендуемые диаграммы. Выделите ваши данные и выполните команду Вставка –> Диаграммы –> Рекомендуемые диаграммы. Excel отобразит вкладку Рекомендуемые диаграммы диалогового окна Вставка диаграммы (рис. 1). В этом окне можно предварительно просмотреть графическое представление данных, выбрав нужный вид диаграммы. [1]

%d1%80%d0%b8%d1%81-1-%d0%b2%d0%ba%d0%bb%d0%b0%d0%b4%d0%ba%d0%b0-%d1%80%d0%b5%d0%ba%d0%be%d0%bc%d0%b5%d0%bd%d0%b4%d1%83%d0%b5%d0%bc%d1%8b%d0%b5-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%8b

Рис. 1. Вкладка Рекомендуемые диаграммы позволяет отображать данные в виде нескольких доступных типов диаграмм; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Рекомендуемые диаграммы в Excel

Создание сводных таблиц с двумя видами группирования дат

Если вы создавали несколько сводных таблиц из одного и того же источника данных, то, возможно, замечали, что группирование поля в одной сводной таблице влияет на остальные таблицы. В частности, все остальные сводные таблицы начинают использовать такой же вариант группирования. Иногда именно это и требуется, но так бывает не всегда. Например, вам может понадобиться сделать два самостоятельных отчета в виде сводных таблиц: в одном данные обобщаются по месяцам, в другом по неделям. [1]

%d1%80%d0%b8%d1%81-1-%d0%b7%d0%b0%d0%b4%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b8%d0%bc%d0%b5%d0%bd%d0%b8-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be%d0%bd%d1%83

Рис. 1. Задание имени диапазону

Подробнее »Создание сводных таблиц с двумя видами группирования дат

Таблицы в Excel

Таблицы — важная функция Excel, но многие пользуются ими не слишком активно. В этой заметке описано, как работать с таблицей, перечислены ее достоинства и недостатки. [1] Таблица — эго прямоугольный диапазон ячеек, содержащий структурированные данные. Каждая строка таблицы соответствует одному объекту. Например, в строке может находиться информация о клиенте, банковской трансакции, сотруднике или товаре. В каждом столбце содержится определенный информационный фрагмент. Так, если в каждой строке мы имеем сведения об отдельном сотруднике, то в столбцах могут быть детали информации о нем — фамилия, номер, дата приема на работу, оклад, отдел. В верхней части таблицы расположена строка заголовка, описывающая данные, содержащиеся в каждом столбце.

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be%d0%bd-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d1%85-%d0%b0-%d0%b2-%d0%be%d0%b1%d1%8b%d1%87%d0%bd%d0%be%d0%bc-%d0%bf%d1%80%d0%b5%d0%b4%d1%81

Рис. 1. Диапазон данных: (а) в обычном представлении; (б) в виде таблицы

Подробнее »Таблицы в Excel

Excel. Выполнение нестрогого поиска

Если вы работаете с большим листом, на котором содержится множество данных, найти нужную информацию бывает непросто. Диалоговое окно Найти и заменить – удобный инструмент для поиска информации, но некоторые его возможности неизвестны большинству пользователей. [1] Чтобы открыть диалоговое окно Найти и заменить, нажмите Ctrl+F (откроется вкладка Найти) или Ctrl+H (Н английское, откроется вкладка Заменить). Чтобы увидеть дополнительные опции, нажмите кнопку Параметры (рис. 1).

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b8%d0%b0%d0%bb%d0%be%d0%b3%d0%be%d0%b2%d0%be%d0%b5-%d0%be%d0%ba%d0%bd%d0%be-%d0%bd%d0%b0%d0%b9%d1%82%d0%b8-%d0%b8-%d0%b7%d0%b0%d0%bc%d0%b5%d0%bd%d0%b8%d1%82%d1%8c

Рис. 1. Диалоговое окно Найти и заменить, открытое на вкладке Найти

Подробнее »Excel. Выполнение нестрогого поиска

Excel. Получение информации с веб-страницы

Если требуется регулярно обращаться к обновляемым данным с веб-страницы, создайте веб-запрос. На рис. 1 представлен сайт https://news.yandex.ru, показывающий курс доллара. [1]

%d1%80%d0%b8%d1%81-1-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b4%d0%b8%d0%b0%d0%bb%d0%be%d0%b3%d0%be%d0%b2%d0%be%d0%b3%d0%be-%d0%be%d0%ba%d0%bd%d0%b0-%d1%81

Рис. 1. Использование диалогового окна Создание веб-запроса для указания данных, которые следует импортировать; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Excel. Получение информации с веб-страницы

Excel. Мгновенное заполнение

При импорте данных часто требуется немного подправить текст. Например, имена были записаны в верхнем регистре, а в них нужно оставить заглавными только первые буквы. Один из вариантов — использовать формулы для изменения текста (например, =ПРОПНАЧ). Другой способ — применить новую функцию Excel 2013, которая называется Мгновенное заполнение. [1]

%d1%80%d0%b8%d1%81-1-%d0%bc%d0%b3%d0%bd%d0%be%d0%b2%d0%b5%d0%bd%d0%bd%d0%be%d0%b5-%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b1%d1%8b%d1%81%d1%82%d1%80%d0%be-%d0%bf%d1%80%d0%b5

Рис. 1. Мгновенное заполнение быстро преобразует имена из столбца А в нужный регистр

Подробнее »Excel. Мгновенное заполнение

Excel. Обнаружение лишних пробелов

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

%d1%80%d0%b8%d1%81-1-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d0%b0-%d0%bf%d0%be%d0%b8%d1%81%d0%ba%d0%b0-%d0%b2%d0%be%d0%b7%d0%b2%d1%80%d0%b0%d1%89%d0%b0%d0%b5%d1%82-%d1%86%d0%b2%d0%b5%d1%82-%d0%b2

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1Подробнее »Excel. Обнаружение лишних пробелов

Excel. Работа с номерами кредитных карт

Если вы когда-либо пытались вводить в ячейку шестнадцатизначный номер кредитной карты, то, вероятно, замечали, что Excel всегда заменяет последнюю цифру на 0. [1] Почему программа так поступает? Дело в том, что Excel обеспечивает обработку чисел с точностью только до 15 знаков (поэкспериментируйте).

%d1%80%d0%b8%d1%81-1-%d0%b2%d0%b2%d0%be%d0%b4-%d0%bd%d0%be%d0%bc%d0%b5%d1%80%d0%be%d0%b2-%d0%ba%d1%80%d0%b5%d0%b4%d0%b8%d1%82%d0%bd%d1%8b%d1%85-%d0%ba%d0%b0%d1%80%d1%82-%d1%81-%d0%b4%d0%b5%d1%84

Рис. 1. Ввод номеров кредитных карт с дефисами

Подробнее »Excel. Работа с номерами кредитных карт

Сравнение двух диапазонов с помощью условного форматирования

Если требуется сравнить два списка элементов и определить, какие различия есть между ними, воспользуйтесь условным форматированием (рис. 1). [1] В примере используется текст, но этот метод работает и с числовыми данными.

%d1%80%d0%b8%d1%81-1-%d0%bc%d0%be%d0%b6%d0%bd%d0%be-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d1%82%d1%8c-%d1%83%d1%81%d0%bb%d0%be%d0%b2%d0%bd%d0%be%d0%b5-%d1%84%d0%be%d1%80%d0%bc

Рис. 1. Можно использовать условное форматирование, чтобы наглядно показать различия двух диапазонов

Подробнее »Сравнение двух диапазонов с помощью условного форматирования