Файл: Методические рекомендации по выполнению лабораторно практических работ по теме Обработка табличной информации.pdf

- 14 -
Диапазон аргументов участвующих в вычислении выделен пунктирной линией.
После растягивания диапазон аргументов смещается «следом» за формулой. Тем самым, в примере, захватываются пустые ячейки. Подобное смещение произойдет при переносе ячейки с формулой в любую новую позицию. Также можно увидеть изменения аргумента в самой записи функции. (вверху – A1:F1, внизу – D1:I1). Данное свойство переноса аргументов формулы относительно расположения самой формулы называется относительной адресацией.
Для того, чтобы при перемещении формулы аргумент не изменялся, следует поставить знаки «$» перед буквой столбца и номером строки. Пример:
Пример ввода функции, где в диапазоне аргументов правая граница становиться неизменной.
Для быстрой установки знаков «$» используется клавиша F4
При использовании зафиксированного аргумента говорят, что используется абсолютная адресация. Сам знак $ фиксирует столбец и/или строку так, чтобы при перемещении формулы значение не менялось. В формуле может стоять знак $ не в обоих случаях, а только в одном. Такая адресация называется смешанной. Пример:

- 15 -
В формулу введены аргументы с зафиксированными столбцами
При смещении формулы вниз аргументы смещаются следом за формулой , т.к. строки не фиксированы (нет знака $)
При переносе формулы в другой столбец значения не изменяются, т.к. столбцы фиксированы (есть знак $).
Ход работы.
1. Откройте лабораторную работу №1.
2. Вычислите поля, выделенные серым цветом a. Светло-серые ячейки в столбцах ИТОГИ вычисляются с использованием функции СУММПРОИЗВ, где первый массив - это диапазон ячеек с числами в области заголовка таблицы (а значит должен быть зафиксирован номер строки), а второй массив - диапазон ячеек стоящих напротив (слева, в той же строке) от вычисляемой ячейки и соответствуют ячейкам в заголовке таблицы (те же буквы столбцов) и используется относительная адресация.

- 16 - b. Темно-серые ячейки вычисляются с использованием Функции ∑– СУММА, где в качестве «Число1» выделяется диапазон всех нижестоящих светло-серых ячеек, вплоть до следующей темной ячейки. c. Столбец ВСЕГО вычисляется как сумма итогов (сложить три числа из столбцов итоги в той же строке, что и вычисляемая ячейка). Используется функция ∑–
СУММА, где удерживая ctrl, выбираются 3 числа из столбцов «итоги» в той же строки. d. Строка «Общий объем учебного времени» вычисляеться как сумма всех вышестоящих темно-серых ячеек e. Строка «понедельная нагрузка» вычисляется как сумма всех вышестоящих ячеек из соответствующего столбца.
3. Сравнить значения таблицы с аналогичной таблицей, набранной в MS WORD.
4. На выполнение работы отводиться 40 минут, отставание на 10 минут снижает оценку на 1 балл.

- 17 -

- 18 -
Математическая
запись
Не
правильная
запись
Что не так
Правильная
запись
Sin a1
=sin a1
Нет скобок, стоит пробел
=sin(a1) cos
2 3
=cos^2 3
Нет скобок, сначала аргумент потом операция, стоит пробел
=(cos(3)^2)
=cos^2*(3)
Сначала аргумент потом операция, аргумент не связан с операцией умножения






3 2
log
3
=log(3,(2/3))
Сначала идет аргумент функции потом основание, разделитель между аргументами « ; » а не« , »
=log((2/3); 3) cos
2
x
2
=cos(x)^2^2
Не правильно расставлены скобки
(как введено, означает:
((cos
2
????)
2
)
=cos(x^2)^2
e
x
=e^x
Что такое «e» компьютер не знает.
Знает функцию EXP
=exp(x)
=exp^x
Надо возводить в степень не имя функции, а просто записать аргумент
Примечание: при вводе формул рассматривайте действия с конца. Это поможет не
запутаться в скобках. Пример: Формула:
????
2 1+
????
1+????
− ????
Этапы выполнения записи:
№
этапа
Запись
Примечание
1. =
Формула начинается со знака «=»
2. =(()-x)
????
2 1+
????
1+????
− ???? Записали последнее действие «-» в скобках, читать как, что-то сложное - х
3. =((()/())-x)
????
2 1+
????
1+????
− ???? Потом рассматриваем дробь как нечто сложное деленное на нечто сложное
4. =(((x^2)/())-x)
????
2 1+
????
1+????
− ???? Сначала записываем формулу, которая стоит в числителе (сверху)
5. =(((x^2)/(1+()))-x)
????
2 1+
????
1+????
− ???? Потом ту, что стоит в знаменателе
(снизу) как 1+ что то сложное
6. =(((x^2)/(1+(z/())))-x)
Продолжаем раскрывать операции.
7. =(((x^2)/(1+(z/(1+y))))-x)
Правила упрощения записи:
1. Используется правило приоритета: «^», « * , / », « + , - ». Если операция стоит левее или на том же уровне, то она вычисляется первой и скобки можно опустить.
Пример: «=(3+(2*4))» можно записать как «=(3+2*4)», или «=(((3^2)*4)-1)» можно записать как «=(3^2*4-1)»
2. Первую операцию можно не заключать в скобки. Пример: «=(3+2)» можно записать «=3+2» или вместо «=(3^2*4-1)» записать «=3^2*4-1»

- 19 -
3. Примечание: если вы не уверены в правильности сокращения, то лучше
записывать в скобках, это не влияет на вычисление, но будет гарантировать
правильность порядка вычисления.
ПСЕВДАНИМ (ИМЯ)
Псевдоним(имя)
— это осмысленное краткое обозначение, позволяющее легче понять назначение ссылки на ячейку
, константы
, формулы или таблицы
До назначения имени.
После назначения псевдонима
(стандартный адрес ячейки)
(смысловое имя, должно отличаться от адреса
ячейки)
СТАНДАРТНЫЕ МАТЕМАТИЧЕСКИЕ ФУНКЦИИ
Математическая
запись
Название
Запись в MS Excel
sin x
Синус
SIN(x)
cos x
Косинус
COS(x)
tg x
Тангенс
TAN(x)
e
x
Экспонента
EXP(x)
|x|
Модуль
ABS(x)
ln x
Натуральный логарифм
LN(x)
lg x
Десятичный логарифм
LOG10(x)
log
y
x
Логарифм
LOG(x;y)
x
Квадратные корень
КОРЕНЬ(x)
n
x
Корень n-ой степени
x^(1/n)
π
Значение числа пи
ПИ()
Ход работы.
1. Выбрать 3 ячейки для исходных данных, дать им псевдонимы соответственно X, Y,
Z. Вписать значения x=5; y=3; z=-2 2. Создать таблицу:
№ задания
Вычисления
1 3. Записать в таблицу вычисления нижеследующих формул (напротив формул приводится правильный ответ для самопроверки)

- 20 -
4. На выполнение работы отводиться 40 минут, отставание на 15 минут снижает оценку на 1 балл
№
Формула для вычисления
Правильный ответ
1.
1 +
????
2 1 +
????
1 + ????
51
2.
1
sin(????
2
− 5)
3
− 25
-23,99783837
3.
√|????|
5
+ cos
2
(
1
????
) + ????
5,340260158
4.
√????
????
+
1
????
+ ????
2
−
1
???? + ????
3 16,1594497
5.
3????
√???? + lg ????
− 1
-3,04426656
6.
???? ∙ (log
????
???? + log
????
????
2
)
11,63163318
7.
sin ????
2
+
1
????
|????−1|
0,082564682
8.
√????
5
+ sin
2
????
2
+ ????
4,397246647
9.
cos
3 2????
3
+ sin ????
2
-0,555301217
10.
1
sin
2
????
????
????
+ 5
+ |????|
2,199752475

- 21 -
ЛАБОРАТОРНАЯ РАБОТА №4.
«ВВОД ФОРМУЛ С УСЛОВИЯМИ»
Цель: Научиться делать вычисления по заданным условиям
Задачи:
1. Изучить операторы условия.
2. Изучить логические функции.
3. Изучить Функции с частичным условием СуммЕсли, СчетЕсли.
4. Изучить работу функции «Если».
Теория.
Особым видом формул в Excel являются логические выражения (условия), которые можно интерпретировать как вопрос, на который можно однозначно ответить
«ДА»(TRUE, ИСТИНА, 1) или «НЕТ»(FALSE, ЛОЖЬ, 0). Логические выражения строятся из логических операторов и логических функций.
ТАБЛИЦА ЛОГИЧЕСКИХ ОПЕРАТОРОВ
Оператор Примеры записи в Excel
Как интерпретировать
=
(А1=3)
Значение в ячейки А1 равно 3? (если равно то будет запись TRUE а если не равно то FALSE)
(B2="красный")
В ячейке В2 записано слово «красный»?
(регистр записи не имеет значения т.е. ответ будет ИСТИНА и для «Красный» или
«КРАСНЫЙ»)
<
(A2Значение в ячейке А2 меньше чем значение в ячейки B2?
<=
(A3<=2)
Значение в ячейке А3 меньше или равно 2?
>
(3>A1)
Число 3 больше чем значение в ячейке А1?
>=
((A1^2)>=(A1*2))
Является ли квадрат значения в ячейки А1 большим или равным удвоенному значению в ячейки А1? (например Если А1=2 то ИСТИНА а если А1=1 то ЛОЖЬ)
<>
(А2<>(B2^2))
<> - знак неравенства. Читать: значение в А2 не равно квадрату значения в B2?
ТАБЛИЦА ЛОГИЧЕСКИХ ФУНКЦИЙ
Функция Примеры записи в Excel
Как интерпретировать
И
И(A1;A2)
Все ли значения в ячейках A1 и A2 являются истинными? Все ли введенные условия выполняются?
ИЛИ
ИЛИ(B1:B5)
Есть ли среди ячеек из диапазона В1:В5 хотя бы одно истинное? Есть ли хотя бы одно выполнимое условие из введённых?
НЕ
НЕ(B5)
Является ли выражение в В5 ложным?

- 22 -
Примечание: в логических функция могут участвовать и значения других типов,
тогда 0 интерпретируется как ЛОЖЬ(FALSE) а все остальные значения
интерпретируется как ИСТИНА(TRUE).
ФУНКЦИИ С «ЧАСТИЧНЫМИ УСЛОВИЯМИ» (КРИТЕРИЯМИ)
Критерий может быть трех видов:
1. адрес ячейки. Пример B3. Что интерпретируется как сравнения значений из диапазона со значением в ячейки В3.
2. Значение. Пример 7 или "красный", что интерпретируется как сравнения значений из диапазона с введенным значением.
3. "#Х", где # - любой логический оператор, а Х любое значение. Пример "<>7".
Функция с критериями
Функция
Описание
Пример использования
СчетЕсли
СчетЕсли(диапазон; критерий)
Считается сколько значений из диапазона удовлетворяют критерию
СчетЕсли(A1:A15; "<5")
Результатом будет число значений меньше 5 из диапазона ячеек
A1:A15
СуммЕсли СуммЕсли(диапазон; критерий; диапазон суммирования)
Складываются значения из диапазона суммирования стоящие напротив тех значений из диапазона, что удовлетворяют критерию
СуммЕсли(А1:А15; "красный";
B1:B15)
Складываются значения из диапазона B1:B15 напротив которых, в столбце A, написано
«красный»
Примечание. Для того чтобы функция с частичными условиями могла работать как полноценная функция с условиями, можно поступить следующим образом:
1. Создать новый столбец(строку), где будет вписано необходимое логическое выражение.
2. С помощью функций СчетЕсли или СуммЕсли в качестве диапазона выбирается созданный вами столбец и сравнивается со значением «истина».
Например:
Подсчитать число значений из диапазона A1:A15, которые ниже среднего значения из соответствующего диапазона

- 23 -
ФУНКЦИЯ ЕСЛИ
ЕСЛИ(логическое выражение; формула для ИСТИНА; формула для ЛОЖЬ)
Что можно интерпретировать как: если на вопрос логического выражения ответ положительный, то вычислять формулу для ИСТИНА, а если отрицательный, то вычислять формулу для ЛОЖЬ.
Использовать эту функцию целесообразно, если в логическом выражении используются адреса ячеек, значения которых могут меняться.
Пример: Ввели следующую формулу:
A
B
C
D
1
=Если((A1>3);A1-3;A1)
Не меняя формулы в зависимости от того, что стоит в А1 будут получаться следующие результаты:
A
B
C
D
1 1
1
A
B
C
D
1 2
2
A
B
C
D
1 3
3
A
B
C
D
1 4
1
A
B
C
D
1 5
2
И т.д.
Случайные числа от 0 до 100 в диапазоне A1:A15
=СЛЧИС()*100
Среднее значение диапазона A1:A15
=СРЗНАЧ(A1:A15)
=A1<$A$16
Нужное логическое выражение
=СЧЕТЕСЛИ(B1:B15; ИСТИНА)