Задание 1. Определить номер позиции (счет ведется с единицы) ячейки, содержащей число 31 в одномерном массиве A2: D2.
В ячейке А6 запишем формулу:
= ПОИСКПОЗ(31;A2:D2;0)
(если вместо 0 поставить 1, то ищется число меньше-равно искомого; если вместо 0 поставить -1, то ищется число больше-равно искомого)
Результат поиска – 1.
A
B
C
D
1
2
2
31
4
52
11
3
12
7
12
3
4
123
5
6
1
В ячейке А6 запишем формулу :
= ПОИСКПОЗ(31;A1:А4;0)
Результат поиска – 2.
A
B
C
D
1
2
2
31
4
52
11
3
12
7
12
3
4
123
5
6
2
Задание 2. Определить для диапазона A1: D4 порядковый номер ячейки, содержащей самое большое число.
В ячейке А6 запишем формулу:
= ПОИСКПОЗ(МАКС(А1:А4);A1:А4;0)
Результат поиска – 4.
A
B
C
D
1
2
2
31
4
52
11
3
12
7
12
3
4
123
5
6
4
Задание 3. Прочитать содержимое ячейки, находящейся в диапазоне A2: D5 на пересечении третьей строки и столбца, в самой верхней ячейке которого записана цифра 4.
В ячейке А7 запишем формулу:
= ГПР(4;А2:D5;3;0)
Результат поиска – 43.
A
B
C
D
1
2
31
4
52
11
3
12
7
12
3
4
123
43
28
19
5
2
67
82
5
6
7
4 3
Задание 4. Имеется обширная электронная таблица, в которой записаны индексы товаров и их цены. Менеджеру по продажам нужно срочно узнать цены ряда товаров по их индексам. Поскольку таблица большая, поиск «вручную» неэффективен. Что следует сделать менеджеру?
A
B
C
D
E
1
Цена товара
2
Индекс
Товара
ФП
ЛН
СК
ДЦ
3
Цена
товара
3000
4820
7000
5800
4
5
Менеджеру следует составить таблицу Запрос с двумя рабочими ячейками Индекс товара и Цена товара. В ячейке А9 запишем формулу:
= ГПР(“СК”;В2:Е3;2;0)
Результат поиска – 7000.
6
7
Запрос
8
Индекс
товара
Цена
товара
9
СК
7000
Задание 5. Прочитать содержимое ячейки, находящейся в диапазоне A2: D5 на пересечении второго столбца и строки, в крайней левой ячейке которого записано число 12.
В ячейке А7 запишем формулу:
= ВПР(12;А2:D5;2;0)
Результат поиска – 7.
A
B
C
D
1
2
31
4
52
11
3
12
7
12
3
4
123
43
28
19
5
2
67
82
5
6
7
7
Задание 6. Втаблице сведены данные производительности агрегата по месяцам. Требуется организовать быстрый и эффективный поиск значений производительности для каждого месяца.
A
B
C
1
Производительность
2
Месяц
Объем
3
Январь
2665
4
Февраль
3772
5
Март
4879
6
Апрель
5986
7
Май
7093
8
Июнь
8200
9
Июль
9307
10
Август
10414
11
Сентябрь
11521
12
Октябрь
12628
13
Ноябрь
13735
14
Декабрь
14842
Менеджеру следует составить таблицу Запрос с двумя рабочими ячейками Месяц и Объем.
A
B
C
D
E
1
Запрос
2
Месяц
Объем
3
Март
4879
В ячейке Е3 запишем формулу:
= ВПР(D3;A3:B14;2;0)
Результат поиска – 4879.
Задание 7. Определить, что записано в ячейке, находящейся на пересечении третьей строки и второго столбца диапазона B3: D7.
В ячейке А10 запишем формулу:
= ИНДЕКС(В2:D6;3;2)
Результат поиска – 56.
A
B
C
D
1
2
23
61
41
3
11
92
48
4
24
56
72
5
55
51
7
6
26
73
49
7
8
9
10
56
Задание 8. Найти в ведомости фамилии сотрудников с минимальной и максимальной зарплатой.
A
B
1
Ведомость
2
Фамилия
Зарплата
3
Иванов
2665
4
Петров
3772
5
Сидоров
4879
6
Нестеров
5986
7
Создайте дополнительную таблицу с ячейками Минимальная зарплата и Максимальная зарплата.
В ячейке В11 запишем формулу:
= ИНДЕКС(А3:А6;ПОИСКПОЗ(МИН(В3:В6);В3:В6;0))
Результат поиска – Иванов.
В ячейке В10 запишем формулу:
= ИНДЕКС(А3:А6;ПОИСКПОЗ(МАКС(В3:В6);В3:В6;0))
Результат поиска – Нестеров.
8
9
10
Максимальная
зарплата
Нестеров
11
Минимальная
зарплата
Сидоров
Задание 9. В таблице приведены данные по прибыли за четыре года для ряда фирм. Составить формулы для определения лучшей фирмы года и лучшего года для каждой из фирм.
A
B
C
D
E
F
1
Фирма
Год
Лучший
Год
2
2006
2007
2008
2009
3
Авеста
276
733
467
363
4
Корона
719
482
543
222
5
Сокол
325
944
473
617
6
Гнездо
813
685
628
547
7
Лучшая в году
Задание 10. Используя данные предыдущего примера, сделайте так, чтобы можно было создать запрос о показателях отдельной фирмы.
Задание 11. Фирма, специализирующая на переводах с иностранных языков, определяет стоимость своих услуг по тарифной сетке в зависимости от языка оригинала и количества страниц. Определить стоимость перевода с португальского языка на русский текста, содержащего 68 страниц.
A
B
C
D
1
Тарифная сетка
2
Кол-во
Страниц
Языки
3
немецкий
английский
португальский
румынский
арабский
4
От 10 стр.
12
10
14
15
18
5
От 20 стр.
18
15
18
17
22
6
От 30 стр.
22
20
24
25
26
7
От 40 стр.
27
26
29
28
30
8
От 50 стр.
30
28
39
32
34
9
От 60 стр.
39
37
42
41
42
10
От 70 стр.
45
40
48
47
49
11
От 80 стр.
48
46
52
52
54
Лабораторная работа №13
Логические функции
Цель работы: изучениелогических функций
Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий.
Функция ЕСЛИ позволяет определить, выполняется ли указанное условие. Если условие истинно, то значением ячейки будет выражение 1, в противном случае – выражение 2.
=ЕСЛИ (условие; выражение 1; выражение 2) Например, =ЕСЛИ (В2>20; «тепло»; «холодно») Если значение в ячейке В2>20, то выводится сообщение тепло, в противном случае – холодно.
Задание 1.
1) Заполните таблицу и отформатируйте ее по образцу:
2) Заполните формулами пустые ячейки. Абитуриент зачислен в институт, если оценка по математике 4 или 5, в противном случае – нет. 3) Сохраните документ под именем студент. Совместно с функцией ЕСЛИ используются логические функции И, ИЛИ, НЕ. Например, =ЕСЛИ(И(Е4<3; Н8>=3); «выиграет»; «проиграет»). Если значение в ячейке Е4<3 и Н8>=3, то выводится сообщение выиграет, в противном случае – проиграет.
Задание 2.
1) Создайте файл «Студент». 2) Скопируйте таблицу на Лист 2. 3) После названия таблицы добавьте пустую строку. Введите в ячейку В2 Проходной балл, в ячейку С2 число 13. Изменим условие зачисления абитуриента: абитуриент зачислен в институт, если сумма баллов больше или равна проходному баллу и оценка по математике 4 или 5, в противно м случае – нет.
4) Сохраните полученный документ.
Задание 3.
1. Заполните таблицы. 2. Заполните формулами пустые ячейки. Засушливым считается месяц, в котором количество выпавших осадков меньше 15 мм (воспользуйтесь формулой СЧЕТЕСЛИ). 3. Заполните столбец Прогноз:
засуха, если количество осадков < 15 мм;
дождливо, если количество осадков >70 мм;
нормально (в остальных случаях).
4. Представьте данные таблицы Количество осадков (мм) графически, расположив диаграмму на Листе 2. Выберите тип диаграммы и элементы оформления по своему усмотрению. 5. Переименуйте Лист 1 в Метео, Лист 2 в Диаграмма. Удалите лишние листы рабочей книги.
6) Установите ориентацию листа – альбомная, укажите в верхнем колонтитуле (Вид, Колонтитулы) свою фамилию, а в нижнем – дату выполнения работы. 7) Сохраните таблицу под именем метео.
Задание 4.
Дана последовательность чисел: 25, –61, 0, –82, 18, –11, 0, 30, 15, –31, 0, –58, 22. В ячейку А1 введите текущую дату. Числа вводите в ячейки третьей строки. Заполните ячейки К5:К14 соответствующими формулами.
Отформатируйте таблицу по образцу:
Лист 1 переименуйте в Числа, остальные листы удалите. Результат сохраните в своей папке под именем Числа.xls.
2019-05-24
501
Обсуждений (0)
м случае – нет.
