Системный и Бизнес-анализ | Microsoft Excel

2021-01-20 10:01:00 ​Ввод только уникальных значений

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

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

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

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

#ПроверкаДанных Открыть/Комментировать

2021-01-19 10:01:00 ​Ввод значений, больших, чем в предыдущей ячейке

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

=А2>А1

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

#ПроверкаДанных Открыть/Комментировать

2021-01-18 10:01:00 ​Ввод только текста

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

=ЕТЕКСТ(A1)

#ПроверкаДанных Открыть/Комментировать

2021-01-15 10:01:00 ​Создание раскрывающегося списка

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

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

#ПроверкаДанных Открыть/Комментировать

2021-01-14 10:01:00 ​Выделение неверных данных

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

#ПроверкаДанных Открыть/Комментировать

2021-01-13 10:01:00 ​Типы проверяемых данных

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

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

#ПроверкаДанных Открыть/Комментировать

2021-01-12 10:01:01 ​Определение критерия проверки

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

#ПроверкаДанных Открыть/Комментировать

2021-01-11 10:01:00 ​Средства проверки данных

Механизм проверки данных в Excel позволяет задать определенные правила, по которым будет контролироваться корректность ввода данных в ячейки. Например, пусть необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. И если предварительно описать это в правилах проверки данных, то в случае, если пользователь введет неправильное значение, программа выведет соответствующее сообщение, как показано в примере.
Программа Excel позволяет легко определять критерии для проверки данных, в том числе с использованием формул, что дает возможность создавать достаточно сложные критерии.

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

#ПроверкаДанных Открыть/Комментировать

2020-12-30 14:01:00 ​Методы предварительной подготовки данных. Классификация значений

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

Таблица просмотра, состоящая из двух столбцов, размещается в диапазоне D2:E84. Хранящаяся в ячейке В2 формула копируется в ячейки, расположенные снизу от нее:

=ВПР(A2;$D$2:$E$84;2;ЛОЖЬ)

Еще одна выгода, которую можно извлечь при использовании функции =ВПР(), заключается в том, что она возвращает значение #Н/Д, если соответствие найдено не было. Это удобно для выявления ошибок в написании названий регионов (в данном примере). То, что последним аргументом передается значение ЛОЖЬ, отражает тот факт, что требуется обеспечить точное соответствие названий.

#ИмпортДанных Открыть/Комментировать

2020-12-30 10:01:00 ​Методы предварительной подготовки данных. Классификация значений

Часто случается так, что в вашем распоряжении имеются значения, которые каким-либо образом должны группироваться. Например, если собираются данные о возрасте людей, может возникнуть необходимость в классификации их по возрастным группам, например 17 и моложе, 18-24,25-34 и т.д.

Проще всего это реализуется с помощью таблиц просмотра. В примере показано, что значения возраста людей хранятся в столбце А, а их классификация - в столбце В. Для заполнения значениями столбца В используется таблица просмотра, хранящаяся в диапазоне D2:E9. В ячейку В2 была занесена приведенная ниже формула, которая затем была скопирована в расположенные под ней ячейки.

=ВПР(A2;$D$2:$E$9;2)

#ИмпортДанных Открыть/Комментировать