ФОРМУЛЫ, ИМЕНА, МАССИВЫ. ФОРМУЛЫ НАД МАССИВАМИ

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

(файл .xls на странице  www.matburo.ru/sub_appear.php?p=l_excel ) 

ФОРМУЛЫ, ИМЕНА, МАССИВЫ.  ФОРМУЛЫ НАД МАССИВАМИ

Задание 1. 

Выполните вычисления по следующим формулам:

                                              A= 4+3*x+2*x2 +x3 ,     B= x + y + z ,     C= 1+ x ,

                                                                                                                    x y z⋅                   ⋅     x y⋅

считая заданными величины x, y, z соответственно в ячейках A3, B3 и C3.

Выполнение. 

Введем в ячейки A3, B3 и C3 конкретные значения переменных, например  1.2, 3, 1.5 и присвоим этим ячейкам соответственно имена X, Y, Z . Для присвоения имен ячейкам используйте команду Вставка /Имя /Присвоить.

В ячейки A5, A6 и A7 введем поясняющий текст, а в ячейки B5, B6 и B7 соответствующие формулы. Например, для вычисления первого значения можно ввести формулу =4+3*X+2*X^2+X^3. Однако, лучше провести вычисления по схеме Горнера, которая позволяет уменьшить число выполняемых операций. В этом случае формула примет вид =((X+2)*X+3)*X+4. Предложенные формулы используют в качестве операндов, созданные имена, что делает их похожими на соответствующие математически формулы. При необходимости, в формулах также можно использовать и ссылки на ячейки рабочей таблицы. В этом случае нужная формула имела бы вид   = (A3+2)*A3+3)*A3+4.

Вид электронной таблицы приведен на следующем рисунке.

Задание 2. 

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

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

Методические указания.

Для вычисления среднего значения используйте функцию СРЗНАЧ.

Задание 3. 

На листе запишите формулу для вычисления произведения сумм двух одномерных массивов

                                 n                 n

A и B, т.е. R = ∑a_i ⋅∑b_i; где a_i и b_i соответствующие элементы массивов, а n – их размерность.

                               i=1             i=1

Выполнение.  

Конкретные данные, например, A={1.5, 1.23, 1.65, 2.44, 1.44} и B={2.11, 3.12, 2.14, 2.33, 3.12}  введем соответственно в ячейки A2:E2 второй и A3:E3 третьей строки листа 3 рабочей таблицы. Затем в ячейку A5 введем формулу: =СУММ(A2:E2)*СУММ(A3:E3). Если диапазону A2:E2 присвоить имя А, а диапазону А3:Е3 – имя В, то можно применить формулу:  =СУММ(A)*СУММ(В). Вид электронной таблицы приведен на рисунке.

Задание 4. 

На листе запишите формулы  вычисления сумм S_i каждой строки двумерного массива

n

(матрицы) D,  т.е.  S_i =∑d_{i j,} , i =1,2,...,m; где m – количество строк матрицы, n –

j=1

количество столбцов

Выполнение.  

Конкретные данные {d_i,j}, i=1,2,…,5, j=1,2,…,4. (матрица пять строк четыре столбца), введем в ячейки A1:D5. Вычислим суммы каждой строки и поместим их в ячейки F1:F5. Для этого поместим в ячейку F1 формулу: =СУММ(A1:D1), и с помощью маркера автозаполнения скопируем ее в ячейки F2:F5. Так как в формуле используется относительная ссылка, то каждая копия настроится на свое местоположение и будет вычисляться сумма соответствующей строки матрицы.

Задание 5. 

На листе запишите формулы для вычисления значений элементов массива  Y_i = a_i / max(b_i) ,i=1, 2,…,n, где a_i и b_i элементы соответствующих массивов, а n – их размерность.

Выполнение.

Конкретные данные {a_i},i=1,2,…,5; {b_i},i=1,2,…,5,  введем соответственно в ячейки A2:E2 второй, и A3:E3 третьей строки листа 5 рабочей таблицы. Затем в ячейку A5 введем формулу: =A2/ МАКС($A$3:$E$3) и с помощью маркера автозаполнения скопируем ее в ячейки B5:F5. Во втором операнде использована абсолютная ссылка, поэтому на новое местоположение будет настраиваться только первый операнд.

Задание 6. 

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

Выполнение.

Произвольные  данные   введем, например,  соответственно в ячейки A2:D6 листа рабочей таблицы. Для вычисления суммы положительных чисел, в  ячейку F4 введем формулу: =СУММЕСЛИ(A2:D6;”>0”; A2:D6), а для вычисления количества отрицательных в ячейку F5 формулу: =СЧЕТЕСЛИ(A2:D6;”<0”).

Задание 7. 

На листе заполните произвольный диапазон любыми числами. Найдите сумму чисел больших заданного в ячейке A1 числа.

Выполнение.

Конкретные данные  введем, например, соответственно в ячейки A2:E2 листа рабочей таблицы. В ячейке А1 запишем произвольное число, а в ячейку A4 введем формулу:

=СУММЕСЛИ(A2:E2;”>”&A1; A2:E2).

Задание 8. 

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

Методические указания.

Среднее арифметическое значение положительных чисел равно частному от деления суммы положительных чисел на количество положительных. Для решения задания используйте функции СУММЕСЛИ, СЧЕТЕСЛИ и ABS.

Задание 9. 

На листе создайте произвольный список имен, и присвойте ему имя ИМЕНА. Определите, сколько раз  в  списке ИМЕНА содержится Ваше имя, заданное в ячейке.

Методические указания.

Используйте функцию СЧЕТЕСЛИ.

Задания для самостоятельной работы Задание 1С. 

Написать    формулы,           заполнения      диапазона         А1:A100              равномерно     распределенными случайными числами из отрезка [-3,55; 6,55], а диапазона B1:B100 случайными  целыми числами из отрезка [-20;80]. Скопировать значения указанных диапазонов в диапазоны D1:D100 и E1:E100, увеличив вдвое значения второго диапазона. 

Выполнение.

Для заполнения диапазона А1:A100 равномерно распределенными случайными числами из отрезка [-3,55; 6,55] введите в ячейку A1 формулу =СЛУЧМЕЖДУ(-3,55;6,55) или =СЛЧИС()*9,1-3,55 а затем скопируйте ее в остальные ячейки диапазона.

Для заполнения диапазона B1:B100 равномерно распределенными случайными числами из отрезка [-20; 80] введите в ячейку B1 формулу =СЛУЧМЕЖДУ(-20;80) или =СЛЧИС()*100-20 а затем скопируйте ее в остальные ячейки диапазона.

Для увеличения вдвое значений диапазона B1:B100 при копировании в диапазон E1:E100 введите в ячейку E1 формулу =B1*2 а затем скопируйте ее в остальные ячейки диапазона.

В свободные ячейки написать формулы:

1.       нахождения среднего арифметического построенных диапазонов (используйте функцию СРЗНАЧ());

2.       максимального и минимального элементов, построенных диапазонов (используйте функции МАКС() и МИН());

3.       суммы трех наименьших элементов диапазона А1:B100 (см. пояснения);

4.       положительного элемента, который чаще всего встречается в диапазоне А1:B100 (см. пояснения).

Задание 2С. 

Для заданного диапазона ячеек рабочего листа Excel.

Написать формулы вычисляющие:

1.       Сумму элементов диапазона, значения которых попадают в отрезок  [-5; 10] (см. пояснения). 

2.       Количество элементов диапазона больших некоторого числа, записанного в ячейке рабочей таблицы (например, из ячейки G1) (используйте функцию СЧЁТЕСЛИ()).

3.       Количество элементов диапазона, значение которых меньше среднего значения элементов диапазона (используйте функции СЧЁТЕСЛИ() и СРЗНАЧ(), см. также пояснения к Заданию 7).