ЛР 7 таблицы. Методические указания. Расчеты и обработка данных

• Название: Методические указания. Расчеты и обработка данных
• Анкор: ЛР 7 таблицы
• Дата: 15.04.2022
• Размер: 3.08 Mb.
• Имя файла: LAB_RAB_7Ekh.docx
• Тип: Методические указания; #477462

лаборатрная РАБОТА № 7

Расчеты в Excel.
Методические указания.

Расчеты и обработка данных

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

Данные в таблицах можно:

• 
  сортировать,
  
• 
  применять к ним фильтры,
  
• 
  формировать значения с помощью автозаполнений.
  

Можно выполнять анализ данных. Значения в таблицах можно группировать.

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

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

Математические вычисления

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

Суммирование. Простая сумма

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

Синтаксис функции:

СУММ(А),

где A– список от 1 до 30 элементов, которые требуется суммировать.

Элемент может быть ячейкой, диапазоном ячеек, числом или формулой.

Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула =СУММ(В2:В7), указанная в ячейке, например, В8, тождественна формуле =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ() добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Выборочная сумма

Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ.

Синтаксис функции

СУММЕСЛИ(А; В; С),

где A– диапазон вычисляемых ячеек.

В– критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;

С– фактические ячейки для суммирования.

В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать.

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

При этом открывается окно установки аргументов функции. В этом окне устанавливается диапазон исходных данных в верхней строке и критерий-условие во второй строке.

Для указания диапазона данных используется значок с красной стрелочкой в конце строки.


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


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

Они находятся в Мастере подстановки функций

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

Авторская формула также начинается со знака = для выбранной результативной ячейки.

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

Работа с мастером функций

Общие сведения. Способы запуска

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

Существует 3 способа запуска мастера функций:

1. 
   С помощью кнопки в строке формул; 
   
2. 
   С помощью команды "Другие функции..." кнопки  ;
   
3. 
   С помощью пункта меню "Вставка" —> "Функция";
   

Первый шаг

После выполнения одного из этих действий откроется окно мастера функций: На первом шаге мы выбираем нужную нам функцию, пользуясь поиском или фильтром категорий. После выбора нужной функции нажимаем "ОК" и попадаем на второй шаг.

Второй шаг

Открывается следующее окно:

Вставка вложенной функции

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 — до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию — специальная кнопка на панели "Строка формул":


После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.
Задания для практической работы
Задание 1. Заполните таблицу, используя функции СЧЕТЕСЛИ() и СЧЕТЗ()

Задание 2. Используя функцию ЕСЛИ заполнить столбец G: если затраты превышают 35000$, то предоставляется скидка 5%, в противном случае – скидки нет. Используя функцию СРЗНАЧЕСЛИ в ячейке С16, посчитать среднюю стоимость посылки. Используя функцию СРЗНАЧЕСЛИМН в ячейке Е16, посчитать среднюю стоимость международных писем.


Таблица расчетов
Задание 3. Построить таблицы и рассчитать сумму затрат на выработку тепла по котельным МП «ТепЛо-1». Знак «?» в таблицах заменить данными самостоятельно.



Задание 4. Заполнить таблицу «Ввод данных в Excel» (таблица ниже) согласно следующим требованиям:

1. 
   Ввод текста осуществлять, соблюдая параметры форматирования ячеек.
   
2. 
   Выполнить тиражирование значения (столбец B).
   
3. 
   Оформить число 15,3 в форматах Excel (столбец C), выбирая формат для каждой выделенной ячейки соответственно.
   
4. 
   Заполнить произвольными значениями, не превышающими 15, с проверкой вводимых данных (столбец D). Выполнить Данные / Проверка вводимых значений / Тип данных / Действительные.
   
5. 
   Заполнить столбец E случайными числами с заданным интервалом.
   

Например: = СЛЧИС()*12+3. (математическая функция СЛЧИС() – возвращает равномерно распределенное случайное число в интервале [0; 1]).

6. 
   Заполнить столбец F упорядоченным рядом чисел из интервала [3,15] с шагом 1,5. (Прогрессия).
   
7. 
   Заполнить столбец G рабочими днями (автозаполнение)
   

Таблица «Ввод данных»

Задания по вариантам

Столб.	B	D	E	F	G
Вар.
1	23,9	<= 20	[‑2, 10]	1; шаг 0,5	с 01.10.10
2	34,5	>= 5	[0, 25]	7; шаг 1,5	с 01.11.10
3	0,92	[2, 15]	[–1, 15]	0; шаг 0,3	с 01.12.10
4	98,1	< 34	[20, 35]	-1; шаг 0,1	с 03.01.13
5	8,54	> ‑12	[–3, 18]	2; шаг 1,2	с 01.02.11

Вопросы к практической работе №7

1. 
   Что такое функции табличного процессора MS Excel?
   
2. 
   Аргументы функций.
   
3. 
   Способы ввода функций.
   
4. 
   Порядок ввода функций с клавиатуры.
   
5. 
   Мастер функций.
   
6. 
   Отображение на экране введенных в ячейки формул.
   
7. 
   Синтаксис логической функции ЕСЛИ.