Как проверить программу в excel

Инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ) не был бы столь популярным, если бы его функциональность ограничивалась бы только собственно проверкой. Ниже кратко перечислены основные приемы работы с этим инструментом.

A. Проверка введенных значений

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

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

• Целое число . В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
• Действительное . В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
• Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата — в статье Как Excel хранит дату и время
• Время . Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 12:00:00 до 23:59:59. Вместо утомительного ввода значения 12:00:00 можно использовать его числовой эквивалент 0,5. Возможность ввода чисел вместо времени следует из того, что любой дате в EXCEL сопоставлено положительное целое число, а следовательно времени (т.к. это часть суток), соответствует дробная часть числа (например, 0,5 – это полдень). Числовым эквивалентом для 23:59:59 будет 0,99999.

Лайфхак как определить количество рабочих дней в excel

• Длина текста . В ячейку разрешен ввод только определенного количества символов. При этом ограничении можно вводить и числа и даты, главное, чтобы количество введенных символов не противоречило ограничению по длине текста. Например, при ограничении количества символов менее 5, нельзя ввести дату позднее 13/10/2173, т.к. ей соответствует число 99999, а 14/10/2173 — это уже 100000, т.е. 6 символов. Интересно, что при ограничении, например, менее 5 символов, вы не сможете ввести в ячейку формулу =КОРЕНЬ(2) , т.к. результат =1,4142135623731 (в зависимости от заданной в EXCEL точности), а вот =КОРЕНЬ(4) – сможете, ведь результат =2, а это только 1 символ.
• Список . Наверное, самый интересный тип данных. В этом случае ввод значений в ячейку можно ограничить ранее определенным списком. Например, если в качестве источника указать через точку с запятой единицы измерения товара шт;кг;кв.м;куб.м , то ничего другого, кроме этих 4-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на Именованную формулу . Пример приведен в статье Выпадающий (раскрывающийся) список
• Другой . В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

При выбранном типе Другой, в поле Формула нужно ввести формулу для расчета логического значения. Если оно принимает значение ИСТИНА, то такое значение разрешено вводить в ячейку, если ЛОЖЬ, то ваше значение ввести не получится. В качестве аргумента формулы нужно использовать ссылку на саму ячейку, к которой применяется Проверка данных или ссылку на зависящую от нее ячейку. Например,

Поиск ошибок в Excel. Подсчет и работа с ошибками

Введите формулу

Пояснение

Ячейка B2 содержала только текст

В Типе данных нет возможности выбрать тип Текст , поэтому приходится этого добиваться косвенно. Вы можете Проверку данных применить прямо к ячейке B2

Допустить ввод значения в ячейку B1 только в случае, если после ввода значение в ячейке D1 будет больше 100, в D2 меньше, чем 400

Проверку данных применяем к ячейке B1 . При этом в ячейке D1 введена формула =B1*2 , а в D2 – формула =B1*3 . Хотя эта формула эквивалентна ограничению Действительное с диапазоном от 50 до 133,33, но при более сложных связях ячеек, этот прием может быть полезен

Значение в ячейке, содержащей возраст работника ( С1 ), всегда должно быть больше числа полных лет работы ( D1 ) плюс 18 (минимальный возраст приема на работу)

При заполнении таблицы данными о возрасте и стаже работы можно поставить эту проверку для обеих ячеек ( C1 и D1 ). Для этого нужно выделить сразу 2 ячейки, вызвать Проверку данных и немного модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)

Все данные в диапазоне ячеек A1:A20 содержали уникальные значения

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

Значение в ячейке, содержащей имя кода продукта ( B5 ), всегда начиналось со стандартного префикса «ID-» и имело длину не менее 10 знаков.

Проверку данных вводим для ячейки B5

При выделении нескольких ячеек, там где нужно, не забывайте указывать абсолютную ссылку на ячейки (например, $A$1:$A$20 ).

При использовании инструмента Проверка данных , предполагается, что в ячейку будут вводиться константы ( 123, товар1, 01.05.2010 и пр.), хотя никто не запрещает вводить и формулы. В этом случае проверяться все равно будет результат вычисления формулы. Вообще вводить формулы в ячейки с проверкой данных не советую – легко запутаться. В этом случае советую использовать Условное форматирование .

В. Отображение комментария, если ячейка является текущей.

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

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

С. Вывод подробного сообщения об ошибке.

После ввода ошибочного значения Проверка данных может отобразить подробное сообщение о том, что было сделано не так. Это некий аналог Msgbox() из VBA .

D. Создание связанных диапазонов (списков)

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

Е. Использование в правилах ссылок на другие листы

В EXCEL 2007 в Проверке данных , как и в Условном форматировании нельзя впрямую указать ссылку на диапазоны другого листа, например, так =Лист2!$A$1 . Позволяют обойти это ограничение использование Имен .

Если в Проверке данных нужно сделать, например, ссылку на ячейку А1 другого листа, то нужно сначала определить Имя для этой ячейки, а затем сослаться на это имя в правиле Проверке данных .

В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.

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

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

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

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

Теперь выделим ячейку А2 и нажмем CTRL+D . Значение из А1 скопируется в А2 вместе с условием Проверки данных . Несмотря на то, что теперь условие Проверки данных будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных , нужно вызвать команду меню Обвести неверные данные ( Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные ). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2 и нажмем клавишу F2 (войдем в режим Правки), затем нажмем ENTER — появится окно с сообщением, что введенное значение неверно.

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

G. Поиск ячеек с Проверкой данных

Если на листе много ячеек с Проверкой данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ).

Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Источник: excel2.ru

Проверка данных в Excel: методы и особенности

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

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

Проверка на введенные значения

Программа Microsoft Office Excel позволяет гибко настроить введенные значения. При этом правильность будет проверяться по одному либо нескольким параметрам. Благодаря этому можно настроить поиск и ячейку.

Числовые проверки

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

• Целочисленные значения – в ячейку можно будет ввести только числа без дробной части. При этом можно также ограничить их значение определенным диапазоном либо запретить ввод отрицательных. Это хорошо подходит для номеров столбцев и строк.
• Действительные числа. В ячейку можно вводить только числовые значения, которые могут включать дробную часть (до десятых). Однако при этом невозможно ввести любой текст. Можно дополнительно вводить дату.
• При выборе параметра «Дата» появится возможность вводить числовые данные только в определенном формате. Для этого осуществляются проверка и поиск по введенному числу, и при неправильном выборе формата появится ошибка. Диапазон значений: от первого января 1900 года до 31 декабря 9999.
• Если выбрать тип ограничений по времени – то, как и в случае с датой, можно будет ввести только временной промежуток в определенном формате. Кроме того, можно ограничить введенные значения, например, введя данные только после полудня. Также существует возможность вводить данные в ячейку программы Microsoft Office Excel при помощи числового эквивалента. Так, 12:00 соответствует число 0.5. Это обуславливается способами хранения данных в Microsoft Office Excel. В приложении за дату и время отвечает дробное число. На примере полудня: 12/24 = 0.5.

Текстовая проверка

Можно ограничить способ введения текста различными методами. Рассмотрим подробнее.

• Проверка данных в Excel по длине введенного текста. Подходит для фамилий и наименований компаний. При этом разрешено вводить в определенную ячейку только заранее заданный объем символов. Также можно вводить числа и даты. Однако здесь существуют свои особенности. Из-за хранения дат в памяти в виде десятичного числа не получится ввести дату позже 13/10/2173, но только если ограничить длительность вводимого значения 5 символами. То же самое относится и к формулам. Если результат формулы слишком длинный, запись в ячейку не будет сделана.
• Список ограничений. Проверка вводимых данных в Excel осуществляется при помощи заранее заданного списка ограничений. При этом можно заранее задать определенный список ограничений. Кроме того, можно задавать значения в списке при помощи ссылки на ячейку либо именованной формулы. Список можно заполнять различными способами.

При помощи формулы

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

Некоторые способы ввода.

• Для того чтобы ячейка содержала только текст без символов, можно определить для ввода исключительно текстовые значения. Так как в обычной проверке данных невозможно ограничить вводимые значения и в текстовое поле можно по ошибке ввести число.
• Ограничить введение при условии, что в какой-либо из ячеек значение выходит за пределы заранее заданного диапазона.
• Добавить проверку введенного значения с формулой «ЕСЛИ». В таком случае можно будет ввести только те значения, которые соответствуют истине в формуле. Таким образом, можно, например, не давать вводить ошибочный возраст или суммы денег.
• Кроме того, можно в диапазоне ячеек вводить только определенные значения, которые не будут пересекаться.

При этом существуют ограничения на ввод формул. Поэтому лучше воспользоваться условным форматированием.

Вывод комментария в случае, если ячейка выбрана

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

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

Вывод сообщения об ошибке

Как сделать проверку данных в Excel? Если пользователь вводит некорректное значение, то можно вывести сообщение об ошибке и предложить ввести значения заново. По факту приложение полностью соответствует функции MessageBox из встроенного языка программирования в Microsoft Excel Visual Basic Application.

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

Виды сообщений об ошибке:

1. «Остановить» – отменить ввод неправильной информации, вывести заданное сообщение.
2. «Предупреждение» – при выборе такого типа сообщения произойдет предупреждение о неправильности введенных данных, программа предложит исправить ошибку.
3. «Сообщение» – примерно то же, что и предупреждение, но выглядит менее устрашающе и не предлагает изменить содержимое ячейки.

Использование ссылок на другие листы

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

Для того чтобы корректно выполнить проверку данных, которые находятся на другом листе, нужно для начала определить имя листа, а также наименование ячейки, затем при помощи ссылочного типа сослаться на полное имя листа и ячейки и корректно ввести данные. Проблема была устранена с обновлением распространяемого пакета Microsoft Office до 2010 года. Однако в более ранних версиях невозможно отобразить на листе проверку данных, которая проводилась этим способом.

Принцип работы проверки данных

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

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

Как найти ячейку с проверкой данных

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

Источник: fb.ru

Сравнение книг с помощью средство диагностики электронных таблиц

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

Чтобы выполнить все эти и другие задачи, вы можете использовать команды на вкладке Inquire (Запрос). На вкладке Inquire (Запрос) ленты Excel имеются кнопки для описанных ниже команд.

Если вкладка Inquire (Запрос) не отображается на ленте Excel, см. раздел Включение надстройки Inquire (Запрос).

Сравнение двух книг

Команда Compare Files (Сравнить файлы) позволяет просмотреть различия между двумя книгами по ячейкам. Чтобы выполнить эту команду, нужно открыть две книги в Excel.

Результаты сравнения выделяются цветом по типу содержимого, например по введенным значениям, формулам, именованным диапазонам и форматам. Имеется даже окно, в котором построчно могут отображаться изменения кода VBA. Различия между ячейками отображаются в удобной для восприятия таблице, примерно так:

Для сравнения двух файлов команда Compare Files (Сравнить файлы) использует средство сравнения электронных таблиц (Майкрософт). В Windows 8 можно запустить это средство вне Excel, выбрав Spreadsheet Compare на экране Приложения. В Windows 7 нажмите кнопку Пуск и выберите Все программы > Microsoft Office 2013 > Средства Office 2013 > Spreadsheet Compare 2013.

Подробнее о средстве сравнения электронных таблиц и сравнении файлов можно узнать в статье Сравнение двух версий книги.

Анализ книги

Команда Workbook Analysis (Анализ книги) создает интерактивный отчет, отображающий подробные сведения о книге и ее структуре, формулах, ячейках, диапазонах и предупреждениях. На приведенном здесь рисунке показана очень простая книга, которая содержит две формулы и подключения данных к базе данных Access и текстовому файлу.

Подробнее об этом можно узнать в разделе Анализ книги.

Отображение связей книги

В книгах, связанных с другими книгами с помощью ссылок на другие ячейки, можно запутаться. Используйте схему связей книги для создания интерактивной графической карты зависимостей, образованных соединениями (ссылками) между файлами. Типы ссылок в схеме могут включать другие книги, базы данных Access, текстовые файлы, HTML-страницы, базы данных SQL Server и другие источники данных. В схеме связей вы можете выбирать элементы и находить о них дополнительные сведения, а также перетаскивать линии соединения для изменения формы схемы.

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

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

Отображение связей листа

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

На этой схеме показаны связи между книгами в четырех разных книгах, а также связи между ними в одной книге. При наметии указателя на узле схемы, например на «Запад» на схеме, появляется веха с информацией.

Подробнее об этом можно узнать в статье Просмотр связей между листами.

Отображение связей ячейки

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

Подробнее о просмотре связей ячейки можно узнать в статье Просмотр ссылок между ячейками.

Очистка лишнего форматирования ячеек

Вы когда-нибудь открывали книгу и медленно загружали ее или она стала огромной? Возможно, к строкам или столбцам применено форматирование, о которых вы не знаете. Чтобы удалить лишнее форматирование и значительно уменьшить размер файла, используйте команду Удалить лишнее форматирование ячеек. Это помогает избежать «раздува электронных таблиц», что Excel быстроту работы.

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

Управление паролями

Если вы используете функции надстройки Inquire (Запрос) для выполнения анализа и сравнения защищенных паролем книг, потребуется добавить пароль книги в список паролей, чтобы с помощью надстройки Inquire (Запрос) можно было открыть сохраненную копию книги. Используйте команду Workbook Passwords (Пароли книги) на вкладке Inquire (Запрос), чтобы добавить пароли, которые будут сохранены на компьютере. Эти пароли шифруются и доступны только вам.

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

Источник: support.microsoft.com