Решение, использующее функцию ВПР

Работа с табличным процессором MS Excel .

Освоение приемов работы с электронными таблицами

Практическое занятие 7.

Импорт данных, вычисления с использованием условных выражений

Цель работы:

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

1. Подготовка к работе
Скопируйте в свою папку «Мои документы» файл Исходный данные10. txt, из папки serv\\..\2 курс, откройте его в блокноте и изучите структуру файла.

Импорт данных в электронные таблицы

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

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

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

Задание

3.1.Импортируйте данные из файла Исходный данные10. txt в рабочий лист Excel.

3.2.Для каждого испытуемого подсчитайте суммарное количество баллов и проставьте отметку по следующей шкале: меньше 30 – «2», от 30 до 40 – «3», от 40 до 50 – «4», свыше 50 – «5».

3.3.Вычислить средний балл по каждому тесту.

4. Указания к выполнению работы
Для вычисления оценок можно использовать вложенную функцию ЕСЛИ. Формула вычисления оценки реализует условный оператор:
если сумма<30, то 2 иначе
если сумма<40, то 3 иначе
если сумма<50, то 4 иначе 5

Почему таким способом не удастся проставить оценки по 10-баллной шкале?

5. Функция вертикального просмотра таблицы ВПР

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

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

Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, БазаДанных или Список.

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.
Значения в первом столбце аргумента инфо_таблица могут быть текстами, числами или логическими значениями.
Регистр не учитывается (т. е. строчные и заглавные буквы не различаются).

Номер_столбца - это номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца инфо_таблица и так далее. Если номер_столбца меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в инфо_таблица, то функция ВПР возвращает значение ошибки #ССЫЛ!.

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

Замечания

Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента инфо_таблица, то функция ВПР возвращает значение ошибки #Н/Д.
Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.
Функция ГПР – горизонтального просмотра таблицы – работает аналогично для случая, когда информационный массив располагается по строкам.

Решение, использующее функцию ВПР

6.1. Создайте новый лист с именем «ВПР» и скопируйте в него полученную раннее таблицу.

6.2. Создайте новый лист с именем «Справка» и разместите не нем справочную таблицу:

0	2
30	3
40	4
50	5

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

6.3. Выделите диапазон с данными и присвойте ему имя tabl (Вставка – Имя – Присвоить)

6.4. Для вычисления оценки используйте функцию:

=ВПР(_адрес_суммы_оценнок,табл,2,ИСТИНА)

6.5. Измените справочную таблицу и вычисление оценки таким образом, чтобы дополнительно выставлялась оценка в виде: «отлично», «хорошо» и т.п.

6.6. Предъявите работу преподавателю.

Дата добавления: 2019-02-22; просмотров: 144; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!