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

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

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

Указания по выполнению:

1. Необходимо использовать функцию MS Excel НАИМЕНЬШИЙ(массив;k), где
Массив — массив или диапазон числовых данных, для которого определяется k-ое наименьшее значение;
k — позиция (начиная с наименьшей) в массиве или интервале ячеек данных.

2. Собственно решением задачи будет являться формула
=НАИМЕНЬШИЙ(A1:B100;1)+НАИМЕНЬШИЙ(A1:B100;2)+НАИМЕНЬШИЙ(A1:B100;3) введенная в соответствующую ячейку Листа.

3. Запомните также, что у функции НАИМЕНЬШИЙ(массив;k) есть соответствующий аналог - функция НАИБОЛЬШИЙ(массив;k). Кроме того, Вы можете столкнуться с задачами, когда требуется определить, например, сумму двух наименьших значений из значений, внесенных в четыре несмежные ячейки, например, ячейки A1, С1, E1 и G1. Решением этой задачи будет, например, формула
=НАИМЕНЬШИЙ((A1;C1;E1;G1);1)+НАИМЕНЬШИЙ((A1;C1;E1;G1);2)

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

Указания по выполнению:

Во-первых, вам понадобится функция МОДА (число1;число2; …), которая собственно и возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Таким образом, выбрать наиболее часто встречающееся число в диапазоне можно, просто записав формулу

=МОДА(A1:B100)

Во-вторых, речь в задании идет о ТОЛЬКО о положительных элементах. Таким образом, исходные данные для функции МОДА() надо представить таким образом, чтобы исключить из рассмотрения отрицательные и равные нулю числа. Это можно сделать, используя функцию ЕСЛИ(), в качестве условия в которой будет фигурировать проверка условия >0. В результате формула массива, которая находит положительный элемент, который наиболее часто встречается в диапазоне будет записана так:

{=МОДА(ЕСЛИ(A1:B100>0;A1:B100;""))}

Обратите внимание, что данная формула является формулой массива, то есть будет корректно работать только после нажатия комбинации клавиш Ctrl+Shift+Enter.

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

В-третьих, особенностью функции МОДА является то, что в отсутствие одинаковых данных в диапазоне вообще, в результате выполнения данной функции появляется сообщение об ошибке Excel #H/Д. Возможно, более корректен будет вид формулы, предусматривающий эту возможность, написанный с задействованием функции ЕНД(), которая возвращает значение ИСТИНА в том случае, если ссылается на значение ошибки #Н/Д (значение недоступно).
Таким образом итоговая формула массива для нахождения положительного элемента, который чаще всего встречается в диапазоне, предусматривающая отсутствие одинаковых данных (в этом в качестве результата выдается соответствующее сообщение) будет выглядеть так:

={ЕСЛИ(ЕНД(МОДА(ЕСЛИ(A1:B100>0;A1:B100;"")));"отсутствуют одинаковые данные";МОДА(ЕСЛИ(A1:B100>0;A1:B100;"")))}

В-четвертых, не будет ошибкой, если Вы будете выполнять данное задание в два действия, с тем, чтобы итоговая формула была проще. То есть можно создать диапазон, по размерности совпадающий с исходными (например C1:D100), каждое из значений которого будет равно соответствующему элементу исходного диапазона A1:B100 в том случае, если оно положительное, и равно “”, в том случае, если оно отрицательное или равное нулю. Т.е. в ячейку С1 следует записать

=ЕСЛИ(A1>0;A1;"")

и соответствующие формулы – во все остальные ячейки диапазона C1:D100, который в результате будет содержать только положительные элементы исходного диапазона, а в тех местах, где в исходном диапазоне были отрицательные и равные нулю элементы, значения будут отсутствовать вообще. Функция МОДА() в этом случае не будет включать в рассмотрение пустые ячейки вообще. Таким образом, результирующая формула в этом случае будет выглядеть как:

=ЕСЛИ(ЕНД(МОДА(C1:D100));"отсутствуют одинаковые данные";МОДА(C1:D100))

которая уже не будет являться формулой массива. При этом можно использовать и формулу МОДА(C1:D100), то есть формулу без проверки на корректность данных, однако в случае появления ошибки #Н/Д необходимо иметь представление о том, чем она вызвана, и о том, как сделать запись более корректной.

Задание 2С.

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

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

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

Указания по выполнению:

Первое условие – то, что суммируемые элементы диапазона должны быть большими либо равными -5, можно задать как ">=-5" или ">="&G2 – в том случае, если упомянутое значение содержится в ячейке G2. Однако использование функции СУММЕСЛИ() не поможет, если условий больше одного, как в данном случае (второе – то, что суммируемые элементы должны быть меньшими либо равными 10), а также если условие чуть более сложное – например, найти сумму четных элементов диапазона.

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

{=СУММ(ЕСЛИ(ОСТАТ(A1:B4;2)=0;A1:B4;0))}

а просуммировать элементы этого же диапазона из отрезка[-5;10] – так:

{=СУММ(ЕСЛИ((A1:B4>=-5)*(A1:B4<=10);A1:B4;0))}

обратите внимание, что обе формулы являются формулами массива, т.е. будут выдавать корректный результат при нажатии клавиш Ctrl+Shift+Enter (иначе будет выдано #ЗНАЧ).

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

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

Дата добавления: 2014-10-31