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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

… +

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel. Даты до 1900 года

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

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

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

Решение: предлагается формула, которая работает с любыми датами с 1 января 1000 г. Она основана на добавлении 1000 лет ко всем датам. Таким образом, допустимая дата, например, 23 января 2009, станет 23 января, 3009. При этом недопустимая дата, например, 17 февраля 1865, станет допустимой – 17 февраля 2865. Поскольку Excel может работать с датами вплоть до 9999 года, то с этой стороны проблем не предвидится.

Введите начальную и конечную даты в ячейки А4 в В4 (рис. 1). Используйте, например, формат 17/02/1865. Если ваша дата после 1900, Excel автоматически преобразует дату в порядковый номер. Если дата до 1900, Excel сохранит ее в виде текста. Я использовал условное форматирование для области А4:В14 на основе формулы =НЕ(ЕТЕКСТ(A4)), которое подкрасило ячейки с датами.

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете эту формулу

Рис. 1. Кажется, что Excel обрабатывает даты до 1900 года, когда вы используете спецформулу

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

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

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

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

Задача: у вас есть числа в диапазоне D2:D11. Вам нужна формула, чтобы переставить числа по возрастанию или по убыванию.

Решение: вы можете использовать функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ (рис. 1). =НАИМЕНЬШИЙ($D$2:$D$11;1) возвращает наименьшее число в диапазоне, =НАИМЕНЬШИЙ($D$2:$D$11;2) возвращает второе минимальное число, и так далее.

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

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

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

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

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

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

Задача: вы хотите сгенерировать случайные числа между 1 и 100, но… без дубликатов. Excel стандартно предлагает функции СЛЧИС() и СЛУЧМЕЖДУ(), но обе они, генерируют совершенно случайные числа с морем дубликатов.

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k). Как правило, она возвращает k-е наименьшее значение массива. Например, =НАИМЕНЬШИЙ({69;9;12;27;42;55};3) возвращает 27, так как, 1-е наименьшее число в списке – 9, 2-е – 12, а 3-е – 27. Нечисловые значения в массиве игнорируются. Например, =НАИМЕНЬШИЙ({69;9;12;ЛОЖЬ;42;55};3) вернет 42.

Решение: сейчас мы разработаем формулу, которая будет формировать массив из неиспользованных чисел диапазона, а затем выбирать из этих чисел (рис. 1).

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

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