Adelitusn.ru

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

Лабораторная работа 4 Excel

Лабораторная работа 4 Excel

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

Краткий комментарий

Функция ВПР – это функция работы со справочниками из категории «Ссылки и массивы». Она ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы. Синтаксис написания функции ВПР:

ВПР(искомое_значение;таблица;номер_столбца),

где — искомое_значение значение, которое должно быть найдено в первом столбце табличного массива;

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

номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 2, то возвращается значение из второго столбца таблицы; если номер_столбца = 3 — значение из третьего столбца таблицы и т.д.

Практическое задание

Загрузить MS Excel 2007.

На Листе 1 рабочей книги на основании данных, представленных на рис. 4.1, создать таблицу. Установить при этом достаточную ширину столбцов и высоту строк, в «шапке» таблицы выполнить центрирование заголовков. Оформить таблицу линиями.

Рис. 4.1 Учет надоя молока за три дня

На Листе 2 Рабочей книги создать таблицу по образцу (Рис. 4.2).

Рис. 4.2 Справочник доярок

Используя функцию ВПР (из категории «Ссылки и массивы»), каждому табельному номеру (Рис. 4.1) найти соответствующее ФИО из таблицы (Рис. 4.2) и поместить в соответствующую графу, а также каждому табельному номеру – соответствующий процент доплаты и поместить в графу «Доплата за мастерство, %» (Рис. 4.1).

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

Рис. 4.3 Мастер функций 1 шаг

А затем, перейдя на второй шаг Мастера функций ввести аргумент данной функции (Рис. 4.4)

Рис. 4.4 Мастер функций 2 шаг

Протащить формулу по столбцу.

Выполнить расчет: «Сумма оплаты» = «Количество» * «Расценку» (взять для расчета 110 руб.); «Сумма доплаты» = «Сумма оплаты» * «Процент доплаты за мастерство» / 100; «Сумма всего» = «Сумма оплаты» + «Сумма доплаты» (для отображения результата использовать числовой формат, ноль десятичных знаков и с разделителем разрядов).

Сохранить документ в фамильной папке под именем лр4зад1.

На Листе 3 рабочей книги на основании данных, представленных на рис. 4.5, создать таблицу по образцу:

Рис. 4.5 Расчет сравнительной эффективности суточных рационов по содержанию переваримого протеина

На листе 4 Рабочей книги создать таблицу по образцу рис. 4.6.

Рис. 4.6 Справочник кормов

Используя функцию ВПР (из категории «Ссылки и массивы»), каждому коду корма (Рис. 4.5) найти соответствующее наименование вида корма из таблицы (Рис. 4.6) и поместить в графу «Вид корма».

Аналогично заполнить столбец «Содержание в одном грамме корма переваримого протеина, г» по первому и второму вариантам рационов.

Посчитать столбец «Количество переваримого протеина» по формуле: «Количество переваримого протеина, г» = «Содержание в одном грамме корма переваримого протеина, г» * «Количество корма, кг» * 1000 по обоим вариантам рационов кормления.

Посчитать итоги в конце таблицы по столбцам.

Сохранить документ в фамильной папке под именем лр4зад2.

Вопросы для самоконтроля

Что позволяет выполнить функция ВПР?

Синтаксис написания функции ВПР?

Аргумент функции ВПР?

Как работает Мастер функций с функцией ВПР?

Тут вы можете оставить комментарий к выбранному абзацу или сообщить об ошибке.

Функция ВПР. Использование функции ВПР. Excel — ВПР

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

Читайте так же:
Способы удаленного управления Android-устройством: 5 бесплатных программ

Функция ВПР пример

Одной из широко известных формул Excel является вертикальный просмотр. Использование функции ВПР на первый взгляд кажется довольно сложным, но это только поначалу.

Как работает ВПР Excel

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

Функция ВПР производит поиск заданного критерия, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не находится, то выдается ошибка #Н/Д (в англоязычном варианте #N/А).

Необходимость использования

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

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

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

В результате работы функции ВПР (VLOOKUP) формируется новая таблица, в которой конкретному искомому менеджеру быстро сопоставляется его сумма продаж.

Алгоритм заполнения формулы

Функция ВПР как пользоваться

Расположена формула ВПР во вкладке «Мастер функций» и разделе «Ссылки и массивы». Диалоговое окно функции имеет следующий вид:

Аргументы в формулу вносятся в порядке очереди:

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

Пример использования функции

Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В — соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.

Наглядный пример организации таблицы

АВСД
продукт 190продукт 360
продукт 2120продукт 190
продукт 360продукт 4100
продукт 4100продукт 2120

Формула, записанная в Д, будет выглядеть так: =ВПР (С1- А1:В5- 2- 0), то есть =ВПР (искомое значение- диапазон данных таблицы- порядковый номер столбца- 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.

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

Читайте так же:
Скачать TimePC последнюю версию

Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1- $А$1:$В$5- 2- 0).

Ошибки при использовании

Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:

  1. Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
  2. В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню «Правка» — «Найти» вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
  3. Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной — текстовый, а у другой — числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
  4. В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
  5. Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.

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

Особенности использования в качестве интервального просмотра 1 или ИСТИНЫ

Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.

ВПР не работает

В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. Причем в качестве интервального просмотра использован критерий ИСТИНА (1), который сразу прерывает поиск при обнаружении значения большего, чем искомое, поэтому выдается ошибка.

При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.

Просто следует учитывать, что особенно важно сортировать интервальные таблицы. Иначе функция ВПР будет выводить в ячейки неправильные данные.

Другие нюансы при работе с функцией ВПР

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

Excel ВПР

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

Другой вариант — озаглавить — подразумевает выделение диапазона данных, потом переход в меню «Вставка»- «Имя»- «Присвоить».

Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР (А1- Лист2!$А$1:$В$5- 2- 0), где Лист2! — является ссылкой на требуемый лист книги, а $А$1:$В$5 — адрес диапазона поиска данных.

Пример организации учебного процесса с ВПР

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

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

Читайте так же:
Лучшие бесплатные программы для сжатия ПДФ

функция ВПР

Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком «Оценки» записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.

Использование функции ВПР

В результате выполнения функция ВПР выдаст оценки, полученные определенными студентами.

Пример организации поисковой системы с ВПР

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

впр функция excel

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

Как пользоваться функцией ВПР в Excel: понятная инструкция для чайников, видео, сравнение двух таблиц, выпадающий список, примеры. Формула функции ВПР в Excel: примеры, несколько условий, расшифровка

Статья-инструкция: «Функция ВПР в Excel для чайников».

Функция ВПР в Excel дает возможность переставлять данные одной таблицы в другую по ячейкам. Это очень популярный и удобный способ, и о нем мы поговорим в сегодняшнем обзоре.

Функция ВПР в Excel: инструкция для чайников

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

funkcii-excel9-1

В другой таблице мы имеем тот же самый список с указанием стоимости этих материалов:

funkcii-excel9-2

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

Приступим к делу:

  • В первой таблице нам не хватает двух столбцов – «Цена» (то есть стоимость за 1 кв.м) и «Стоимость» (то есть общая стоимость завезенного материала). Добавим эти столбцы. Теперь выделите в столбце «Цена» верхнюю первую ячейку, запустите «Мастер функций» (нажать одновременно «F3» и «Shift»), во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР».

funkcii-excel9-3

Запустите «Мастер функций», во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР»

  • Далее в новом открывшемся окне напротив пункта «Искомое значение» наблюдаем следующие показатели: A1:А15. То есть программа фиксирует диапазон наименований материалов в соответствующем столбе «Материалы». То же самое программа должна показать и во второй таблице.

funkcii-excel9-4

Наблюдаем следующие показатели: A1:А15

  • Теперь разберемся со вторым пунктом – «Таблица» (вторая таблица со стоимостью материалов). Нажмите на этот пункт, затем выделите во второй таблице диапазон наименований материалов вместе с ценами. В итоге, результат должен быть следующим.

Нажмите на пункт «Таблица»

  • Далее выделите результаты в пункте «Таблица» и нажмите на «F4», после чего в них появится новый символ «$» — таким образом программа будет ссылаться на показатели в «Таблица».

Выделите результаты в пункте «Таблица» и нажмите на «F4»

  • Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».
Читайте так же:
Убрать голос из песни онлайн

В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ»

  • Последний штрих. Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз то тех пор, пока не увидите полный список наименований материалов вместе с ценами.

funkcii-excel9-8

Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз

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

  • Выделите все показатели в столбце «Цена», нажмите на нем правой кнопкой мышки и далее – на «Копировать».
  • Снова нажмите правой кнопкой мышки по уже выделенным ценам и затем – на «Специальная вставка».
  • В открывшемся окошке поставьте галку, как показано на скриншоте, и нажмите на «ОК»

funkcii-excel9-9

Поставьте галку на «Значения»

Сравниваем две таблицы при помощи функции ВПР в Excel

Предположим, что прайс-лист изменился, и нам необходимо сравнить две таблицы – новые и старые цены:

funkcii-excel9-10

Для этого воспользуемся соответствующей функцией в «Excel»:

  • В старой таблице добавьте столбец «Новая цена»

funkcii-excel9-11

Добавляем столбец «Новая цена»

  • Далее повторяем действия, которые мы совершали выше – выделяем первую верхнюю ячейку, выбираем «ВПР», в пункте «Таблица» получаем результаты и нажимаем на «F4».

1

в пункте «Таблица» получаем следующие результаты

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

funkcii-excel9-12

Работаем с несколькими условиями при помощи функция ВПР в Excel

Выше мы работали с одним условием – наименованием материалов. Но в реальности обстоятельства могут сложиться иначе. Мы можем получить таблицу с двумя и более условиями, например, как с наименованием материалов, так и с наименованием их поставщиков:

funkcii-excel9-13

Таблица с наименованиями поставщиков и материалов

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

Попробуем решить эту задачу:

  • Добавьте в таблицу левый крайний столбец и объедините два столбца – «Материалы» и «Поставщики».

funkcii-excel9-14

Объедините два столбца – «Материалы» и «Поставщики»

  • Точно так же объедините искомые критерии запроса

funkcii-excel9-15

Объедините искомые критерии запроса

  • Снова в пункте «Таблица» задайте соответствующие показатели

2

В пункте «Таблица» задайте такие показатели

  • На скриншоте выше мы видим следующую формулу (1 – объект поиска; 2 – место поиска; 3 – взятые нами данные).

funkcii-excel9-16

1 – объект поиска; 2 – место поиска; 3 – взятые нами данные

Работаем с выпадающим списком

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

Для этого предпримем следующие шаги:

  • Нажимаем мышкой на ячейку «E8» и во вкладке «Данные» выбираем «Проверку данных»

funkcii-excel9-17

Нажмите на «Проверка данных»

  • Далее во вкладке «Параметры» выберите «Список» и нажмите на «ОК»

funkcii-excel9-18

Выберите «Список» и нажмите на «ОК»

  • Теперь в выпадающем списке мы видим пункт «Цена»
Читайте так же:
Как на Samsung убрать блокировку экрана: простая инструкция

funkcii-excel9-19

Получаем пункт «Цена»

  • Далее нам нужно, чтобы напротив пункта «Цена» выставлялась стоимость материала
  • Нажмите на ячейку «E9», в «Мастере функций» выберите «ВПР». Напротив пункта «Таблица» должны быть выставлены следующе показатели.

3

В «Таблица» должны быть следующе показатели

  • Нажмите на «Ок» и получите результат

funkcii-excel9-20

Видео: Функция ВПР в Excel от А до Я

Excel works!

menu

Функция ВПР в Excel. Поиск и возвращение текста по списку

Функция ВПР — это один из самых полезных компьютерных трюков не только в Excel. Кто узнал о нем, тот без нее жить не может, серьезно. Итак, предположим, что у нас есть две таблицы с текстом. Нужно значения одного списка (Фамилии) передать в ячейки другого, в зависимости от текста-условий (Номера ТС). Если конкретнее пример ниже:

ВПР

Задача1. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Функция ВПР. Решение задачи

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как 🙂 Но на самом деле все проще. Для этого нам нужно понять из чего состоит ВПР

Переменные. Функция ВПР
  • Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1. Номер ТС в Таблице2.
  • Таблица — все столбцы Таблицы1, причем первый столбец должен быть, который мы ищем (Номер ТС)
  • Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения (ФИО)
  • [интервальный_просмотр] — может принимать только два значения — Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.
Использование ВПР в Excel и решение примера

Итак, напишем функцию для нашей задачи:

Функция ВПР в Excel

E:E — это диапазон значений, по которым Excel будет сравнивать условия с Таблицей 1. A:B — вся Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот по счету столбец в Таблице 1, который мы будем переносить в ячейку F1. ЛОЖЬ — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично! Значит люди экономят время, зная о простых и действенных методах. Не отставайте!

Не забудьте растащить функцию до конца диапазона. Не знаете как это сделать быстро? Читайте тут.

А как можно сделать ту же функцию с удобными названными диапазонами (на картинке ниже)? Нравится? Читайте тут.

Функция ВПР в Excel

Комментарии по ВПР
  • Если для одной ячейки в Таблице2 есть несколько значений, в Таблице1 будет выдаваться первое значение.
  • Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#). Чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()
  • Чтобы определить какой именно номер столбца записать в формулу, при выделении диапазона показывается, номер столбца.

Номер столбца

  • Чтобы посчитать сумму для нескольких значений из одной таблицы в другой, воспользуйтесь СУММЕСЛИ
Что в итоге:

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

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