Ознакомительная версия. Доступно 12 страниц из 57
Еще один вариант — ссылаться на имя таблицы и столбца в ней через функцию ДВССЫЛ / INDIRECT. Эта функция принимает текст в качестве аргумента и превращает его в ссылку, то есть это такой непрямой способ ссылаться на диапазоны, и она в данном случае помогает обойти ограничение проверки данных.
В Excel 2007 и ранее в проверке данных в принципе нельзя было ссылаться на другой лист, это нужно было делать через ДВССЫЛ / INDIRECT, даже если список не предполагается обновлять.
Подробнее про выпадающие списки с обновлением смотрите в видео:
Выпадающие списки в Excel с автоматическим добавлением новых значений
https://mif.to/VRU7P
Проверка данных (Data Validation) с формулами
Для понимания этого раздела стоит разобраться с логическими значениями и формулами. Если вы еще не знакомы с этой темой, обратитесь к главе «Логические выражения и функция ЕСЛИ / IF».
Файл с примерами: 3 Проверка данных с формулами.xlsx
Формулы в проверке данных работают схожим образом с условным форматированием: вводите формулу для первой ячейки проверяемого диапазона и не забывайте закрепить ссылки (сделать их абсолютными или смешанными) при необходимости.
Для создания правила проверки с формулой нужно проследовать по следующему пути:
Данные → Проверка данных → Тип данных: Другой → Формула (Data → Data Validation → Allow: Custom → Formula).
Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение — зависит от настроек в разделе «Сообщение об ошибке» (Error Alert).
Рассмотрим несколько примеров проверки с формулами.
Разрешаем вводить текст только меньше определенной длины
Длину текста можно определить с помощью функции ДЛСТР / LEN. Она возвращает число — количество символов в ячейке. Соответственно, если мы хотим запретить ввод текста длиннее, допустим, десяти символов, то формула будет выглядеть следующим образом:
=ДЛСТР(ссылка на первую ячейку диапазона) <= 10
Разрешаем вводить только формулы
Функция ЕФОРМУЛА / ISFORMULA возвращает ИСТИНА, если ее аргумент — ячейка с формулой. Соответственно, следующая формула в проверке данных будет разрешать ввод только формул.
=ЕФОРМУЛА(ссылка на первую ячейку диапазона)
Разрешаем вводить только уникальные значения
Уникальные значения — это значения, которые в диапазоне встречаются всего лишь один раз. Подсчитать количество можно с помощью СЧЁТЕСЛИ / COUNTIF:
=СЧЁТЕСЛИ(диапазон; критерий)
В случае с проверкой данных мы будем проверять, сколько раз каждое конкретное значение (начиная с первой ячейки диапазона) будет встречаться во всем диапазоне:
=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)
И сравнивать это с единицей: если значение будет встречаться более одного раза, его вводить уже нельзя.
=СЧЁТЕСЛИ(проверяемый диапазон; первая ячейка этого диапазона)<=1
Если нам нужно запретить ввод повторяющихся значений в диапазоне A2:A30, то формула будет выглядеть так:
=СЧЁТЕСЛИ(A$2:A$30; A2)<=1
Сортировка данных
Сортировка данных — упорядочивание по значениям в одном или нескольких столбцах. Без этой операции никуда: а как иначе посмотреть топ продаж по какому-либо параметру (городам, товарам и т. д.)? Это помогает упорядочить таблицу, анализировать данные становится проще.
Быстрее всего отсортировать данные по возрастанию или убыванию можно на вкладке «Данные».
Выделяем ячейку в том столбце, по которому нужно отсортировать, и нажимаем соответствующую кнопку. Так будет выглядеть начало нашего списка сделок после сортировки по возрастанию (от старых к новым) по столбцу D.
Если вы часто прибегаете к такой быстрой сортировке (по одному столбцу), можно добавить кнопки для сортировки на панель быстрого доступа.
Кроме того, сортировать можно с помощью фильтра (о нем ниже).
А если простой сортировки по одному столбцу недостаточно, открывайте окно «Сортировка» (одноименная кнопка на ленте).
Здесь есть возможность сортировать по нескольким столбцам. Допустим, мы хотим упорядочить таблицу по клиентам, а в рамках каждого клиента — по сумме, чтобы самые крупные сделки были сверху. Тогда мы добавляем два соответствующих уровня сортировки в этом окне — сначала компанию, а затем, нажав кнопку «Добавить уровень» (Add Level), — сумму.
Результат:
В «Параметрах» в сортировке есть две опции: можно сортировать не строки, а столбцы и учитывать регистр текста при сортировке.
Сортировка с помощью пользовательских списков
Не всегда нужно сортировать данные по алфавиту.
Возможно, вы уже обращали внимание, что месяцы или дни недели сортируются не по алфавиту, а в правильном (хронологическом) порядке. Это происходит потому, что в Excel встроены соответствующие списки для сортировки — с короткими и длинными названиями месяцев и дней недели. Мы не можем их удалить или изменить, но можем добавлять новые!
Допустим, в вашей компании есть правила сортировки списка филиалов — не по алфавиту. В таком случае стоит создать пользовательский список.
Списки добавляются и удаляются в параметрах Excel → Дополнительно → Общие (раздел в самом конце вкладки «Дополнительно») → Изменить списки (Options → Advanced → General → Edit Custom Lists).
В окне со списками есть списки дней недели и месяцев — их редактировать и удалять нельзя. Но можно добавлять свои.
После добавления сортировка в диапазонах, таблицах и сводных таблицах может осуществляться по такому порядку. Кроме того, список можно создать непосредственно в процессе сортировки. В любом случае нужно выбрать «Настраиваемый список…» (Custom List…) в окне сортировки.
И далее в открывшемся окне выбрать список (или добавить новый).
Еще один плюс пользовательских списков: они работают при протягивании.
В Google Таблицах пользовательских списков нет.
Фильтрация данных
Фильтрация данных — еще одна базовая операция, без которой никуда в Excel или Google Таблицах. Фильтрация — отбор и отображение строк по заданным критериям (текстовым, числовым или даже по цвету ячейки/шрифта).
Фильтр можно установить и снять сочетанием клавиш Ctrl + Shift + L или на ленте инструментов на вкладке «Данные» (Data → Filter).
Кроме того,
Ознакомительная версия. Доступно 12 страниц из 57
