Читаем Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) полностью

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

Символы подстановки

Что, если мы хотим отфильтровать данные только по юрлицам (ООО и ОАО, но не ИП)?

Можно воспользоваться символами подстановки. Напомним: звездочка заменяет любой текст от 0 до бесконечности символов, а знак вопроса — это один любой символ.

Вот такое условие позволит отфильтровать по условию «ячейка начинается с буквы О, любого символа и затем снова буквы О», которому будут соответствовать и ООО, и ОАО:

О?О*

Следующие условия — это продажи курсов и консультаций ООО и ОАО.

А если в примере с филиалами вам нужны модели только одного бренда, можно добавить условие на названия товаров — бренд, окруженный звездочками (то есть любой текст + название бренда + любой текст).

<p>Функции баз данных</p>

Файл с примерами: Расширенный фильтр и функции БД.xlsx

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

Это диапазон ячеек, в первой строке которого должны быть названия тех полей (столбцов), на которые накладываются условия. Одна строка под этими заголовками — это один набор условий. Если строк более одной — значит, будут подсчитываться все варианты.

Эти функции удобны, когда нужно проанализировать данные по большому списку филиалов, фамилий, рекламных кампаний и любых других признаков, чтобы не использовать много функций типа СУММЕСЛИМН / SUMIFS (по функции на каждый набор условий), а сразу получить результат.

Синтаксис функций баз данных на примере ДСРЗНАЧ / DAVERAGE (вычисление среднего значения):

=ДСРЗНАЧ(данные; столбец; критерии)

• данные (database) = исходная таблица с заголовками;

• столбец (field) = ссылка на заголовок или текст с заголовком того столбца, по которому нужно вести расчет (суммировать/усреднять/извлекать значение, вычислять максимальное или минимальное значение и так далее);

• критерии (criteria) = ссылка на диапазон с условиями.

Если нам нужна сумма остатков по списку филиалов и одному бренду (из последнего примера), сработает такая функция:

=БДСУММ(A1:C500;C1;E2:F17)

Второй аргумент может быть в виде текста заголовка, а не ссылки на него:

=БДСУММ(A1:C500;"остаток, шт.";E2:F17)

А если нужно количество сделок с двумя продуктами и условием на сумму сделки по одному из них (один из наших примеров), понадобится функция БСЧЁТ / DCOUNT или БСЧЁТА / DCOUNTA (первая считает числа, вторая — любые значения):

=БСЧЁТА(Сделки[#Все];Сделки[[#Заголовки];[Компания]];I4:J6)

Вот список всех функций баз данных, отличаются они типом вычисления, а синтаксис у всех схожий.

<p>Функции для поиска и извлечения значений</p>

Файл с примерами. "Функции поиска"

ФУНКЦИЯ ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — подтянуть данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции):

=ВПР (что ищем; таблица с данными, где "что ищем" должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы — интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, если нужно конвертировать оценку из одной системы в другую и так далее, то используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам, в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» (range_lookup) равным нулю (или ЛОЖЬ / FALSE). Только тогда функция будет корректно работать с текстовыми значениями.

ФУНКЦИИ ПОИСКПОЗ / MATCH И ИНДЕКС / INDEX

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

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

Перейти на страницу:

Все книги серии МИФ. Бизнес

Похожие книги

1С: Бухгалтерия 8 с нуля
1С: Бухгалтерия 8 с нуля

Книга содержит полное описание приемов и методов работы с программой 1С:Бухгалтерия 8. Рассматривается автоматизация всех основных участков бухгалтерии: учет наличных и безналичных денежных средств, основных средств и НМА, прихода и расхода товарно-материальных ценностей, зарплаты, производства. Описано, как вводить исходные данные, заполнять справочники и каталоги, работать с первичными документами, проводить их по учету, формировать разнообразные отчеты, выводить данные на печать, настраивать программу и использовать ее сервисные функции. Каждый урок содержит подробное описание рассматриваемой темы с детальным разбором и иллюстрированием всех этапов.Для широкого круга пользователей.

Алексей Анатольевич Гладкий

Программирование, программы, базы данных / Программное обеспечение / Бухучет и аудит / Финансы и бизнес / Книги по IT / Словари и Энциклопедии
1С: Управление торговлей 8.2
1С: Управление торговлей 8.2

Современные торговые предприятия предлагают своим клиентам широчайший ассортимент товаров, который исчисляется тысячами и десятками тысяч наименований. Причем многие позиции могут реализовываться на разных условиях: предоплата, отсрочка платежи, скидка, наценка, объем партии, и т.д. Клиенты зачастую делятся на категории – VIP-клиент, обычный клиент, постоянный клиент, мелкооптовый клиент, и т.д. Товарные позиции могут комплектоваться и разукомплектовываться, многие товары подлежат обязательной сертификации и гигиеническим исследованиям, некондиционные позиции необходимо списывать, на складах периодически должна проводиться инвентаризация, каждая компания должна иметь свою маркетинговую политику и т.д., вообщем – современное торговое предприятие представляет живой организм, находящийся в постоянном движении.Очевидно, что вся эта кипучая деятельность требует автоматизации. Для решения этой задачи существуют специальные программные средства, и в этой книге мы познакомим вам с самым популярным продуктом, предназначенным для автоматизации деятельности торгового предприятия – «1С Управление торговлей», которое реализовано на новейшей технологической платформе версии 1С 8.2.

Алексей Анатольевич Гладкий

Финансы / Программирование, программы, базы данных