Как создать тест в excel: 3 проверенных способа

Функция ИНФОРМ

Синтаксис:

ИНФОРМ(тип_инфо)

Результат: Информация о текущей операционной среде.

Аргументы:

тип_инфо — текст, задающий тип возвращаемой информации.

Значение аргумента	Возвращаемое значение
«directory»	Путь текущего каталога
«memavail»	Количество свободной памяти в байтах
«memused»	Количество памяти, используемой для данных
«numfile»	Количество активных рабочих листов
«origin»	Абсолютная ссылка в стиле А1 в виде текста
«osversion»	Текущая версия операционной системы в виде текста
«recalc»	Текущий режим перевычисления; возвращается «Автоматически» или «Вручную»
«release»	Номер версии Microsoft Excel в текстовом виде
«system»	Название операционной среды: Macintosh — «mac»; Windows — «pcdos»
«totmem»	Общее количество доступной памяти в байтах, включая уже используемую

Отчёт, отчёт и еще раз …?

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

Правильно – бездарно собранной информацией!!!

Итак, смотрим, что мы получили в ответ на наш запрос:

Раз

И два

С одной стороны вроде и информация собрана, а с другой…

Бухгалтррия Плюс – это почти Бухгалтерия Плюс, но не совсем.Антиврус (ПО) – это почти Антивирус (ПО), но не совсем.Отдел сопровждения ПО – это почти Отдел сопровождения ПО, но не совсем.

И уж совсем ни одно и тоже Windows XP, Win XP, XP, WinXP.Как впрочем и 1C Бухгалтерия, 1C, 1C Бухгалтерия 7.0.

Поэтому, засучив рукава, идем за большим напильником и начинаем …

Стоп, а если отложить напильник в сторону (на время) и попытаться проанализировать, что же сделано не так и как избежать повторения этой ситуации.

Пример проверки данных: создание раскрывающегося списка в Excel

Теперь, чтобы собрать воедино все, что вы узнали, давайте воспользуемся проверкой данных для создания раскрывающегося списка.

Раскрывающиеся списки – отличный способ заставить пользователей выбрать элемент из списка, который вы им предоставляете, вместо того, чтобы вводить свои собственные значения.

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

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

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

Выберите ячейку A1 и введите «ИМЯ».
В ячейках ниже добавьте кучу имен.
Наведите курсор на заголовок столбца (например, A ) и щелкните по нему, когда курсор станет стрелкой. Это выберет весь столбец.
Перейдите на вкладку » Вставка» и нажмите » Таблица» .
Установите флажок » Моя таблица имеет заголовки» и нажмите » ОК» . Эта опция перечислит верхнюю ячейку (то есть ИМЯ) в качестве заголовка таблицы.

Далее, давайте создадим таблицу для курсов, где пользователь сможет выбрать курс для каждого студента.

Выберите ячейку B1 и введите «КУРС».
Выберите весь столбец , щелкнув правой кнопкой мыши под именем столбца (как в предыдущей таблице).
Перейдите на вкладку «Вставка» и выберите « Таблица», чтобы создать таблицу.
Установите флажок » Моя таблица имеет заголовки» и нажмите » ОК» .

Теперь, когда у вас есть таблица курсов, пора составить список курсов.

Щелкните значок плюса рядом с Sheet1, чтобы создать новый лист.
На новом листе введите название курсов в столбец.

Наконец, давайте воспользуемся проверкой данных, чтобы создать раскрывающийся список курсов.

Выделите ячейки в столбце КУРС.
Перейдите на вкладку « Данные » и нажмите « Проверка данных» .
На вкладке « Настройки » в разделе « Разрешить» выберите « Список» .
Щелкните поле Источник .
Перейдите на Sheet2 и выберите ячейки, содержащие названия курсов (A1: A5 в этом примере).

Итак, вы создали правило проверки данных. Если вы хотите сделать это тщательно, вам также следует добавить входное сообщение.

В окне «Проверка данных» перейдите на вкладку « Входное сообщение ».
Введите заголовок, а затем собственное сообщение.

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

В окне «Проверка данных» перейдите на вкладку « Предупреждение об ошибке ».
Установите стиль на Stop . Сделав это, пользователь сможет вводить только то, что находится в списке.
Введите заголовок, а затем введите сообщение об ошибке. Вы также можете оставить это поле пустым.
Когда все будет готово, нажмите « ОК» в окне «Проверка данных».

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

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

F. Как срабатывает Проверка данных

Проверка данных
явно срабатывает при вводе в ячейку значений с клавиатуры с последующим нажатием клавиши ENTER
. В этом случае появляется окно с описанием ошибки.

Если значения вставляются через Буфер обмена
(Вставить значения
) или с использованием сочетания клавиш CTRL
+
D
(копирование значения из ячейки сверху) или копируются сверху вниз, то проверка в явном виде не осуществляется. Кроме того, при копировании значений можно вообще случайно удалить правила Проверки данных
, например если в ячейке источнике не определена Проверка данных
, а данные из нее вставляются через Буфер обмен
а с использованием комбинации клавиш CTRL+V
.

Поясним на примере. Предположим, к ячейке А1

применена Проверка данных
с условием проверки Другой
, где в поле формула введено =СТРОКА(A1)=1
, т.е. для всех ячеек из первой строки условие Проверки данных
будет принимать значение ИСТИНА, для других строк — ЛОЖЬ вне зависимости от содержания ячейки.

Теперь выделим ячейку А2

и нажмем CTRL+D
. Значение из А1

скопируется в А2

вместе с условием Проверки данных
. Несмотря на то, что теперь условие Проверки данных
будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных
, нужно вызвать команду меню Обвести неверные данные
(). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2

и нажмем клавишу F2
(войдем в режим Правки), затем нажмем ENTER
— появится окно с сообщением, что введенное значение неверно.

Есть еще один способ обхода проверки данных. Предположим, ввод в ячейку ограничен значениями от 1 до 3. Теперь в любую другую ячейку без Проверки данных
введем значение 4. Выделим эту ячейку, в Строке формул
выделим значение 4 и скопируем его в Буфер обмена
. Теперь выделим ячейку с Проверкой данных
и нажмем CTRL+V
. Значение вставилось в ячейку! Кроме того, Проверка данных
осталась нетронутой в отличие от случая, когда через Буфер обмена
, например, вставляется значение из WORD. Чтобы убедиться, что данные в ячейке не соответствуют условиям определенным в Проверке данных
, нужно вызвать команду меню Обвести неверные данные
(Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные
).

Как защитить от редактирования ячейки в Excel — запрет ввода ошибочных данных

Доброго времени!

В офисной работе часто возникает необходимость создать какой-нибудь общий документ Excel, в который бы вносили изменения сразу несколько человек (расшарив его в сети).

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

Хорошим решением подобной загвоздки может стать защита определенных ячеек от редактирования — т.е. позволить пользователям изменять документ только в разрешенном месте!

Собственно, об этом и будет сегодняшняя заметка.

Защищаем ячейки в Excel

Для того, чтобы в Excel защитить ячейки от редактирования и изменения — необходимо прибегнуть к инструменту защиты всего листа . Однако, по умолчанию, в этом случае Excel блокирует все ячейки, что нас не очень устраивает.

Поэтому, сначала необходимо выделить те ячейки (строки/столбцы), которые мы хотим разрешить редактировать, после нажать по ним правой кнопкой мышки и кликнуть по меню «Формат ячеек» . См. пример ниже.

Далее в меню «Защита» нужно снять галочку рядом с пунктом «Защищаемая ячейка». Нажать OK.

После перейти в меню «Рецензирование» и активировать инструмент «Защитить лист» . Меню Excel приведено на скрине ниже.

Далее укажите пароль и отметьте галочками, что пользователи могут делать с листом (можно оставить все по умолчанию).

Собственно, на этом всё!

В заранее отмеченных нами ячейках (в шаге 1), в которых мы сняли защиту — можно вводить любые числа и значения, в других же — Excel блокирует ввод и просит снять защиту с листа. Удобно?!

Дополнительно Excel позволят сделать так, что ввести в ячейку можно будет только определенное значение. Например, число от 1 до 100! (дополнительная защита от «дурака»).

Покажу на простом примере как это делается.

Сначала нужно выделить строку/столбец (или просто ячейки), перейти в меню «Данные» и кликнуть по инструменту «Проверка данных» (как он выглядит — показано на скриншоте ниже).

Проверка данных на этапе ввода

Далее в параметрах задаете тип значения, минимум и максимум, и вписываете подсказку. Пример ниже.

Указание диапазона верных значений

После этого, когда поставите курсор в «нашу ячейку» Excel сразу же подскажет, что ввести можно от 1 до 100 (а если кто-то введет что-то отличное — выскочит ошибка ).

Как работает проверка

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

Как убрать проверку данных в Excel

В целом, есть два способа удалить проверку в Excel: стандартный подход, разработанный Microsoft, и метод без мыши, разработанный фанатами Excel, которые никогда не отрывают руки от клавиатуры без крайней необходимости (например, чтобы выпить чашку кофе:)

Способ 1: Обычный способ отключить проверку данных

Обычно, чтобы удалить проверку данных на листах Excel, выполните следующие действия:

Выберите ячейку (ячейки) с проверкой данных.
На вкладке «Данные» нажмите кнопку «Проверка данных».
На вкладке «Настройки» нажмите кнопку «Очистить все», а затем нажмите «ОК».

Советы:

Чтобы удалить проверку данных из всех ячеек на текущем листе, используйте инструмент «Найти и выделить», чтобы выбрать все ячейки с проверкой.
Чтобы удалить определенное правило проверки данных, выберите любую ячейку с этим правилом, откройте диалоговое окно «Проверка данных», установите флажок «Применить эти изменения ко всем другим ячейкам с такими же настройками» и нажмите кнопку «Очистить все».

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

Способ 2: Удалить правила проверки данных при помощи Специальной вставки

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

Выберите пустую ячейку без проверки данных и нажмите , чтобы скопировать ее в буфер обмена.
Выберите ячейки, в которых вы хотите отключить проверку данных.
Нажмите , и затем . Эта комбинация клавиш вызовет «Специальная вставка» > «Проверка данных».
Нажимаем ОК.

Как отключить эту ошибку

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

Выберите ячейку, в которой вы не можете указать нужное вам значение.
Перейдите на панели инструментов на вкладку «Данные».
Нажмите на инструмент «Работа с данными».
Кликните на иконку «Проверка данных».

Для того чтобы убрать все настройки, достаточно нажать на кнопку «Очистить всё».
Сохраняем изменения кликом на «OK».

Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.

Как сделать всплывающую подсказку?

Чтобы сделать всплывающую подсказку для ячейки выберем на панели вкладок Данные -> Работа с данными -> Проверка данных и в открывшемся окне перейдем в центральную вкладку:

Далее вводим информацию в поля Заголовок и Сообщение, при этом текст введенный в заголовок при отображении подсказки будет показываться жирным шрифтом, а также устанавливаем галочку напротив поля Отображать подсказку, если ячейка является текущей. Нажимаем OK и получаем следующий результат:

трюки • приёмы • решения

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

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

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

Автозавершение также изменяет регистр букв автоматически. Если начать ввод со слова сахар (со строчной буквой «с») во второй записи, Excel сделает букву «с» прописной в соответствии с предыдущей записью в столбце.

Если столбец содержит несколько записей, которые соответствуют первым нескольким символам, Excel не отобразит предложение по автоматическому завершению, пока новая запись не будет соответствовать одной из старых однозначным образом. Например, если столбец также содержит продукт под названием «сахар-рафинад», автозавершение не будет реализовано, пока вы не введете либо первую букву слова «песок», либо первую букву слова «рафинад».

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

Рис. 32.1. Выбор пункта контекстного меню Выбрать из раскрывающегося списка позволяет отображать список записей в столбце

Вы даже можете получить доступ к списку с клавиатуры: нажмите Shift+F10 для отображения контекстного меню, а затем нажмите клавишу К (горячую клавишу для команды Выбрать из раскрывающегося списка). Используйте кнопки со стрелками, чтобы сделать свой выбор, и нажмите Enter.

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

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

Особенно трудно добиться правильного оформления по определенной маске (шаблону). Например, дату в виде строки можно записать несколькими способами:

01.01.2001;
01/01/2001;
1 января 2001 года и т.д.

В статье описывается функционал, позволяющий упростить процесс сбора и проверки данных в Excel.

Список проверки данных Excel (раскрывающийся список)

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

Откройте диалоговое окно «Проверка данных» (вкладка «Данные» > «Проверка данных»).
На вкладке «Настройки» выберите «Список в поле «Тип данных».
В поле Источник введите элементы списка проверки Excel, разделенные точкой с запятой. Например, чтобы ограничить пользовательский ввод тремя вариантами, введите Да; Нет; Н/Д.
Убедитесь, что выбрана опция Список допустимых значений, чтобы стрелка раскрывающегося списка отображалась рядом с ячейкой.
Нажмите ОК.

Выпадающий список проверки данных Excel будет выглядеть примерно так:

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

Другие способы создания списка проверки данных в Excel

Предоставление списков, разделенных точкой с запятой, непосредственно в поле «Источник» — это самый быстрый способ, который хорошо работает для небольших раскрывающихся списков, которые вряд ли когда-либо изменятся. В других сценариях можно действовать одним из следующих способов:

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

Во всех этих случаях вы просто записываете соответствующую ссылку на диапазон либо элемент таблицы в поле Источник.

Функция IsMissing

Описание функции

Функция IsMissing возвращает значение типа Boolean, указывающее, был ли необязательный аргумент типа данных Variant передан процедуре:

True — если в процедуру не было передано значение для необязательного аргумента;
False — значение для необязательного аргумента было передано в процедуру.

Пример с IsMissing

1
2
3
4
5
6
7
8
9
10
11
12

FunctionScepka(x,Optionaly)

IfNotIsMissing(y)Then

Scepka=x&y

Else

Scepka=x&» (а необязательный аргумент не подставлен)»

EndIf

EndFunction

SubPrimer5()

Debug.Print Scepka(«Тропинка»,» в лесу»)’Результат: Тропинка в лесу

Debug.Print Scepka(«Тропинка»)’Результат: Тропинка (а необязательный аргумент не подставлен)

EndSub

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

Целое число;
Действительное число;
Список;
Дата;
Время;
Длина текста;
Другой.

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

Самым необычным видом является выпадающий список.

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

Как сделать проверку данных в Excel

Чтобы добавить проверку данных в Excel, выполните следующие действия.

1. Откройте диалоговое окно «Проверка данных».

Напомним, где находится кнопка проверки данных в Excel. Выбрав одну или несколько ячеек для проверки, перейдите на вкладку «Данные» > группа «Работа с данными» и нажмите кнопку «Проверка данных».

2. Создайте правило проверки Excel.

На вкладке «Параметры» определите критерии проверки в соответствии с вашими потребностями. В критериях вы можете указать любое из следующего:

Значения — введите числа в поля критериев, как показано на снимке экрана ниже.
Ссылки на ячейки — создание правила на основе значения или формулы в другой ячейке.
Формулы — позволяют выразить более сложные условия.

В качестве примера создадим правило, разрешающее пользователям вводить только целое число от 100 до 999:

Настроив правило проверки, нажмите кнопку «ОК», чтобы закрыть окно «Проверка вводимых значений», или переключитесь на другую вкладку, чтобы добавить подсказку по вводу и/или сообщение об ошибке.

3. Подсказка по вводу (необязательно).

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

Убедитесь, что установлен флажок Отображать подсказку при выборе ячейки.
Введите заголовок и текст сообщения в соответствующие поля.
Нажмите OK, чтобы закрыть диалоговое окно.

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

4. Отображение предупреждения об ошибке (необязательно)

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

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

Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку «Сообщение об ошибке» и задайте следующие параметры:

Установите флажок Выводить сообщение об ошибке (обычно установлен по умолчанию).
В поле Вид выберите нужный тип оповещения.
Введите заголовок и текст сообщения об ошибке в соответствующие поля.
Нажмите ОК.

И теперь, если пользователь введет недопустимые значения, Excel отобразит специальное предупреждение с объяснением ошибки (как показано в начале этого руководства).

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

ЧаВо

— Как удалить данные из ячейки, если в ней выпадающий список, а нужно сделать пустую ячейку.

— Нужно щелкнуть по ячейке и нажать клавишу DEL.

— Как добавить в список новые значения для выбора или удалить ненужные?

— Для этого нужно отредактировать список значений списка (в нашем случае в столбце Н) и заново выбрать диапазон значений в окне Настройка вводимых значений.

— Как вообще удалить выпадающий список, а не только одно значение?

— Зайдите Данные — Проверка данных — Тип данных: Любое значение.

— Как сделать выпадающий список с данными, расположенными на другой странице?

— Ранее мы говорили, что удобно использовать несколько листов Excel для разных данных, например, для основной таблицы использовать Лист1, а для всех данных, из которых формируются выпадающие списки, можно использовать Лист 2.

Как сделать выпадающий список из данных, расположенных на другом листе? Нужно диапазону с вариантами выпадающего списка присвоить имя (Выделить диапазон — Формулы — Присвоить имя).

А затем на Шаге 3 в качестве источника указать это имя диапазона.

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

Проверка данных Excel

01.01.2001;
01/01/2001;
1 января 2001 года и т.д.