000818.ru

Финансовые и Бух. услуги
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Как извлечь данные из электронной таблицы, используя VLOOKUP, MATCH и INDEX

Когда вам нужно найти и извлечь столбец данных из одной таблицы и поместить его в другую таблицу, используйте функцию VLOOKUP. Эта функция работает во всех версиях Excel на Windows и Mac, а также в Google Sheets. Он позволяет найти данные в таблице с помощью идентификатора, который используется совместно с другой таблицей. Эти две таблицы могут находиться на разных листах или даже в разных книгах. Существует также функция HLOOKUP, которая делает то же самое, но с данными, расположенными горизонтально, строка за строкой.

Когда вам нужно найти конкретные данные, например, имя человека в строке или столбце, помогут функции MATCH и INDEX.

Создание выпадающих списков

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

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

Проверка по числу введенных символов

Подсказки – это хорошо, но что делать с самыми “прогрессивными” пользователями, которые подсказок не читают? Остается одно – бить по рукам… то есть использовать фильтрацию ввода, я хотел сказать.

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

Проверка по числу введенных символов в MS Excel

Проверка количества символов, введенных в MS Excel

Первое, что может оказаться полезным, это проверка по длине введенного текста. Выбираем пункт “Длина текста” и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле “Сообщение” был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде “Напоминание об оплате” (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Слишком длинный текст! Excel стоит на страже и не пропустит такой очевидной ошибки

Текст слишком длинный! Excel стоит на страже против такой очевидной ошибки

При использовании функции ПОИСКПОЗ будет выдана позиция текста в ячейке, а в противном случае — ошибка #ПОИСКПОЗ! Как видно из приведенного примера, ячейка B5 содержит число «4» в позиции 5. Мы могли бы использовать следующую формулу:

В результате она вернет число 5. Однако, поскольку мы даем функции НАЙТИ массив элементов для проверки, она вернет массив результатов, который выглядит следующим образом:
<#ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!,4, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!>
Другими словами, НАЙТИ проверяет содержимое B5 для каждого числа и возвращает результат каждой проверки как элемент массива.
После того, как НАЙТИ вернет массив, СЧЁТ считает элементы в массиве. СЧЁТ считает только числовые значения, поэтому любой элемент #ЗНАЧЕН! в массиве обрабатывается как ноль. Если в массиве есть числа СЧЁТ вернет число больше нуля, если нет то вернет ноль.
Последний шаг в формуле — сравнение результата функций НАЙТИ и СЧЁТ с нулем. Если были найдены какие-либо числа, формула вернет ИСТИНА. В противном случае формула вернет ЛОЖЬ.
Если вы хотите сделать что-то большее, чем просто проверить, содержит ли ячейка текст, вы можете заключить формулу в оператор ЕСЛИ следующим образом:

Читайте так же:
Как объединить три ячейки для создания даты в Excel?

Приведенная выше формула возвращает «Да», если B5 содержит какие-либо числа, и «Нет», если не содержит.

Содержит ли ячейка числовое значение?

Функция ЧИСЛО — самый удобный способ узнать, содержит ли ячейка численное значение:

3. Ввод и редактирование содержимого ячеек

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

Содержимое ячеек

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

Содержимое ячейки можно отредактировать двумя способами

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

Введение понятия «табличный режим» и «текстовый режим».

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

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

Содержимое ячеек

Здесь кроется подводный камень. Если вы вставите в ячейку текст из буфера обмена, ячейка потеряет режим «слово в слово». Можно, конечно, снова вызвать диалоговое окно «Формат ячеек» и назначить этот режим именно этой ячейке. Но лучше использовать возможности специальной вставки из буфера обмена:

Содержимое ячеек

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

Подготовьте структуру для рабочего листа

Чтобы подготовить структуру для рабочего листа —

  • Начните с чистого листа.
  • Поместите заголовок в строке 2.
  • Поместите заголовки столбцов в строке 3.
  • Для заголовков столбцов Вероятность, Воздействие и Риск —
    • Щелкните правой кнопкой мыши на ячейке.
    • В раскрывающемся списке выберите Формат ячеек.
    • В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
    • Введите 90 ниже ориентации.

    Ваш рабочий лист будет выглядеть следующим образом —

    Результат листа

    Установить допустимые значения для категории риска

    В ячейках M5 — M13 введите следующие значения (M5 — заголовок, а M6 — M13 — значения)

    • Нажмите на первую ячейку под столбцом Категория риска (H6).
    • Нажмите вкладку ДАННЫЕ на ленте.
    • Нажмите Проверка данных в группе Инструменты данных.
    • Выберите Проверка данных … из раскрывающегося списка.

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

    После открытия появляется диалоговое окно Проверка данных.

    • Перейдите на вкладку «Настройки».
    • В разделе «Критерии проверки» выберите «Список» в раскрывающемся списке «Разрешить:».

    Выберите список

    • Выберите диапазон M6: M13 в появившемся поле Источник:.
    • Выберите поле Игнорировать пробелы и выпадающий список внутри флажков ячеек.

    Флажки

    Установить входное сообщение для категории риска

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

    Показать входное сообщение

    Установить оповещение об ошибке для категории риска

    Чтобы установить предупреждение об ошибке —

    • Перейдите на вкладку Предупреждение об ошибке в диалоговом окне Проверка данных.
    • Установите флажок Показывать предупреждение об ошибке после ввода неправильных данных.
    • Выберите Stop из раскрывающегося списка Style:
    • В поле Title введите Invalid entry:
    • В поле Error message: введите Select the value from the drop-down list.
    • Нажмите OK.

    Установить оповещение об ошибке

    Проверка данных для категории риска

    Для первой выделенной ячейки в категории риска

    • Установлены критерии проверки данных
    • Набор входных сообщений
    • Набор предупреждений об ошибках

    Теперь вы можете проверить настройки.

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

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

    Вводное сообщение отображается правильно.

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

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

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

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

    Сообщение отображается правильно

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

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

    Выпадающий список

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

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

    Нажмите Enter после ввода в ячейку слова Люди. Для ячейки появится сообщение об ошибке.

    Отображается сообщение об ошибке

    • Проверьте сообщение об ошибке.
    • У вас есть возможность сделать еще одну попытку или отменить ее. Выберите оба варианта.

    Вы успешно настроили управление данными в ячейке.

    Внимание. Очень важно проверять орфографию и грамматику своих сообщений.

    Установите действительные критерии для столбца категории риска

    Теперь вы готовы применить критерии контроля данных ко всем ячейкам в столбце «Категория риска».

    На данный момент вам нужно запомнить две вещи —

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

    Не определяйте критерии для диапазона или столбца ячеек, которые вам не нужны. Это неоправданно увеличивает размер файла. Это называется избыточным форматированием. Чтобы удалить избыточное форматирование, которое может присутствовать в рабочем листе, полученном из внешнего источника, следуйте инструкциям в главе «Запрос» данного руководства.

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

    Критерии не следует устанавливать для ненужных диапазонов ячеек или для всего столбца. Это неоправданно увеличивает размер файла. Мы называем это чрезмерным форматированием. Как удалить форматирование при получении рабочего листа из внешнего источника, вы узнаете в главе «Запрос» данного руководства.

    Следуйте инструкциям, приведенным ниже

    • Определите критерии валидации для 10 ячеек категории риска.
    • Это можно легко сделать, нажав на правый нижний угол первой ячейки.
    • Удерживайте появившийся символ + и перетащите его вниз.

    Установить действительные критерии

    Проверка включена для всех выделенных ячеек.

    Щелкните по последнему выбранному столбцу и проверьте.

    Выбранный и проверенный столбец

    Проверка столбца «Категория риска» завершена.

    Установить значения проверки для источника риска

    В этом случае у нас есть только два значения — Внутреннее и Внешнее.

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector