​Определение критерия проверки

Для определения типа данных, которые могут содержаться в ячейке или диапазоне ячеек, необходимо выполнить следующие действия.
1. Выделите ячейку или диапазон ячеек.
2. Выберите команду Данные => Работа с данными => Проверка данных. Excel выведет диалоговое окно Проверка вводимых значений.
3. Щелкните на корешке вкладки Параметры.
4. Выберите одно из значений в раскрывающемся списке Тип данных. В зависимости от выбранного варианта внешний вид вкладки Параметры может измениться за счет добавления или удаления дополнительных элементов управления. (Для определения формулы нужно выбрать опцию Другой.)
5. С помощью имеющихся на этой вкладке элементов управления задайте критерий проверки данных. Доступные элементы управления зависят от выбора, сделанного на предыдущем этапе.
6. (Дополнительно.) Щелкните на вкладке Подсказка по вводу и введите сообщение, которое должно появляться на экране при выделении указанной ячейки. Это сообщение выводится для того, чтобы сообщить пользователю, какие данные ему можно вводить. Если пропустить этот шаг, то при выделении ячейки не появится никакого сообщения.
7. (Дополнительно.) Щелкните на вкладке Сообщение об ошибке и введите сообщение, которое должно появляться в случае, если пользователь введет недопустимое значение. Выбор вида сообщения в списке Вид определит, какой выбор будет у пользователя при внесении неверных данных. Для предотвращения ввода неправильных значений нужно выбрать вид сообщения Остановка. Если пропустить этот шаг, то при возникновении ошибки будет появляться стандартное сообщение об ошибке.
8. Щелкните на кнопке ОК. После выполнения указанных действий в выделенной ячейке или диапазоне будет выполняться проверка вводимых данных.

#ПроверкаДанных

​Типы проверяемых данных

Вкладка Параметры диалогового окна Проверка вводимых значений предоставляет большой выбор типов данных для условий проверки. Выбрать тип допустимых данных можно в раскрывающемся списке Тип данных (как уже отмечалось, остальные элементы управления этой вкладки изменяются в зависимости от выбранного типа данных). Можно выбрать следующие типы данных:
▪️ Любое значение. Выбор этого варианта приводит к удалению условия проверки данных. Однако сообщение для ввода все равно будет выводиться, если не сбросить флажок Отображать подсказку, если ячейка является текущей на вкладке Подсказка по вводу.
▪️ Целое число. Пользователь должен ввести целое число. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, можно определить, что вводимое значение должно быть целым числом, большим или равным 100.
▪️ Действительное. Пользователь должен ввести действительное число. Диапазон допустимых значений можно определить с помощью раскрывающегося списка Значение. Например, можно определить, что вводимое число должно быть между 0 и 1.
▪️ Список. Пользователь должен выбрать значение из предложенного списка значений. Небольшой список можно здесь же ввести в поле Источник (значения в списке должны отделяться точкой с запятой) либо указать в виде диапазона ячеек.
▪️ Дата. Пользователь должен ввести дату. С помощью раскрывающегося списка Значение можно определить допустимый диапазон дат. Например, можно определить, что вводимая дата должна быть больше или равна 1 января 2019 года и меньше или равна 31 декабря 2019 года.
▪️ Время. Пользователь должен ввести значение времени. С помощью раскрывающегося списка Значение можно определить допустимый диапазон значений. Например, вводимое значение времени должно быть больше, чем 10:00.
▪️ Длина текста. Ограничивается максимальная длина вводимой строки (количество символов). С помощью раскрывающегося списка Значение можно определить допустимую длину строки. Например, можно установить, что длина вводимой строки должна равняться одному символу.
▪️ Другой. В этом случае потребуется определить логическую формулу, которая определяет правильность вводимых пользователем данных (напомним, что логическая формула возвращает значение ИСТИНА или ЛОЖЬ). Можно ввести формулу непосредственно в поле Формула (которое появляется после выбора этого типа проверки) или определить ссылку на ячейку с формулой. Далее в этой главе приводятся примеры нескольких полезных формул.

Также на вкладке Параметры диалогового окна Проверка вводимых значений присутствуют следующие флажки опций:
▪️ Игнорировать пустые ячейки. Если этот флажок установлен, то допускается оставить данную ячейку пустой.
▪️ Список допустимых значений. Этот флажок появляется только при выборе в списке Тип данных значения Список и управляет выводом рядом с текущей ячейкой кнопки со стрелкой, предназначенной для раскрытия списка допустимых значений, в котором пользователь сможет сделать свой выбор.
▪️ Распространить изменения на другие ячейки с тем же условием. Если этот флажок установлен, то все внесенные изменения будут применяться и к другим ячейкам, которые содержат исходные условия проверки данных.

#ПроверкаДанных

​Выделение неверных данных

В Excel имеется команда Данные => Работа с данными => Проверка данных => Обвести неверные данные, после выбора которой все неверные значения (с точки зрения средств проверки данных) будут обведены красными кружками. После исправления неверного значения кружок исчезнет.
Для того чтобы убрать кружки с рабочего листа, выберите команду Данные => Работа с данными => Проверка данных => Удалить обводку неверных данных. В примере все значения, большие 200 и меньшие 10, являются неверными.

#ПроверкаДанных

​Создание раскрывающегося списка

Чаще всего средство проверки вводимых данных используется для создания раскрывающегося списка допустимых значений, которые можно вводить в данную ячейку. Ниже пример, в котором названия месяцев, содержащиеся в диапазоне А1:А12, используются для создания такого раскрывающегося списка.

Чтобы создать такой список, выполните следующие действия:
1. Введите список допустимых значений в один столбец или строку. Эти значения будут присутствовать в раскрывающемся списке;
2. Выберите ячейку, которая должна содержать раскрывающийся список, а затем команду Данные => Работа с данными => Проверка данных. В результате откроется диалоговое окно Проверка вводимых значений;
3.  На вкладке Параметры диалогового окна Проверка вводимых значений выберите тип данных Список и в поле Источник укажите диапазон, который содержит требуемый список значений. Диапазон может находиться на другом рабочем листе, но он должен принадлежать этой же рабочей книге;
4.  Убедитесь, что флажок Список допустимых значений установлен;
5.  Выполните установку значений любых других требуемых параметров в диалоговом окне Проверка вводимых значений;
6.  Щелкните на кнопке ОК. После выполнения этих действий при выборе ячейки справа от нее будет появляться кнопка раскрывающегося списка;
7.  Щелкните на этой кнопке, чтобы развернуть список и выбрать требуемое значение.

#ПроверкаДанных

​Ввод только текста

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

=ЕТЕКСТ(A1)

#ПроверкаДанных

​Ввод значений, больших, чем в предыдущей ячейке

Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке:

=А2>А1

В формуле предполагается, что активной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.

#ПроверкаДанных

​Ввод только уникальных значений

Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне A1:C20 повторяющиеся значения:

=СЧЁТЕСЛИ($A$1:$C$10;A1)=1

Это логическая формула, возвращающая значение ИСТИНА в случае, если значение из ячейки появляется в диапазоне А1:С20 только один раз. В противном случае эта формула возвращает значение ЛОЖЬ.

Здесь также предполагается, что А1 является активной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ() используется абсолютная ссылка. Вторым аргументом является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона.

#ПроверкаДанных

​Excel позволяет быстро анализировать данные, визуально представлять работу, составлять отчёты, на их основе оптимизировать затраты и увеличивать прибыль. Время и ресурсы, потраченные на изучение Excel, станут ценным вложением в сотрудников компании.
Преимущества использования Excel и Google Таблиц:

1 — Экономия средств.

Для автоматизации бизнес-процессов нужно потратить ресурсы — денежные и временные. Обучить сотрудников Excel дешевле и быстрее, чем внедрить автоматизацию.

2 — Одна программа во всех бизнес-процессах.

Excel — универсальный продукт. Можно создавать и быстро заполнять базы данных, проводить сложные расчёты, анализировать данные любой сложности, использовать в качестве CRM-системы.

3 — Визуализация данных.

Визуализация фиксирует внимание на главном и быстро доносит основные мысли и идеи. Отчёты перестанут быть безликими столбцами цифр.

Вы научитесь работать с данными на онлайн-курсе «Excel + Google-таблицы для решения бизнес-задач» от Skillbox. Всего за 4 месяца узнаете, как автоматизировать свою работу с помощью скриптов и макросов, и освободите время для других задач. А также поймёте, как составлять сложные отчёты и строить прогнозы с помощью таблиц.

Переходите по ссылке: https://clc.am/XNKH8w , чтобы посмотреть программу онлайн-курса или зарегистрироваться.

​Ввод дат по дню недели

Следующая формула проверки данных предполагает, что содержимое ячейки является датой, и подтверждает, что эта дата является понедельником:

=ДЕНЬНЕД(A1)=2

При вводе этой формулы предполагается, что активной ячейкой диапазона является ячейка А1. В ней задействована функция ДЕНЬНЕД(), возвращающая значение 1 для воскресенья, 2 — для понедельника и т.д. Обратите внимание, что функция ДЕНЬНЕД() принимает в качестве аргумента любое неотрицательное значение (не только даты).

#ПроверкаДанных

​Ввод значений, не превышающих суммы

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

=СУММ($B$1:$B$6)<=$E$1

#ПроверкаДанных

​Создание зависимых списков

Как говорилось ранее, проверку данных можно использовать для создания раскрывающихся списков в ячейках. Данный пример посвящен использованию списка, управляющего записями во втором раскрывающемся списке. Другими словами, второй раскрывающийся список зависит от значения, выбранного в первом раскрывающемся списке.
На видео представлен простой пример зависимого списка, созданного с применением механизма проверки данных. В ячейке F2 выполняется проверка данных, отображающая список из трех элементов диапазона A1:C1 (Овощи, Фрукты и Ягоды). Когда пользователь выбирает элемент из списка, второй список (в ячейке G2) отображает элементы, соответствующие элементу первого списка.
На этом рабочем листе используются три именованных диапазона:
▪️ Овощи - A2:A9
▪️ Фрукты - B2:B6
▪️ Ягоды - C2:C5
В ячейке G2 содержится следующая формула проверки данных:

=ДВССЫЛ($F2)

Поэтому раскрывающийся список, представленный в ячейке G2, зависит от значения, отображенного в ячейке F2.

#ПроверкаДанных

​Ввод текста, начинающегося с определенного символа

В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра).

=ЛЕВСИМВ(А1)="а"

Это логическая формула, возвращающая значение истина в том случае, если первым символом ячейки является буква А. В любом другом случае эта формула возвращает значение ЛОЖЬ. При вводе этой формулы предполагается, что активной ячейкой выделенного диапазона является ячейка А1.

Следующая формула является вариантом предыдущей формулы. В ней используются групповые символы во втором аргументе функции СЧЁТЕСЛИ(). В этом случае формула проверки данных гарантирует, что содержимое ячейки начинается с буквы А и содержит ровно пять символов:

=СЧЁТЕСЛИ(B1;"А????")=1

#ПроверкаДанных

​Вывод сообщения с напоминанием

Средство проверки данных Excel обеспечивает возможность вывода пользователю предупреждающего сообщения, когда он выбирает определенную ячейку. Обычно это сообщение должно подсказывать пользователю, какие именно данные для данной ячейки будут расценены как некорректные, и предупреждать его о том, что он получит сообщение об ошибке, если все-таки попробует ввести в эту ячейку недопустимые для нее данные. Тем не менее это сообщение можно использовать и для любых иных целей.

В примере представлено сообщение, напоминающее пользователю о необходимости завершить предыдущий этап работы. В этом случае в диалоговом окне Проверка вводимых значений в списке Тип данных на вкладке Параметры выбирается вариант Любое значение, что оставляет пользователю возможность вводить в эту ячейку любые данные. Фактически это сообщение - просто напоминание, связанное с той ячейкой, которой пользователь непременно воспользуется в самом начале работы с данной рабочей книгой.

#ПроверкаДанных

​Структурирование рабочих листов

Если вы работали когда-нибудь с текстовым процессором, то наверняка знаете, что такое структура. Большинство текстовых процессоров (таких, как, например, Microsoft Word) поддерживает режим структуры, который позволяет просмотреть только заголовки и подзаголовки документа. Однако вы можете легко расширить структуру так, чтобы, кроме заголовков, увидеть расположенные под ними элементы (т.е. текст). В этом режиме очень удобно просматривать и анализировать структуру документов.
В Excel также можно использовать структуру, что значительно облегчает работу с большими массивами данных на рабочих листах.

Работая со структурами, нужно учитывать следующее:
▪️ Один рабочий лист может иметь только одну структуру. Если нужно создать несколько структур (на основе разных иерархий данных), для создания каждой из них используйте отдельный рабочий лист.
▪️ Можно создать структуру вручную или позволить Excel сделать это автоматически. Выбирая второй вариант, следует заранее подготовить таблицу данных, чтобы она была правильно отформатирована
▪️ Можно создать структуру для всех данных рабочего листа или только для выбранного диапазона данных.
▪️ Удалить структуру можно с помощью всего лишь одной команды: Данные => Структура => Разгруппировать => Удалить структуру.
▪️ Для освобождения пространства экрана символы структуры можно скрыть, оставив при этом саму структуру.
▪️ Структура может иметь до восьми вложенных уровней.

#Структурирование

​Подготовка данных

Данные какого типа пригодны для создания структуры? Как правило, данные должны иметь иерархическую организацию, примером которой может служить бюджет, построенный по следующей схеме:

Фирма
  Офисы и филиалы
    Отдел
      Категория бюджета
        Статья бюджета

В данном случае каждая статья бюджета (например, Доходы от реализации товаров и услуг и Доходы от прочей деятельности) является частью некоторой категории бюджета (например, Доходы). Каждый отдел имеет собственный бюджет, подразделения состоят из отделов. Офисы и филиалы, в свою очередь, составляют фирму в целом. Такой тип организации данных подходит для создания вертикальной структуры (по строкам).
Создав структуру, можно просмотреть информацию на любом из ее уровней. Структура часто используется при создании отчетов для представления руководителям разных уровней. Так, высшему руководству фирмы представляются только итоги по подразделениям, руководителям подразделений - итоги по отделам, а каждому руководителю отдела - все данные по его отделу.
Следует помнить, что с помощью структур нельзя скрыть данные. Данные, скрытые при сворачивании структуры, могут быть легко обнаружены при ее разворачивании.
Данные, зависящие от времени, которые могут быть "свернуты" в более длительные временные интервалы (например, кварталы), подходят для создания горизонтальной структуры (по столбцам). Принципы работы с вертикальной и горизонтальной структурами - одни и те же, причем уровни последней не обязательно должны быть связаны со временем.
Прежде чем создавать структуру, следует убедиться в том, что все итоговые формулы введены правильно и согласованно. Согласованность означает, что все формулы имеют одинаковое относительное положение на рабочем листе. Как правило, формулы для вычисления итогов (например, промежуточные итоги) находятся под данными, к которым они относятся. Однако в некоторых случаях формулы для подсчета итогов располагаются над соответствующими диапазонами. Excel признает любой из этих методов, но вы должны быть последовательны в их использовании. Иначе, если используемые на листе формулы не будут согласованы, при автоматическом создании структуры вам не удастся получить желаемый результат.

#Структурирование

​Автоматическое создание структуры

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

📌 Если вы решите автоматически создать структуру на основе таблицы, созданной с помощью команды Вставка => Таблицы => Таблица, то программа не сможет это сделать. Создавать структуры на основе таблиц можно только вручную.

Чтобы автоматически создать структуру для некоторого диапазона данных, поместите табличный курсор в любую ячейку этого диапазона. Далее выберите команду Данные => Структура => Группировать => Создать структуру. Excel проанализирует формулы из выделенного диапазона и создаст структуру. В зависимости от формул будет создана либо горизонтальная, либо вертикальная структура, либо обе сразу. Если рабочий лист уже имеет структуру, будет задан вопрос, не хотите ли вы изменить ее. Щелкните на кнопке ОК, чтобы удалить старую структуру и создать новую.

#Структурирование

​​Создание структуры вручную

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

Чтобы создать группу строк, выполните следующие действия:
1. Выделите полностью все строки, которые нужно включить в группу. Единственный способ это сделать - щелкнуть на номере строки, а затем провести указателем мыши для того, чтобы выбрать соседние строки.
2. Выберите команду Данные => Структура => Группировать => Группировать. Excel отобразит символы для созданной группы.
3. Повторите эти действия для каждой создаваемой группы. При сжатии структуры все строки, составляющие группу, становятся скрытыми, однако итоговая строка, не включенная в группу, скрыта не будет и останется видимой.

Чтобы создать многоуровневую структуру, можно объединить несколько групп в одну. При создании многоуровневой структуры всегда следует начинать с самого нижнего уровня иерархии. Если случайно были сгруппированы неправильные столбцы или строки, их можно разгруппировать с помощью команды Данные => Структура => Разгруппировать => Разгруппировать.

Чтобы ускорить процесс группирования и разгруппирования, можно воспользоваться такими комбинациями клавиш:
<Alt+Shift+стрелка вправо> - группирование выбранных строк или столбцов;
<Alt+Shift+стрелка влево> - разгруппирование выбранных строк или столбцов.

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

#Структурирование

​​Добавление данных к структуре

Иногда возникает необходимость добавить к структуре дополнительные строки или столбцы. В некоторых случаях можно, не затрагивая структуру, вставить новые строки или столбцы так, чтобы они стали ее составной частью. Однако в определенных ситуациях может обнаружиться, что новая строка (или столбец) не стала частью структуры. Если структура была создана автоматически, еще раз выберите команду Данные => Структура => Группировать => Создать структуру. Excel попросит подтвердить, что вы хотите изменить существующую структуру.

Если же структура была создана вручную, то и изменения в нее нужно вносить вручную.

#Структурирование

​​Сокрытие символов структуры

Символы структуры занимают достаточно много места на экране (насколько много, зависит от количества уровней). Если же вам нужно максимально использовать пространство экрана, временно скройте эти символы, не удаляя структуру.
Для этого воспользуйтесь комбинацией клавиш <Ctrl+8>, которая, работая, как переключатель, отображает или скрывает символы структуры. Но учтите: если символы структуры скрыты, вы не сможете вручную расширять или сворачивать уровни структуры.

📌 Хотя символы структуры могут быть скрыты, сама структура при этом сохраняется и в рабочем листе отображаются данные ее текущего уровня, поэтому некоторые строки или столбцы могут быть скрыты.

#Структурирование

​​Настройка символов структуры

При создании структуры вручную программа Excel ставит символы структуры под итоговыми строками. Это может оказаться очень неудобно и непонятно, так как пользователю приходится щелкать на символе, который находится под расширяемым разделом.
Если вы предпочитаете, чтобы символы структуры появлялись в той же строке, что и итоговая строка, щелкните на кнопке открытия диалогового окна, расположенной в нижнем правом углу группы Данные => Структура. Программа Excel выведет диалоговое окно Параметры. Сбросьте флажок итоги в строках подданными и щелкните на кнопке ОК.

#Структурирование