Excel Выбрать из Списка Ближайшее Число • Поиск ячеек

Как в Экселе сделать выпадающий список, Excel 2007, 2010, 2013 и 2016

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

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

В этом материале речь пойдет именно о заполнении таблицы. Здесь вы сможете узнать, как в Эксель сделать выпадающий список, который позволит значительно быстрее заполнять таблицу данными. Инструкция будет актуальна для Excel 2007, 2010, 2013 и 2016.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».
как в excel найти слово

Связь таблиц в Excel. ИНДЕКС, СМЕЩ и ПОИСКПОЗ.

Первый способ: Применение расширенного автофильтра На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

Функция ИНДЕКС в Excel и ее особенности.

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

Функция ИНДЕКС. Получение данных из таблиц.

Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.

Получение данных из таблиц

Стоит поменять в ней значение, и результат функции СУММ поменяется.

Получение данных из таблиц

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

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

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

Получение данных из таблиц

В этом примере одна и та же формула позволили получить данные по сервисному обслуживанию за первый период, то есть за январь, по очереди по трем областям. Однако чаще все-таки используется вариант функции ИНДЕКС, который был рассмотрен в начале.

. Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.

  1. Ссылка, от которой отсчитывается перемещение.
  2. На сколько строк надо переместиться.
  3. Сколько столбцов надо отсчитать для перемещения.
  4. Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
  5. Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.

Функция ИНДЕКС. Получение данных из таблиц.

В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.

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

Получение данных из таблиц

И еще. Как и в случае с ИНДЕКС, внутри другой формулы результат работы СМЕЩ может использоваться как часть адреса.

Получение данных из таблиц

Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ.

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

Функция ИНДЕКС. Получение данных из таблиц.

Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:

Получение данных из таблиц

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

Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.

Аналогично комбинированию ИНДЕКС и ПОИСКПОЗ, мы можем использовать для извлечения нужного значения из таблицы комбинирование СМЕЩ и ПОИСКПОЗ. В полученной составной функции с помощью ПОИСКПОЗ находим перемещение по строкам и столбцам. Расписывать все это по отдельности второй раз уже станем, а сразу покажем формулу.

Функция ИНДЕКС. Получение данных из таблиц.

В следующем примере с помощью сцепления формул СМЕЩ и ПОИСКПОЗ рассчитывается общая стоимость заказа по каждой позиции. При этом цена автоматически изменяется в зависимости от заказанного количества в соответствии с условиями, указанными в заголовке.

Получение данных из таблиц

Давайте теперь подведем итог. Мы научились для связывания таблиц и получения данных из одной из них для вставки в другую использовать функции ИНДЕКС, СМЕЩ, ПОИСКПОЗ, а также различные их комбинации. Полученные формулы позволяют с успехом заменить функцию ВПР, при этом работая более быстро и гибко.

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Итак, сначала создаем список данных, которые должны находится в выпадающем списке, и размещаем его в любом удобном месте документа Эксель. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Если вы хотите оставить возможность вводить в ячейку данные не содержащиеся в выпадающем списке, то вам необходимо выделить ячейку с впадающим списком и нажать на кнопку «Проверка данных». Дальше в открывшемся окне необходимо перейти на вкладку «Сообщение об ошибке» и снять отметку напротив функции «Выводить сообщение об ошибке».

Как в Экселе сделать выпадающий список, Excel 2007, 2010, 2013 и 2016 | Интернет и компьютер

  • Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
  • Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
  • Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: