3.5. Определение среднего арифметического значения чисел, удовлетворяющих некоторому условию

Указание

Все задачи данного раздела решить двумя способами:

• с использованием функции СРЗНАЧ;

• без использования этой функции.

3. Даны 15 чисел. Подготовить лист для определения среднего арифметического тех из них, которые больше 10. Рассмотреть два случая:

• известно, что числа, большие 10, среди заданных есть;

• допускается, что чисел, больших 10, среди заданных может не быть.

Решение

1. Заполняем таблицу входных данных.

2. Устанавливаем курсор на ячейку В2 и вводим формулу =ЕСЛИ(A2>10;A2)

Используя маркер заполнения, формулу из ячейки В2 распространяем на диапазон В3:В16.

3. Устанавливаем курсор на ячейку С3 и ввожу формулу

=ЕСЛИ(СРЗНАЧ(B2:B16)>0;

СРЗНАЧ(B2:B16);"нет чисел>10")

4. Устанавливаем курсор на ячейку F3 и ввожу формулу =ЕСЛИ(СУММЕСЛИ(A2:A16;">10")/СЧЁТЕСЛИ(A2:A16;">10")>0;СУММЕСЛИ(A2:A16;">10")/СЧЁТЕСЛИ(A2:A16;">10");"нет чисел>10")

5. После нажатия ENTER отображается результат

3.6. Поиск значения, соответствующего некоторому другому значению

Примечание

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

1. Имеется таблица с данными вида:

Столбец 1	Столбец 2	…

Необходимо в столбце N найти значение из той же строки столбца M, в которой записано заданное значение, например, по заданному номеру телефона найти фамилию, по названию государства – его столицу и т. п. Принимается, что N <M и данные в столбце M неупорядочены.

2. Имеется таблица с данными вида:

Строка 1
Строка 2
…

Необходимо в строке с номером N найти значение из того же столбца строки M, в котором записано заданное значение. Принимается, что N < M и данные в строке M неупорядочены.

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

3. В таблице представлена информация вида:

Страна	Столица
Чехия	Прага
США	Вашингтон

Подготовить лист для определения:

• страны, столица которой – Лондон;

• страны, столица которой будет указываться в отдельной ячейке.

В обоих случаях принять, что искомая страна в списке есть.

Решение

1. Заполняем таблицу входных данных.

2. Устанавливаем курсор на ячейку В9 и ввожу столицу 3. Устанавливаем курсор на ячейку D10 и ввожу формулу =ВПР("Лондон";A2:B6;1) 4. Устанавливаем курсор на ячейку D11 и ввожу формулу =ИНДЕКС(A2:B6;ПОИСКПОЗ(B9;B2:B6;0);1) 5. После нажатия ENTER отображается результат

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

Указание

Все задачи данного раздела решить двумя способами:

• без использования функций МИН и МАКС;

• с использованием таких функций.

3. Подготовить лист для нахождения такого наименьшего n, что:

Решение

В ячейку В2 ввожу следующую формулу 1/A2 и распространяю ее на диапазон В3:В16 В ячейку С3 ввожу следующую формулу С2+В3 и распространяю ее на диапазон С4:С16 В ячейку В18 ввожу следующую формулу ПОИСКПОЗ(3;C2:C16;1)+1 и получаю результат. В ячейку D2 ввожу следующую формулу ЕСЛИ(C2<=3;C2;"") и распространяю ее на диапазон D3:D16 В ячейку В19 ввожу следующую формулу ПОИСКПОЗ(МАКС(D2:D16);D2:D16;1)+1 и после нажатия ENTER получаю результат.