gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 95 гостей онлайн
Возможности Excel. Найти в Excel несколько данных сразу.

Найти в Excel несколько данных сразу.

      Функция "ВПР" в Excel поможет найти данные и перенести их в другую ячейку, таблицу. Например, заполнить бланк. Отбор данных в Excel этой функцией производится по первому столбцу таблицы.
      У нас есть таблица  с перечнем  товара, ценой,  наличием его на складе. В другой таблице указаны оптовые скидки. Нам нужно быстро найти всю информацию об этом товаре. У нас такая таблица - прайс.
В формуле, которую мы будем писать,  будем указывать диапазон этой таблицы (прайса).  Можно присвоить имя диапазону. Выделяем всю таблицу (кроме шапки) и присваиваем этому диапазону имя, например «Продукты». Как присвоить имя диапазону, смотрите в статье «Диапазон в Excel» тут.
      Внимание!
Чтобы формула правильно работала, в ячейках должен быть одинаковый формат ячеек.
Если в одних ячейках стоит  формат «Текстовый», а в других – «Числовой», то формула покажет ошибку.
Ещё - эта функция ищет только по первому столбцу таблицы Excel.
Всю найденную информацию можно разместить в таблице рядом с таблицей – прайсом. Например, так.  НАЙТИ СРАЗУ ВСЮ ИНФОРМАЦИЮ ПО ОБЪЕКТУ В ТАБЛИЦЕ EXCEL/Или на другой странице разместить таблицу с  найденной информацией.
       Принцип работы такой.
В ячейку F2 пишем наименование товара. Можно написать инвентарный номер, другие коды, т.е. то, что записано в первом левом столбце прайса. В остальных ячейках столбца F установлены формулы. Они находят в таблице и показывают нам эту информацию.
      Итак, делаем активной ячейку, в которую будем устанавливать формулу (в примере будем искать цену товара). У нас, в примере – это ячейка  F4.
На закладке «Главная» в разделе «Библиотека функций» выбираем «Ссылки и массивы» и в ней выбираем функцию «ВПР».
Нажимаем «ОК». В появившемся окне пишем:
      В строке «Искомое_значение» указываем адрес ячейки, в которой будем писать название товара, который ищем. В примере – это ячейка F2 .
      В строке «Таблица» пишем имя диапазона таблицы прайса. У нас, в примере, - «Продукты». Можно указать адрес диапазона.
      В строке «Номер_столбца» указываем НОМЕР ПО СЧЕТУ столбца (не буквы), где написаны цены товара.
В примере – второй столбец прайса.
      В строке «Интервальный _просмотр» пишем слово «Ложь». Это значит, что таблица будет искать точное значение наименования.  Например, у нас формула настроена искать «Творог». И будет искать только творог.
А если мы укажем не «ложь», а «истина», то таблица будет искать похожие слова. Может возникнуть путаница.Нажимаем «ОК». Получилось так.
В  ячейке F4 появилась такая надпись, #Н/Д (значит - нет данных), п.ч.  мы в ячейку F2 не написали название товара, информацию по которому ищем.
Теперь пишем в ячейку F2 «Творог». Получилось так.По такому же принципу устанавливаем формулу по строке «Наличие», только столбец указываем третий (в прайсе -наличие на складе стоит в третьем столбце).
       Как написать в Excel формулу по скидкам.
У нас есть такая таблица по оптовым скидкам.
       Внимание!
В таблице в столбце скидки должны стоять по возрастанию. Можно установить сортировку. Смотрите статью «Фильтр в Excel».
      Также устанавливаем функцию ВПР в ячейке F6. Но, в строке "Таблица" указываем диапазон таблицы со скидками, в строке «Интервальный_просмотр», пишем «Истина», потому  что могут взять не ровное количество товара.Нажимаем «ОК». Теперь, когда в ячейке F3 напишем количество покупаемого товара, в ячейке F6 появится цифра скидки в процентах.
В ячейке  F7 устанавливаем формулу итоговой суммы со скидкой. =F3*F4-((F3*F4)*F6/100) Получилось так.
Таблицу скидок можно сделать на другой странице.
Если товара нет в наличии, то получится так.Таким способом можно выбирать любую информацию из таблицы Excel.
      Как найти в таблице функцией ВПР по данным не одного, а двух столбцов, читайте в статье "Соединить функции "ВПР" и "СЦЕПИТЬ" в Excel" здесь.
         Из таблицы Excel можно выбрать данные сразу по двум параметрам на пересечении строки и столбца. Читайте в статье "Как найти в Excel ячейку на пересечении строки и столбца".