Как сделать выборку в excel из списка

Дополнительная информация

В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ).На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).|
Если вы не укажете условие аргумента TRUE(ИСТИНА) (value_if_true (значение_если_истина)) в функции, т.е. условие указано только для аргумента value_if_false (значение_если_ложь), то формула вернет значение “0”, если результат вычисления функции будет равен TRUE (ИСТИНА);На примере ниже формула равна =IF (A1>20;«Отказать») или =ЕСЛИ(A1>20;”Отказать”), где аргумент value_if_true (значение_если_истина) не указан, формула будет возвращать “0” всякий раз, когда условие соответствует TRUE (ИСТИНА).

или C чего начать

Что такое Быстрый поиск

Это наиболее простой способ поиска документов в системе. Он доступен со стартовой страницы, а также из любого другого места системы через панель инструментов или при выборе вкладки и сразу готов к работе.
У Быстрого поиска простой и удобный интерфейс, как в поисковых системах интернета: всего одна строка, в которую можно вводить любую известную информацию о документах, и они будут найдены.
Быстрый поиск позволяет найти:
• конкретный документ;
• конкретный фрагмент документа (главу, статью и т.п.);
• документы по вопросу (ситуации).

Как работает Быстрый поиск

Задайте в поисковой строке известные вам данные о документе, который вы хотите найти, или же слова (фразу), описывающие ситуацию, по которой вам нужна информация (рис. 2.1).

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

Рис 2.1 По подсказке можно сразу перейти в документ без построения списка

Результаты Быстрого поиска появляются поэтапно. По запросам с подсказкой первые несколько документов выдаются практически одновременно с нажатием на кнопку (рис. 2.2), а пока пользователь их просматривает, список документов достраивается полностью.

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

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

1закон об АО

2 Нажмем кнопку .

3 Искомый документ — второй в списке.

Рис 2.2 Быстрый поиск Федерального закона «Об акционерных обществах»

Часто требуется найти не просто документ, а какой-то его фрагмент, например, статью. Особенно это актуально для документов большого объема: кодексов, объемных законов.

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

1 Зададим в строке Быстрого поиска: статья 168 ТК РФ (рис. 2.3).

2 Нажмем кнопку . Трудовой кодекс РФ стоит первым в списке.

3 Под названием кодекса стоит ссылка на искомую статью (рис. 2.3). Щелкнем по ней. Документ сразу откроется на статье 168.

Рис 2.3 Быстрый поиск статьи 168 Трудового кодекса РФ

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

1как рассчитать больничный

2 Нажмем кнопку . Будет построен список наиболее соответствующих запросу документов.

3 Перейдем в типовую ситуацию «Как рассчитать и оплатить больничный». Мы получим полную информацию по вопросу (рис. 2.5):
• пошаговые инструкции;
• пример расчета пособия и заполнения больничного листа;
• перечень связанных вопросов;
• ссылку на Путеводители, чтобы глубже изучить вопрос.

Рис 2.5 Ответ на вопрос в типовой ситуации

Поиск информации адаптируется под выбранный профиль. Например, в профиле «Бухгалтерия и кадры» в начало списка попадают документы, адресованные бухгалтеру.

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

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

Как правильно обращаться с выбросами

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

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

Удалить выбросы

Самый простой способ удалить выбросы из набора данных — просто удалить их. Таким образом, это не исказит ваш анализ.

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

Нормализовать выбросы (отрегулировать значение)

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

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

Чтобы дать вам реальный пример, если вы анализируете маржу чистой прибыли компаний, где большинство компаний находится в пределах от -10% до 30%, а есть несколько значений, превышающих 100%, я просто изменит эти выбросы на 30% или 35%.

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

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

Надеюсь, вы нашли этот урок полезным.

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

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

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

Например, чтобы разрешить любое целое число, находящееся между 100 и 999, выберите критерии «минимум и «максимум» в вкладке Параметры» и введите в них адреса ячеек с этими числами:

Рис18

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

Например, чтобы запретить пользователям вводить даты после сегодняшней даты, введите формулу =СЕГОДНЯ() в какую-нибудь ячейку, скажем, B1, а затем настройте правило проверки даты на основе этой ячейки:

Или вы можете ввести формулу =СЕГОДНЯ() непосредственно в поле Конечная дата, что будет иметь тот же эффект.

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

Синтаксис:

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

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

Аргументы:

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

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

Видеоинструкция

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

Файлы Microsoft Excel довольно часто используются для хранения данных, с которыми работает несколько различных пользователей

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

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

Для включения проверки надо выделить защищаемые ячейки, затем перейти на вкладку «Данные» и выбрать пункт «Проверка данных».

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

В качестве примера укажем любое целое число не больше 100000.

На вкладке «Сообщение об ошибке» выбираем действие, которое должно произойти при неверном вводе. Выбрать можно один из трех вариантов:

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

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

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

В качестве дополнительной помощи на вкладке «Сообщение для ввода» есть возможность оставить подсказку.

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

И если уж так случилось, что пользователям все таки удалось ″накосячить″, есть возможность выделить неправильно введенные данные. Сделать это можно, выбрав в меню «Проверка данных» пункт «Обвести неверные данные».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сообщение для ввода данных

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

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

В появившемся окне перейдите на вкладку «Сообщение для ввода».
Укажите любой текст в указанных полях.
Для продолжения нажмите на «OK».

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

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

Приготовьте лист доходов и расходов как показано на рисунке:

Прибыль – это естественно доход минус расход. Допустим нам нужно проверить, в какие дни прибыль падала ниже 40$. Решение следующее:

Выделите данные в диапазоне D2:D6 и выберите инструмент: «Данные»-«Работа с данными»-«Проверка данных».
В появившемся окне: «Проверка вводимых значений» установите такие же настройки как показано на рисунке. И нажмите ОК.
Теперь выберите инструмент из выпадающего списка: «Данные»-«Проверка данных»-«Обвести неверные данные»

И обратите внимание на результат:
При необходимости можете удалить красные обводки, выбрав инструмент «Удалить обводку неверных данных».

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

Проверка времени на основе текущего времени

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

В поле Тип данных выберите Время .

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

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

Чтобы проверить дату и время на основе текущей даты и времени:

=ТДАТА()

Чтобы проверить время на основе текущего времени, используйте выражение:

Синтаксис функции ЕСЛИ

Вот как выглядит синтаксис этой функции и её аргументы:

Логическое выражение – (обязательное) условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);

Значение если «да» – (обязательное) действие, которое выполняется в случае положительного ответа;

Значение если «нет» – (обязательное) действие, которое выполняется в случае отрицательного ответа;

Давайте вместе подробнее рассмотрим эти аргументы.

Первый аргумент – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».

Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “<”, “>=”, “<=”, “<>”.

Контроль ввода данных в Excel 2010

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

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

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

Первым нашим шагом станет открытие вкладки «Данные» верхнего меню:

В этой вкладке мы направим своё внимание на раздел под названием «Работа с данными»:

Ну, а в самом разделе устремимся взором на функционал поименованный «Проверка данных»:

Давайте щелчком левой кнопки мыши по стрелочке справа от названия откроем этот функционал и посмотрим на содержащиеся в нём опции:

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

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

Например, мы собираемся вводить в ячейки показатели, содержащие не менее и не более 5-ти нулей. Конечно, если количество самих показателей не более пяти, то вероятность недобрать нули или перебрать, невелика. А если количество показателей значительно больше, то после 125-го введённого показателя, в наших глазах появится мелькание нулей, резь и слезливость. Нам это нужно? Нет!

Давайте на простом примере с этими нулями проверим, как контроллер работает.

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

Затем откроем окошко проверки данных и начнём работу с параметрами:

В окошке «Тип данных» установим параметр «Целое число»:

В поле «Значение» выберем из списка параметр «Между»:

После того как мы выбрали этот параметр в нижней части окошка появились две строки ввода поименованные «Минимум» и «Максимум»:

Поскольку в нашем примере показатели не должны содержать менее или более пяти нулей, то в строку минимума мы собственноручно впишем параметр равный 100 000, а в строку максимума 900 000:

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

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

В общем, с заполнением строк этой вкладки поступаем на своё усмотрение.

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

В этой вкладке мы также можем ввести заголовок и сам текст сообщения. На мой взгляд, заполнение одного лишь поля «Сообщение» вполне будет достаточно. При выборе одного из вариантов: Остановка, Предупреждение, Сообщение раздела «Вид» мы должны быть готовы к тому, что варианты Предупреждение и Сообщение выполнив каждый свою задачу (предупредив или же сообщив) всё же позволят осуществить ввод показателя не соответствующего условию:

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

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

Повторение результата последнего наблюдения

LOCF (Last observation carried forward) — повторение результата последнего наблюдения. Данный метод применяется, как правило, при заполнении пропусков во временных рядах, когда последующие значения априори сильно взаимосвязаны с предыдущими.

Рассмотрим 2 случая, когда применение LOCF обосновано.

Случай 1. Если мы измеряем температуру воздуха в некоторой географической точке на открытом пространстве, причем измерения проводятся каждую минуту, то при нормальных условиях — если исключить природные катаклизмы — измеряемая величина априори не может резко (на 10–20 °C) измениться за столь короткий интервал времени между последующими измерениями. Следовательно, заполнение пропусков предшествующим известным значением в такой ситуации обоснованно.

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

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

Хотя в описанных выше ситуациях метод логичен и обоснован, он тоже может привести к существенным искажениям статистических свойств даже в случае MCAR . Так, возможна ситуация, когда применение LOCF приведет к дублированию выброса (заполнению пропусков аномальным значением). Кроме того, если в данных много последовательно пропущенных значений, то гипотеза о небольших изменениях уже не выполняется и, как следствие, использование LOCF приводит к неправильным результатам.

С помощью формулы

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

Некие методы ввода.

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

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

Проверка ввода с учетом регистра

Если регистр символов имеет значение, используйте СОВПАД (EXACT) в сочетании с функцией ЛЕВСИМВ, чтобы создать формулу проверки с учетом регистра для записей, начинающихся с определенного текста:

Например, чтобы разрешить только те коды заказов, которые начинаются с «AРТ-» (ни «арт-», ни «Арт-» не допускаются), используйте эту формулу:

В приведенной выше формуле функция ЛЕВСИМВ извлекает первые 4 символа из ячейки A2, а СОВПАД выполняет сравнение с учетом регистра с жестко заданной подстрокой (в данном примере «AРТ-«). Если две подстроки точно совпадают, формула возвращает ИСТИНА и проверка проходит успешно; в противном случае возвращается ЛОЖЬ и проверка завершается неудачно.

Как настроить ограничения проверки данных Excel

При добавлении правила проверки данных в Excel вы можете выбрать один из предопределенных параметров или указать новые критерии на основе собственной формулы. Ниже мы обсудим каждую из встроенных опций.

Как вы уже знаете, критерии проверки определяются на вкладке «Параметры» диалогового окна «Проверка данных» (вкладка «Данные» > «Проверка данных»).

В первую очередь нужно настроить проверку типа записываемых данных.

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

Равно или не равно указанному числу
Больше или меньше указанного числа
Между двумя числами или вне, чтобы исключить этот диапазон чисел

Например, вот как выглядят ограничения по проверке данных Excel, которые допускают любое целое число больше 100:

Функции проверки свойств и значений

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

Синтаксис:

ЕПУСТО(значение) ЕОШ(значение) ЕОШИБКА(значение) ЕЛОГИЧ(значение) ЕНД(значение) ЕНЕТЕКСТ(значение) ЕЧИСЛО(значение) ЕССЫЛКА(значение) ЕТЕКСТ(значение)

Результат: Приведенные выше функции возвращают значение ИСТИНА в следующих случаях:

Функция	Возвращает значение ИСТИНА, если
ЕПУСТО(значение)	значение ссылается на пустую ячейку
ЕОШ(значение)	значение ссылается на любое значение ошибки, кроме #Н/Д
ЕОШИБКА(значение)	значение ссылается на любое значение ошибки (#Н/, #ЗНАЧ!, #ССЫЛ!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО!)
ЕЛОГИЧ(значение)	значение ссылается на логическое значение
ЕНД(значение)	значение ссылается на значение ошибки #Н/Д (значение недоступно)
ЕНЕТЕКСТ(значение)	значение ссылается на любой элемент, который не является текстом
ЕЧИСЛО(значение)	значение ссылается на число
ЕССЫЛКА(значение)	значение ссылается на ссылку
ЕТЕКСТ(значсние)	значение ссылается на текст

Аргументы:

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

Внедрение ссылок на остальные листы

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

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

ЧаВо

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

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

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

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

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

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

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

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

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

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

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

Список

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

Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:

Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
Создать именованный массив, в поле «Источник» указать его имя
Перечислить пункты прямо в поле «Источник» через точку с запятой

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