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

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

Задача: как-то раз вы наткнулись на удобное контекстное меню, дающее доступ к дополнительным параметрам копирования. Но не может вспомнить, как вы получили доступ к этому меню.

Решение: в Excel существует дополнительное (альтернативное) меню с полезными опциями (рис. 1). Чтобы открыть его, выделите ячейку или диапазон ячеек. Перейдите к правому краю данных. Щелкните правой кнопкой мыши и перетащите границу вправо в новую область. Отпустите кнопку мыши. Появится новое «секретное» меню (так называемое альтернативное меню перетаскивания; Alternate Drag and Drop menu).

Рис. 1. Альтернативное «секретное» меню перетаскивания

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

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

Задача: вы ввели формулу СУММ в строке 23 (рис. 1). В следующей строке вы бы хотели подсчитать СРЗНАЧ. Однако, если вы скопируете ячейку В23 в В24, диапазон, на который ссылается формула будет автоматически изменен.

Рис. 1. Вы хотите изменить функцию СУММ в строке 23 на СРЗНАЧ в строке 24

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

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

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

Рис. 1. Исходная структура данных

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

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

Проблема: вы создали сводную таблицу для суммирования продаж по клиентам. Теперь вы хотите отфильтровать результаты, чтобы отображались только покупатели с объемом продажи между $20 000 и $30 000. Команда автофильтр для сводных таблиц заблокирована.

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

Рис. 1. Выделите на одну ячейку больше, чем заголовки сводной таблицы

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

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

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

В базе данных (рис. 1) записи и столбцы пронумерованы. После применения автофильтра в столбце G (> 335) и скрытии двух столбцов (D и F), пропуски в автонумерации не появились (рис. 2).

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

Рис. 1. Полная база данных

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

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

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

Решение: предположим, что рабочий лист содержит список имен в диапазоне А2:А30 (рис. 1). Ячейка A1 содержит заголовок столбца. Вы можете решить эту проблему вручную, пройдя по меню: ДАННЫЕ –> Сортировка и фильтр –> Дополнительно. Откроется диалоговое окно Расширенный фильтр (рис. 2). Выберите опцию Скопировать результат в другое место, задайте исходный диапазон $A$1:$A$30, Диапазон условий оставьте пустым, в поле Поместить результат в диапазон установите ссылку $H$1, выберите Только уникальные записи, нажмите Оk.

Рис. 1. Диалоговое окно Расширенный фильтр

Подробнее »Как извлечь уникальные значения из списка

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

Задача: требуется построить формулу, которая возвращает сумму всех цифр в строке текста. Например, применяя формулу к текстовой строке «мне 24 года, а моему отцу – 43», хотелось бы получить 13 (2 + 4 + 4 + 3).

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

1 * количество единиц в строке +

2 * количество двоек в строке +

… +

9 * количество девяток в строке = результат

Чтобы узнать количество вхождений цифры (скажем, 4), можно воспользоваться функцией ПОДСТАВИТЬ с заменой цифры 4 на пустую строку. Например, ПОДСТАВИТЬ(текст;4;"") вернет текст без всех четверок. В нашем примере – «мне 2 года, а моему отцу – 3». Если подсчитать разность длин исходной и получившейся строк, то это и будет количество четверок в строке: =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;4;"")). Ответ = 2.

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

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

Задача: некоторые инженерные проблемы проектирования требуют использования таблиц для вычисления значений параметров. Поскольку таблицы являются дискретными, дизайнер использует линейную интерполяцию для получения промежуточного значения параметра. Таблица (рис. 1) включает высоту над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр). Например, если надо найти скорость ветра, соответствующую высоте 47 метров, то следует применить формулу: 130 + (180 – 130) * 7 / (50 – 40) = 165 м/сек.

Рис. 1. Высота над землей (управляющий параметр) и скорость ветра (рассчитываемый параметр)

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

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

Задача: если вам требуется запись текста в обратном порядке, можно воспользоваться кодом VBA. Однако, существует нетривиальный способ выполнить задачу с помощью формулы.

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

Выполните следующие действия.

Пройдите по меню ФАЙЛ –> Параметры. Перейдите на вкладку Формулы, поставьте галку напротив Включить итеративные вычисления. Установите Предельное число итераций – 100 (рис. 1). Нажмите Ok.

Рис. 1. Включить итеративные вычисления

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

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

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

Решение: ВПР не может решить эту проблему. Комбинация двух функций СМЕЩ и ПОИСКПОЗ могла бы это сделать, при условии, что таблица будет отсортирована по ключевому столбцу (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). Но если вам по каким-то соображения не удобно делать сортировку, можете воспользоваться пользовательской функцией VLOOKUPNTH (рис. 1). Вам нужно добавить эту функцию в проект VBA (если вы никогда не делали этого, см. Сумма по цвету ячеек в Excel). Чтобы получить доступ к коду в приложенном Excel-файле можно, например, кликнуть правой кнопкой мыши на ярлык листа и выбрать Исходный текст. Функция работает, как и ВПР, но вместо использования ЛОЖЬ/ИСТИНА в качестве четвертого аргумента, вы указываете, какое по порядку вхождение вы хотите получить для искомого значения.

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах, начиная с G

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