Портфолио на тему: "Решение задач в MS Excel"

Задача 1

Произвести вычисления над числами

Задача 2

Рассчитать по формуле число вариантов рассаживания учащихся в группе Ор-321

Дано:

Число уч-ся в группе	Кол-во выбранных уч-ся
23	2
23	4
23	3

Решение:

1. По данным значений рассчитываю число вариантов рассаживания учащихся по формуле:

ЧИСЛКОМБ(23;2)
ЧИСЛКОМБ(23;4)
ЧИСЛКОМБ(23;3)

ЧИСЛКОМБ из категории Математические, использую Мастер функции на панели инструментов Стандартная.

Задача 3

Рассчитать расходы за один день учащегося группы Ор-321.

Дано:

Решение:

1. По данным значений рассчитываю расходы за один день по формуле СУММПРОИЗВ из категории Математические (формула имеет вид

СУММПРОИЗВ(C14 : C17; D14 : D17). Использую Мастер функции на панели инструментов Стандартная.

Задача 4

Дано: стороны треугольника.

Сторона a	5
Сторона b	7
Сторона c	9

Рассчитать по формулам:

– полупериметр;

– площадь;

– высоты;

– медианы;

– биссектрисы;

– радиус вписанной окружности;

– радиус описанной окружности.

Решение:

1. Рассчитываю полупериметр по формуле (B2 + B3 + B4) / 2.

2. Рассчитываю площадь по формуле КОРЕНЬ (B7 * (B7 – B2) * (B7 – B3) * (B7 – B4)).

3. Рассчитываю высоту по формуле (2 * B8) / B2 ; (2 * B8) / B3 ; (2 * B8) / B4.

4. Рассчитываю медианы по формуле

1/2 * КОРЕНЬ(2 * (СТЕПЕНЬ (B3; 2) + СТЕПЕНЬ (B4; 2)) – СТЕПЕНЬ (B2; 2));
1/2 * КОРЕНЬ(2 * (СТЕПЕНЬ (B2; 2) + СТЕПЕНЬ (B4; 2)) – СТЕПЕНЬ (B3; 2));
1/2 * КОРЕНЬ(2 * (СТЕПЕНЬ (B2; 2) + СТЕПЕНЬ (B3; 2)) – СТЕПЕНЬ (B4; 2)).

5. Рассчитываю биссектрисы по формуле

2/(B3 + B4) * КОРЕНЬ (B3 * B4 * B7 * (B7 – B2));
2/(B2 + B4) * КОРЕНЬ (B2 * B4 * B7 * (B7 – B3));
2/(B2 + B3) * КОРЕНЬ (B2 * B3 * B7 * (B7 – B4)).

6. Рассчитываю радиус вписанной окружности по формуле B8 / B7.

7. Рассчитываю радиус описанной окружности по формуле (B2 * B3 * B4) / (4 * B8).

Задача 5

Два одинаково заряженных шарика ( один с зарядом 15 мкКл, другой – с 25 мкКл) приводят в соприкосновение и вновь разносятся на расстояние 5 см. Определите заряд каждого шарика после соприкосновения и силу их взаимодействия.

Дано:		CИ
q1	15 мкКл	0,000015	Величину заряда в системе СИ нахожу по формуле:			(15 * СТЕПЕНЬ (10; –6))
q2	25 мкКл	0,000025	Величину заряда в системе СИ нахожу по формуле:			(25 * СТЕПЕНЬ (10; –6))
r	5 см	0,05
k	9000000000
q'1
q'2
F
Решение:
q'1 = q'2	0,00002	Число q'1 = q'2 нахожу по формуле: (0,000015 + 0,000025) / 2
F	1440	Число F нахожу по формуле: 9000000000 * ((0,00002 * 0,00002)/СТЕПЕНЬ(0,05; 2))

ГРАФИКИ И ДИАГРАММЫ

Задача 6

Дано: площадь крупнейших государств

Россия	22,4
США	9,4
Индия	3,3
Китай	9,6
Бразилия	8,5

Построить диаграмму по данным таблицы.

Решение:

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

Задача 7

Дано: рост населения с 1650 по 1960 гг.

1650 год	545
1750 год	788
1850 год	1181
1950 год	2517
1960 год	2995

Построить диаграмму по данным таблицы.

Решение:

1. По данным значения таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Коническая, вид Гистограмма со столбцами в виде конусов.

Задача 8

Дано: численность населения млн. чел.

Часть света	Численность населения млн.чел.	Плотность чел/кв.км
Азия	2323	52
Европа	663	63
Америка	560	13
Африка	400	13
Австралия и Океания	21	2

Построить диаграмму по данным таблицы.

Решение:

1. По данным значения таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Круговая, вид Объемный вариант круговой диаграммы.

МАТЕМАТИЧЕСКЕ ФУНКЦИИ, ГРАФИКИ

Задача 9

Построить параболу по данным значениям Х и У.

Значения Х

х

–2,5

–2

–1,5

–1

–0,5

0

0,5

1

1,5

2

2,5

Решение:

1. По данным значениям Х нахожу значения У по формуле У = Х²
Использую Мастер функции fx на панели инструментов Стандартная, категорию Математические, функцию СТЕПЕНЬ.

2. По данным значениям таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Точечная, вид Точечная диаграмма со значениями, соединенными сглаживающими линиями.

x	y
–2,5	6,25
–2	4
–1,5	2,25
–1	1
–0,5	0,25
0	0
0,5	0,25
1	1
1,5	2,25
2	4
2,5	6,25

Задача 10

Построить кубическую параболу по данным значениям Х и У.

Значения Х

Х

–2,5

–2

–1,5

–1

–0,5

0

0,5

1

1,5

2

2,5

Решение:

1. По данным значениям Х нахожу значение У по формуле У = Х³
Использую Мастер функции fx на панели инструментов Стандартная, категорию Математические, функцию СТЕПЕНЬ.

2. По данным значениям таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Точечная, вид Точечная диаграмма со значениями, соединенными сглаживающими линиями.

 

x	y
–2,5	–15,625
–2	–8
–1,5	–3,375
–1	–1
–0,5	–0,125
0	0
0,5	0,125
1	1
1,5	3,375
2	8
2,5	15,625

Задача 11

Построить синусоиду по данным значениям Х и У.

Значения Х

x

360

330

300

270

240

210

180

150

120

90

60

30

0

–30

–60

–90

–120

–150

–180

–210

–240

–270

–300

–330

–360

Решение:

1. По данным значениям Х нахожу значения У по формуле У = sin (Х)
Использую Мастер функции fx на панели инструментов Стандартная, категорию Математические, функцию SIN.

2. По данным значениям таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Точечная, вид Точечная диаграмма со значениями, соединенными сглаживающими линиями.

x	y
360	–2,4503E–16
330	–0,5
300	–0,8660254
270	–1
240	–0,8660254
210	–0,5
180	1,22515E–16
150	0,5
120	0,866025404
90	1
60	0,866025404
30	0,5
0	0
–30	–0,5
–60	–0,8660254
–90	–1
–120	–0,8660254
–150	–0,5
–180	–1,2251E–16
–210	0,5
–240	0,866025404
–270	1
–300	0,866025404
–330	0,5
–360	2,4503E–16

Задача 12

Построить косинусоиду по данным значениям Х и У.

Значения Х

x

360

330

300

270

240

210

180

150

120

90

60

30

0

–30

–60

–90

–120

–150

–180

–210

–240

–270

–300

–330

–360

Решение:

1. По данным значениям Х нахожу значения У по формуле У= cos (Х)
Использую Мастер функции fx на панели инструментов Стандартная, категорию Математические, функцию COS.

2. По данным значениям таблицы строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Точечная, вид Точечная диаграмма со значениями, соединенными сглаживающими линиями.

 

x	y
360	1
330	0,866025
300	0,5
270	–1,8E–16
240	–0,5
210	–0,86603
180	–1
150	–0,86603
120	–0,5
90	6,13E–17
60	0,5
30	0,866025
0	1
–30	0,866025
–60	0,5
–90	6,13E–17
–120	–0,5
–150	–0,86603
–180	–1
–210	–0,86603
–240	–0,5
–270	–1,8E–16
–300	0,5
–330	0,866025
–360	1

МАТЕМАТИЧЕСКИЕ ФУНКЦИИ, ЛОГИЧЕСКИЕ ФУНКЦИИ

Задача 13

Дано:

Предварительная ведомость группы Ор-321.

	A	B	C
1	№ п/п	Фамилия, имя, отчество	Оценка
2	1	Аравин	5
3	2	Булыгина	4
4	3	Грачева	3
5	4	Гусев	5
6	5	Козлова	5
7	6	Комратова	5
8	7	Коровкина	4
9	8	Куренков	4
10	9	Лункина	2
11	10	Петров	3
12	11	Салогуб	4
13	12	Тощенко	5
14	13	Хохлова	4

Рассчитать количество оценок “отлично”, “хорошо”, “удовлетворительно”, “неудовлетворительно” и всего.
Решение:

1. По данным значениям рассчитываю количество оценок отлично по формуле ЕСЛИ (C2 = 5; 1; 0). Формулу копирую в диапазон ячеек (D2:D14). Использую Мастер функции на панели инструментов Стандартная, категорию Логические, функцию ЕСЛИ.

2. По данным значений рассчитываю количество оценок хорошо по формуле ЕСЛИ (C2 = 4; 1; 0). Формулу копирую в диапазон ячеек (Е2 : Е14). Использую Мастер функции на панели инструментов Стандартная, категорию Логические, функцию ЕСЛИ.

3. По данным значений рассчитываю количество оценок удовлетворительно по формуле ЕСЛИ (C2 = 3; 1; 0). Формулу копирую в диапазон ячеек (F2 : F14). Использую Мастер функции на панели инструментов Стандартная, категорию Логические, функцию ЕСЛИ.

4. По данным значений рассчитываю количество оценок неудовлетворительно по формуле ЕСЛИ (C2 = 2; 1; 0). Формулу копирую в диапазон ячеек (G2 : G14). Использую Мастер функции на панели инструментов Стандартная, категорию Логические, функцию ЕСЛИ.

5. Присваиваю ячейкам

• в диапазоне (D2:D14) имя ОТЛИЧНО,
• в диапазоне (Е2 : Е14) имя ХОРОШО,
• в диапазоне (F2 : F14) имя УДОВЛЕТВОРИТЕЛЬНО,
• в диапазоне(G2:G14) имя НЕУДОВЛЕТВОРИТЕЛЬНО.

6. Использую Мастер функции, Математические,

• формулу СУММ(ОТЛИЧНО) и рассчитываю количество оценок отлично,
• формулу СУММ(ХОРОШО) и рассчитываю количество оценок хорошо,
• формулу СУММ(УДОВЛЕТВОРИТЕЛЬНО) и рассчитываю количество оценок удовлетворительно, формулу СУММ(неудовлетворительно) и рассчитываю количество оценок неудовлетворительно, формулу СУММ (С16 : С19) и рассчитываю количество оценок ИТОГО.

ФУНКЦИИ ССЫЛКИ И МАССИВЫ, ЛОГИЧЕСКИЕ ФУНКЦИИ, СТАТИСТИЧЕСКИЕ ФУНКЦИИ, ГРАФИКИ

Задача 14

Дано:

1. Успеваемость за первое полугодие (Таблица 1, имя листа Успеваемость).

2. Критерии оценок (Таблица 2, имя листа Критерии).

Таблица 1. Успеваемость

Ячейка	Текст	Ячейка	Текст
А4	Предмет	B4	Балл
А5	Математика	B5	4
А6	Русский язык	B6	4
А7	Литература	B7	5
А8	Программное обеспечение	B8	5
А9	История России	B9	5
А10	Обработка информации	B10	5
А11	География	B11	4
А12	Физика	B12	5
А13	Химия	B13	4
А14	Производственное обучение	B14	5
А15	ОБЖ	B15	5
А16	Физическая культура	B16	4
А17	Информатика	B17	5

Таблица 2. Критерии

Ячейка	Текст	Ячейка	Текст
А1	пробел	B1	Застой
А2	2	B2	Позор
А3	3	B3	Тяп-ляп
А4	4	B4	Взлет
А5	5	B5	Победа

1. Рассчитать средний балл.
2. Показать характеристику успеваемости.
3. Построить диаграмму.

Решение:

1. Переименовала в таблице Критерии столбцы "А" – "БАЛЛ" ,"B" – "ХАРАКТЕРИСТИКА".

2. Ячейки столбца С будут заполняться автоматически, т.к. информация, содержащаяся в ячейках столбца В будет просматриваться Функцией ПРОСМОТР. Для этого я выполнила следующие действия:

а) выделила ячейку С5 и выбрала функцию fx, категорию Ссылки и массивы, функцию ПРОСМОТР;
б) в диалоговом окне Мастер функции выбрала первую строку;
в) в поле Искомое значение записала В5 (это значит, что Excel просмотрит содержимое ячейки);
г) в поле Вектор просмотра на листе Критерии выбираю столбец А (в поле появляется запись "Критерии!Балл");
д) в поле Вектор результата на листе Критерии выбираю столбец В (в поле появляется запись "Критерии!Характеристика");
е) в ячейке С5 появится текст соответствующий баллу (в моем случае Взлет).

3. В ячейку C5 дописываю формулу: ЕСЛИ (B5 = " "; " "; ПРОСМОТР (B5; Балл; Характеристика)).

4. Копирую формулу в диапазон С5:С17.

5. Для подсчета среднего балла оценок в ячейки С2 использую Мастер функции, категорию Статистические, функцию СРЗНАЧ (формула имеет вид СРЗНАЧ (B5 : B17)).

6. Произвела форматирование ячеек для выделения цветом: синим – Взлет, красным – Победа следующим образом:

а) активизировала ячейку С5 и выбрала опцию Условное форматирование в Меню Формат;
б) задала условия форматирования: Условие 1 – значение ––> равно ––> = Взлет ––> Формат ––>Вид (цвет синий);
в) выбрала кнопку "А также" Условие 2 – значение ––>равно ––> = Победа ––> Формат ––>Вид (цвет красный), ОК;
г) форматирование скопировать в диапазон С5 : С17.

7. По данным таблицы диапазона А5 : В17 строю диаграмму.
Использую Мастер диаграмм на панели инструментов Стандартная, тип Широкая гистограмма, вид Объемная гистограмма вдоль оси в виде перпендикулярной плоскости.

МАТЕМАТИЧЕСКИЕ ФУНКЦИИ С СОЗДАНИЕМ АБСОЛЮТНОЙ ССЫЛКИ

Задача 15

Оформить прайс-лист.

Дано:

Наименования товаров, цена, количество проданного товара за 1 день.

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

2. Подсчитать итоговые суммы в рублях и евро.

Решение:

1. Оформила таблицу в диапазоне А1 : С6, ввела в ячейку В9 курс евро как постоянную величину.
2. Подсчитала сумму в рублях по формуле B2 * C2, формулу скопировала в диапазон D2 : D6.
3. Подсчитала сумму в евро с использованием абсолютного адреса D2 / $B$9, скопировала формулу в диапазон Е2 : Е6.
4. Подсчитала итоговые суммы в рублях СУММ (D2 : D6), в евро СУММ (E2 : E6).

ПРАЙС-ЛИСТ