В этом видео мы рассмотрим некоторые формулы, которые можно использовать для запроса таблицы.
Поскольку таблицы поддерживают структурированные ссылки, вы можете многое узнать о таблице с основными формулами.
На этом листе Таблица1 содержит данные о сотрудниках. Давайте рассмотрим несколько примеров.
Для начала вы можете использовать функцию ROWS для подсчета строк таблицы. Это только количество строк данных. Как видите, у нас в списке 19 человек.
= СТРОКИ (Таблица1)
Функция COLUMNS будет делать то же самое для столбцов.
= КОЛОННЫ (Таблица1)
Чтобы получить общее количество ячеек таблицы, вы можете использовать формулу с обеими функциями.
= СТРОКИ (Таблица1) * КОЛОННЫ (Таблица1)
Для подсчета пустых ячеек можно использовать такую функцию, как COUNTBLANK.
= СЧИТАТЬПУСТОТЫ (Таблица1)
Чтобы подсчитать видимые строки, вам необходимо использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, ссылаясь на столбец, не содержащий пустых ячеек.
В этом случае ID является обязательным значением, я использую 103 как номер функции и столбец ID для справки.
= ПРОМЕЖУТОЧНЫЙ ИТОГ (103; Таблица1 (ID))
Число 103 указывает SUBTOTAL подсчитывать значения только в видимых строках.
Теперь, если я фильтрую таблицу, количество видимых строк уменьшается, а когда я очищаю фильтр, оно снова увеличивается.
SUBTOTAL часто появляется в таблицах, так как исключает отфильтрованные строки.
Лист 3 в этой книге содержит полный список операций, доступных с SUBTOTAL, и ссылку на дополнительную информацию на нашем сайте.
Чтобы получить значение из итоговой строки, вы можете использовать спецификатор #Totals. Просто укажите и щелкните.
= Таблица1 ((# Итоги), (Группа))
Excel вернет ошибку #REF, если строка итогов не отображается.
Вы можете перехватить эту ошибку с помощью ЕСЛИОШИБКА и вернуть пустую строку, если общая строка отключена.
= ЕСЛИОШИБКА (Таблица1 ((# Итоги); (Группа)), "")
В столбце с числовыми данными, таком как столбец даты начала, вы можете использовать MIN и MAX для получения самой ранней и самой поздней дат.
= МИН (Таблица1 (Начало))
= МАКС (Таблица1 (Начало))
Если вы хотите, чтобы они отвечали фильтру, используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ с 105 и 104.
= ПРОМЕЖУТОЧНЫЙ ИТОГ (105, Таблица1 (начало)) - мин.
= ПРОМЕЖУТОЧНЫЙ ИТОГ (104, Таблица1 (начало)) - макс.
Такие функции, как СЧЁТЕСЛИ и СУММЕСЛИ, также хорошо работают с таблицами. Я могу легко подсчитать количество каждой группы с помощью СЧЁТЕСЛИ.
= СЧЁТЕСЛИ (Таблица1 (Группа); I17)
Как всегда, лучшая часть использования таблицы для данных - это динамический диапазон. Когда я вставляю больше данных, все формулы мгновенно обновляются.
Курс
Таблицы ExcelСвязанные ярлыки
Ввод и перемещение вниз Enter Return Ввод и перемещение вправо Tab Tab Перемещение на одну ячейку вниз ↓ ↓ Завершить запись и оставаться в той же ячейке Ctrl + Enter ⌃ + Return Выбрать столбец таблицы Ctrl + Space ⌃ + Space Выбрать таблицу Ctrl + A ⌘ + A Копировать выбранные ячейки Ctrl + C ⌘ + C Вставить содержимое из буфера обмена Ctrl + V ⌘ + V
