Тема 2.2 Адресация данных. Работа с функциями и формулами. Мегаформулы. Построение диаграмм на основе полученных данных.

Практическая работа №6

Адресация данных по имени. Манипулирование данными при помощи расширенного списка. Случайная группировка и сортировка данных.

Задание 1 При создании формулы, ссылающейся на данные из листа, использовал заголовки строк и столбцов для указания данных. Следующий пример (см. рисунок 6.1) содержит столбцы, помеченные «Изделие 1», «Изделие 2», «Изделие 3». Для подсчета общей суммы для столбца «Изделие 1» использовал формулу =СУММ(Изделие 1).

Рисунок 6.1 – Применение Адресации ячеек.

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

Рисунок 6 .1-2 - Заполнение таблицы с использванием функций и имен.

Рисунок 6.1-3 - Формула для суммы налога, лицам с даходами меньше 7000 рублей

Задание 2 Создал ниже представленную таблицу. Использовал расширенный фильтр (Данные Фильтр Расширенный фильтр…) с оператором ИЛИ для отображения тех сотрудников:

1. чья сумма за заказ составляет более 500 долларов ИЛИ менее 400 долларов(см.рис 6.2-1);

2. чья фамилия Иванов и Петров и дата заказа более 16.07.2009 ИЛИ менее 03.09.2009(см.рис 6.2-2);

3. чья сумма за заказ составляет более 500 долларов ИЛИ менее 400 долларов, чья фамилия Иванов и Петров(см.рис 6.2-3);

4. чья фамилия начинается на букву И(см.рис 6.2-4);.

Рисунок 6.2.1 - Сумма за заказ составляет более 500 долларов ИЛИ менее 400 долларов

Рисунок 6.2.2 - Фамилия Иванов и Петров и дата заказа более 16.07.2009 ИЛИ менее 03.09.2009

Рисунок 6.2.3 - Сумма за заказ составляет более 500 долларов ИЛИ менее 400 долларов, чья фамилия Иванов и Петров

Рисунок 6.2.4 - Чья фамилия начинается на букву И.

Для применения инструмента Excel Расширенный фильтр мне понадобилась копия заголовков столбцов таблицы выше основных данных.

Практическая работа №7

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

Задание 1.

1. Создал арифметическую прогрессию¹ вида: 1, 7, 13, 19,…,313(см.рис 7.1-1).

Рисунок 7.1.1 - Работа с арефмитической прогрессией.

2. Создали геометрическую прогрессию вида: (см.рис7.1-2).

Рисунок 7.1.2 - Работа с геометрической прогрессией.

Рисунок 7.1.3 -Результат работы.

Задание 2.

1. Используя прогрессию и смешанные ссылки, построил таблицу значений функции 2^х переменных f(x,y) в виде:

2. Функцию, границы и шаг изменил аргументы взяли из таблицы вариантов. Границы изменения по X и по Y совпадают(см.7.2).

Рисунок 7.2 – Организация информации в MS Excel по заданию.

Практическая работа №8

Решение задач с использованием формул и встроенных функций.

Задание 1 Создали таблицу своих расходов в течение недели, аналогичную таблице на рисунке(см рис 8.1).

Рисунок 8.1- Формула для заполнения таблицы

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

2. Отформатировал таблицу по собственному усмотрению, используя различные цвета заливки, границ, шрифта.

Задание 2 Создал таблицу Расчёта задолженности ООО «Север» по состоянию на 1.07.03 заданного вида(см. рис 8.2).

1. Вычислил:

• кол-во дней (=1.07.03 - Просрочка(04.02.03));

• 2% от платежа (СУММА*2%);

• размер пени (СУММА*КОЛ-ВО ДНЕЙ *2%);

• всего (СУММА +размер пени + 2% от платежа);

2. Ввел формулы в первую строку и скопировал по столбцам.

3. Затем подсчитал (Автосуммирование):

• долг по арендной плате;

• долг по коммунальным платежам;

• общий размер задолженности.

4. В конце таблицы вывел дату и время окончания работы. При этом посмотрел все возможные варианты их представления в Excel.

5. Полученные результаты сохранил в файле с зашитой структуры книги.

Рисунок 8.2 – Исходные данные для расчёта задолженности ООО «Север».

Задание 3 Создал таблицу расчёта заработной платы по образцу, представленному ниже. Форматирование таблицы (ячеек) определил самостоятельно(смю рис 8.3).

Рисунок 8.3- Таблица расчёта заработной платы

Задание 4 Организовал ввод данных как представлено ниже(см. рис 8.4).:

Рисунок 8.4 - Огранизация матриц, обратная матрица

1. Вычислил произведение матриц А*В и В*А².

2. Вычислил обратную к приведенной ниже матрице C³.

Задание 5. Нашёл решение(см. рис 8.5)..

Рисунок 8.5-Организация матриц для нахождения линейных уравнений

Задание 6. В ячейки А1, В1 и С1 ввел произвольные числа. При помощи логических функций в ячейке D1 вывел максимальное из этих трёх чисел. Формула универсальна, т.е. при изменении содержимого трёх ячеек, в ячейке D1 всё равно выводиться максимальное из них(см. рис 8.6)..

Рисунок 8.6-Нахождение максимального числа

Задание 7. В ячейках А1 и В1 ввел произвольные числа. В ячейку С1 ввел формулу, рассчитывающую: если два числа кратны пяти, рассчитал сумму двух чисел, если хотя бы одно число кратно пяти то рассчитывал разность этих чисел, если оба числа не кратны, то вывел – “не то, не другое”(см. рис 8.7)..

Рисунок 8.7 - Организация чисел по формуле

Задание 8. Вычислил(см. рис 8.8).:

.

Рисунок 8.8 - Нахождение уравнения

Задание 9.Вывел случайный⁴ номер билета, представил таблицу данных вида(см. рис 8.10).:

Рисунок 8.9 - Таблица данных

Задание 10. Определил возможное число комбинаций⁵ из 3 карт в колоде, содержащей 52 карты. Для решения поставленной задачи использовал функции случайных чисел.

Рисунок 8.10 - Использование функции

Практическая работа №9

Преобразование формул и функций MS Excel в значения. Вычисление текстового выражения. Работа с датой и временем в MS Excel.

Задание 1. Организовал данные следующим образом(см. рис 9.1):

Рисунок 9.1-Организация данных

В данном примере подсчитал количество символов в строке⁶, если их больше 15, иначе вывел сообщение “В данной строке меньше 15 символов”.

Задание 2. Создал следующий фрагмент таблицы(см. рис 9.2):

Рисунок 9.2- Организация данных

1. В данном задании получил конечный вариант ответа в одной ячейке виде одной строки, состоящей из Фамилии Имени Отчества, с использованием текстовых функций⁷ и относительных ссылок.

2. Изменил адреса ячеек на абсолютные ссылки. Проследил, как меняются аргументы и внесли все наблюдения в отчёт.

Задание 3. Подсчитал количество учащихся группы⁸, родившихся в апреле, используя ниже представленную таблицу. Для подсчёта количества учащихся, использовал формулу массива. (см. рис 9.3).

Рисунок 9.3 - Количество учащихся группы, родившихся в апреле.

Практическая работа №10

Построение мегаформул. Трюки с мегаформулами.

Задание 1. Создал на листе ниже представленную таблицу.

На втором листе, использовал данные из первой таблицы, относительные ссылки и формулы⁹, организовал таблицу вида:

Рисунок 10-Организация данных

Практическая работа №11

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

Задание 1. Используя необходимые функции, построил следующие графики:

График А

, в интервале [- 2П: 2П], с шагом 0,1. (см. рисунок11.1.1)

Рисунок 11.1.1-Построение графика

График B

в интервале [-2: 3,5], с шагом 0,05. (см. рисунок11.1.2)

Рисунок 11.1.2-Построение диаграммы

График C

в интервале [- 10:10], с шагом 0,2. (см. рисунок11.1.3)

Рисунок 11.1.3-Построение диаграммы

Задание 2. Построил график функции, границы и шаг изменения аргументов взял из таблицы вариантов. Границы изменения по X и по Y совпадают. Форматирование созданных листов определил сам. Указал для графиков название осей. В каждом из графиков установил самостоятельно форматирование(см. рисунок 11.2).

Рисунок 11.2-Построение диаграммы

Задание 3. Создал новую книгу, состоящую из 2 листов (см. рисунок 11.3.1)

Рисунок 11.3.1 – Пример организации данных

Лист 2 – Стипендия. Создал таблицу

Рисунок 11.3.2 - Организация данных

Смешенная диаграмма.

Заполнил электронную таблицу, определяющую стипендию согласно правилу:

1. Для вывода столбца «Фамилия» использовал данные листа «Успеваемость»

2. Столбец «Инициалы» записывал при помощи функций СЦЕПИТЬ и ЛЕВСИМВ, использовал соответствующие столбцы листа «Успеваемость»

3. Столбец «Стипендия» начисляется студентам, обучающимся на бюджетной основе и если средний балл по успеваемости больше или равен «3»;

4. Столбец «Надбавка к стипендии» начисляется студентам, обучающимся на бюджетной основе и имеющие средний балл более или равен «4» в виде 50% от стипендии.

Построил объединённую диаграмму: Гистограмму, отражающую размер стипендии каждого студента + График, отражающий средний балл студента (см. рисунок 11.3.2).