Листу с таблицей присвоить имя Повременная оплата

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

Функции в формулах

1. Открыть новый лист и составить таблицу для расчета зарплаты при сдельно-прогрессивной системе оплаты труда. Столбцы таблицы: Фамилия И.О., Выработка, Стоимость работы,Пенсионный фонд, Налог, К выдаче.

Значения столбца Фамилия И.О. получить из столбцов Фамилия, Имя и Отчество таблицы на листе Расчет зарплаты с помощью операции конкатенации и функции ЛЕВСИМВ.

Столбец Выработка (количество изделий) заполнить произвольными значениями от 8 до 12.

Стоимость работы рассчитывается по следующим правилам: работа в пределах нормы выработки оплачивается по установленным расценкам, работа сверх нормы – по повышенным на 20% расценкам. Норма выработки равна 10 изделиям. Расценки: 1 000 руб. за 1 изделие. При вводе формулы использовать функцию ЕСЛИ.

Замечание: по повышенным расценкам оплачивается только произведенное сверх нормы, например, если произведено 12 изделий, то 10 из них будет оплачено по обычным расценкам и только 2 по повышенным.

Отчисления в пенсионный фонд составляют 1% от стоимости работы.

Налог= (Стоимость работы– Пенсионный фонд – МРОТ) × 13%.

К выдаче = Стоимость работы– Пенсионный фонд – Налог.

Все константы (норму выработки, расценки, процент налога) разместить во второй строке таблицы (под «шапкой»).

2. Добавить итоговую строку, содержащую суммы столбцов Выработка, Стоимость работы, Пенсионный фонд,Налог, К выдаче.

3. Присвоить листу имя Сдельная оплата.

Форматирование таблицы

4. Ячейкам, содержащим денежные величины, назначить денежный формат.

5. Открыть лист Повременная оплата.

Создать стиль, основанный на формате ячейки Оклад (шрифт – Times New Roman 12, полужирный, выравнивание по горизонтали и по вертикали – по центру, фон – светло-серый, рамки). Присвоить стилю имя «Шапка».

Использовать этот стиль для форматирования «шапки» таблицы на листе Сдельная оплата.

6. Отформатировать итоговую строку встроенным стилем «Итог»

7. Оформить таблицу утолщенной рамкой, столбцы разделить тонкими вертикальными линиями; «шапку», итоговую строку и строку с константами отделить тонкими горизонтальными линиями, строки таблицы линиями не разделять.

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

Вложенные функции

1. Открыть новый лист и составить таблицу для расчета стоимости телефонных разговоров. Столбцы таблицы: Номер по порядку, Дата, Время начала разговора, Время окончания разговора, Время разговора, День недели, Час суток, Тариф, Стоимость разговора.

СтолбецДата заполнить произвольно датами одного месяца.

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

Значения остальных столбцов рассчитываются:

- Стоимость разговора равна произведению времени разговора на тариф.

- Время разговора рассчитывается как разность между окончанием и началом разговора в минутах. Для определения нужного компонента времени (минут) использовать функцию МИНУТЫ.

Используется следующая система оплаты (тариф): в выходные дни − 0,15 долл. за 1 минуту разговора, в будни − 0,39 долл. с 8:00 до 21:00 и 0,10 долл. – в остальное время суток.

Для определения дня недели использовать функцию ДЕНЬНЕД.

Для определения часа суток использовать функцию ЧАС. Час суток определять по времени начала разговора.

Добавить итоговую строку, содержащую сумму столбца Стоимость разговора.

Листу с таблицей присвоить имя Телефон.

2. Отформатировать таблицу:

- ячейкам, содержащим денежные величины, назначить денежный формат;

- «шапку» отформатировать с помощью стиля «Шапка», созданного в предыдущей работе;

- итоговую строку отформатировать с помощью встроенного стиля «Итог»;

- расчертить таблицу линиями; внешнюю рамку оформить утолщенной линией; столбцы разделить тонкими черными линиями;

- добавить фон.

Формулы массивов

Массив – это набор элементов, которые могут обрабатываться как единая группа.

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

Массивы не обязательно должны храниться в диапазонах ячеек: их можно вводить непосредственно в формулу

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

Одна формула массива может выдавать один или несколько результатов.

Примеры:

1. Формула массива { = A1 : A3 + B1 : B3 } эквивалентна трем формулам: = A1 + B1, = A2 + B2 и = A3 + B3 ; результатом этой формулы будут три значения.

2. Формула { = СУММ (B2 : D2 * B3 : D3 ) } эквивалентна формуле = B2 * B3 + C2 * C3 + D2 * D3; ее результатом будет одно значение.

Формулы массива заключаются в фигурные скобки. С клавиатуры их вводить не надо: формула будет заключена в скобки автоматически при нажатии комбинации клавиш Ctrl + Shift + Enter.

Чтобы ввести формулу массива, следует:

1. Выделить диапазон ячеек, в который надо ввести формулу массива.

2. Ввести формулу, выделяя интервалы ячеек в качестве ссылок.

3. Нажать комбинацию клавиш Ctrl + Shift + Enter.

Не допускается редактирование (вставка, удаление, перемещение, копирование) ячеек, которые составляют только часть массива.

Массивы констант

Подобно тому, как в обычную формулу вводят значение, в формулу массива можно ввести массив значений, называемый массивом констант. Массивы констант могут содержать числа, текст, логические значения.

Для массива констант установлены следующие правила:

- массив констант заключается в фигурные скобки;

- значения в одной строке разделяются точками с запятыми;

- строки разделяются двоеточиями;

- .текст в массивах заключается в кавычки;

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

Примеры записи массивов констант:

1. Запись { 10 ; 20 ; 30 ; 40 } соответствует массиву

2. Запись { 100 : 200 : 300 } – массиву

3. Запись { 1 ; 2 ; 3 : 4 ; 5 ; 6 } – массиву

4. Формула массива

{ = { A2 : B4 * { 1 ; 2 : 3 ; 4 : 5 ; 6 } } }

эквивалентна следующим формулам:

= A2 * 1 ; = B2 * 2 ;= A3 * 3 ; = B3 * 4 ; = A4 * 5 ; = B4 * 6 .

Чтобы ввести в диапазон ячеек массив констант, надо:

1. Выделить диапазон ячеек, в который надо ввести формулу массива.

2. Ввести массив в соответствии с приведенными выше правилами, начиная со знака «=». В отличие от формул массива, при вводе массива констант фигурные скобки вводятся вручную.

3. Нажать комбинацию клавиш Ctrl + Shift + Enter.

Примеры использования формул массива

Пусть в диапазон ячеек A1:A10 введены числовые данные. Необходимо просуммировать только те ячейки из этого диапазона, значения которых превышают 5.

Для этого надо ввести формулу:

{=СУММ(ЕСЛИ(A1:A10>5;A1:A10))}.

Замечание: фигурные скобки не вводятся вручную, они будут вставлены автоматически после нажатия клавишCtrl + Shift + Enter.

Если значения ячеек, которые необходимо просуммировать, должны находиться в интервале от 3 до 7, формула должна иметь следующий вид:

{=СУММ(ЕСЛИ((A1:A10>3)*(A1:A10<7);A1:A10))}.

Здесь используется операция логического умножения.

Если надо просуммировать ячейки со значениями меньше 2 или больше 6, то следует ввести формулу:

{=СУММ(ЕСЛИ((A1:A10<2)+(A1:A10>6);A1:A10))}.

Здесь используется операция логического сложения.

Замечание: в формулах массива нельзя использовать непосредственно функции И или ИЛИ, поскольку эти функции возвращают отдельный результат, ИСТИНА или ЛОЖЬ, а для функций массива требуется массив результатов.

Использование массивов для решения задач линейной алгебры

Пусть дана система линейных уравнений:

Эту систему в матричном виде можно записать следующим образом:

где

Решением этой системы будет

где – обратная матрица.

Другой способ решения – по формулам Крамера:

где − определитель матрицы – определитель матрицы , получаемой из матрицы заменой -го столбца (т. е. столбца коэффициентов при неизвестном x_i) вектором свободных членов B.

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

Функции для операций с матрицами

Для операций с матрицами Microsoft Excel располагает следующими функциями:

- МОПРЕД – вычисление определителя матрицы;

- МОБР – обращение матрицы;

- МУМНОЖ – перемножение матриц;

- ТРАНСП – транспонирование матрицы.

Функция МОПРЕД возвращает определитель матрицы (матрица хранится в массиве). Синтаксис функции:

МОПРЕД ( массив )

Функция МОБР возвращает обратную матрицу для матрицы, хранящейся в массиве. Синтаксис функции:

МОБР ( массив )

Функция МУМНОЖ возвращает произведение матриц (матрицы хранятся в массивах). Синтаксис функции:

МУМНОЖ ( массив 1; массив 2 )

Результатом является массив с таким же числом строк, как массив 1, и с таким же числом столбцов, как массив 2.

Функция ТРАНСП меняет местами строки и столбцы массива. Синтаксис функции:

ТРАНСП ( массив )

Результатом является массив, представляющий собой транспонированную исходную матрицу.

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

Формулы массива

1. Ввести в ячейки таблицы следующие массивы констант:

{1:2:3:4};

{1;2;3;4};

{1;2;3:4;5;6};

{1,5:0,45:10,05};

{1,5e3;0,5e-2;3,25e2};

{0,5;0,6:0,1;1,3:1,4;0,8}.

2. Записать введенные в диапазоны ячеек данные в виде массивов констант:

0,5

0,2

1,3

1,1

0,1	0,3
0,5	0,8
0,7	0,6

3. Составить формулы массива, эквивалентные следующим формулам:

1) =A1*A2;

=B1*B2;

=C1*C2.

2) =(A8+B8)*(A9+B9)*(A10+B10).

Проверить эквивалентность формул.

4. Составить формулы, эквивалентные следующей формуле массива:

{=A2:C3*{2;3;1:4;2;5}}

Проверить эквивалентность формул.

5. Ввести массив произвольных числовых значений. Выполнить суммирование значений массива с учетом условий:

- простого условия, задаваемого с помощью оператора сравнения (например: >10)4

- сложного условия, когда два простых условия связаны логическим оператором И;

- сложного условия, когда два простых условия связаны логическим оператором ИЛИ.

6. Вычислить определители матриц:

1) ; 2) .

7. Выполнить умножение матриц:

1) ; 2) ; 3) .

8. Найти матрицу, обратную заданной: .

Выполнить произведение исходной матрицы на обратную.

9. Решить системы линейных уравнений двумя способами:

а) используя операции с матрицами;

б) по формулам Крамера:

1) ;

2) ;

3)

Создание диаграмм

Диаграмма – это графическое представление данных рабочего листа. Диаграмма связана с данными рабочего листа: при изменении данных произойдет автоматическое обновление диаграммы.

Значения из ячеек рабочего листа, или точки данных, отображаются на экране в виде линий, столбцов, секторов и других компонентов диаграммы. Точки данных группируются в ряды данных, которые отличаются друг от друга узорами и цветом.

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

Элементы диаграммы

В диаграммах различают следующие элементы (рис. 7).

Ряд данных – строка или столбец, содержащие данные для построения диаграммы. Большинство типов диаграмм могут изображать несколько рядов.

Точка данных – отдельное значение ряда данных (данные, содержащиеся в одной ячейке).

Категории – надписи вдоль горизонтальной оси (числа или текст).

Маркер данных – графическое изображение значения ряда данных (столбик, точка и т. д.).

Подписи данных – численное отображение на диаграмме значения ряда данных.

Легенда – название ряда данных (необходимо при изображении нескольких рядов на одной диаграмме).

Ось значений – вертикальная ось диаграммы.

Ось категорий – горизонтальная ось диаграммы.

Названия осей.

Название диаграммы.

Рис. 7

Область построения – область, ограниченная осями диаграммы (прямоугольник, построенный на осях).

Область диаграммы включает все элементы диаграммы: область построения, оси, легенды, названия осей, название диаграммы.

Выделение блока ячеек для построения диаграммы

Для построения диаграммы необходимо выделить данные на рабочем листе: ряды, категории, легенды.

Ориентация данных может быть горизонтальной или вертикальной.

При горизонтальной ориентации (рис. 8) каждая строка (кроме первой) выделенного блока ячеек принимается за один ряд данных, данные первой строки воспринимаются как имена категорий, а данные первого столбца – как легенды.

При вертикальной ориентации (рис. 9) каждый столбец (кроме первого) выделенного блока ячеек принимается за один ряд данных, данные первого столбца воспринимаются как имена категорий, а данные первой строки – как легенды.

Выделенный блок ячеек должен быть прямоугольным (левая верхняя ячейка тоже должна быть выделена).

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

Способы создания диаграмм

I способ

1. Выделить диапазон данных для построения диаграммы.

2. На ленточной вкладке Вставка в группе Диаграммы щелкнуть по кнопке с нужным типом диаграмм.

3. В открывшемся списке выбрать диаграмму нужного вида.

II способ

1. На ленточной вкладке Вставка в группе Диаграммы щелкнуть по кнопке с нужным типом диаграмм.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. В открывшемся диалоговом окне в поле Диапазон данных для диаграммы указать диапазон ячеек с данными для диаграммы.

4. Если автоматическое определение ориентации данных оказалось ошибочным, щелкнуть по кнопке Строка/столбец для изменения ориентации (правильность определения ориентации можно оценить по виду категорий и легенд).

5. Проверить и при необходимости изменить категории, используя кнопку Изменить в поле Подписи горизонтальной оси (категории).

6. Проверить и при необходимости изменить легенды, используя кнопки Добавить, Изменить, Удалить в поле Элементы легенды (ряды).

7. Щелкнуть по кнопке ОК.

Изменение размеров внедренных диаграмм

Для изменения размера диаграммы нужно выделить ее, затем установить указатель мыши на любой маркер размера (точки на рамке) так, чтобы указатель принял вид двунаправленной стрелки, и перетащить мышью сторону или угол диаграммы.

Точные размеры выделенной диаграммы можно установить в группе Размер на вкладке Работа с диаграммами – Формат.

Перемещение, копирование и удаление внедренных диаграмм

Переместить диаграмму можно одним из следующих способов:

- перетащить мышью в нужное место;

- перетащить в нужное место правой кнопкой мыши, затем в открывшемся контекстном меню выбрать команду Переместить;

- через буфер обмена с помощью процедур вырезания и вставки; эти способом можно переместить диаграмму на другой лист или в другую рабочую книгу.

Скопировать диаграмму можно одним из следующих способов:

- перетащить мышью в нужное место, затем, не отпуская кнопку мыши, нажать клавишу Ctrl, отпустить кнопку мыши, затем – клавишу Ctrl;

- перетащить в нужное место правой кнопкой мыши, затем в открывшемся контекстном меню выбрать команду Копировать;

- через буфер обмена с помощью процедур копирования и вставки; эти способом можно скопировать диаграмму на другой лист или в другую рабочую книгу.

Перемещать и копировать диаграмму в буфер обмена, а также вставлять из буфера обмена можно любым способом: командами группы Буфер обмена, командами контекстного меню, «быстрыми» клавишами.

Чтобы удалить диаграмму, надо выделить ее, затем нажать клавишу Delete или выбрать в меню Правка команду Очистить, вариант Все.

Редактирование диаграмм

Активизация диаграммы

Чтобы внести изменения в диаграмму, ее надо активизировать.

Чтобы активизировать внедренную диаграмму, надо щелкнуть по ней. Для активизации диаграммы на отдельном листе следует перейти к этому листу.

Когда диаграмма активна, в меню появляются некоторые специфические для диаграмм команды; можно выделять компоненты диаграммы и вносить необходимые изменения.

Выделение компонентов диаграммы

Существует три способа выделения компонентов диаграммы.

I способ:с помощью мыши.

При первом щелчке компонент выделяется целиком (рис. 10, слева), после чего можно вносить изменения, затрагивающие компонент в целом.

Например, при щелчке по маркеру данных будет выделен весь ряд данных, и внесенные изменения распространятся на все точки данных этого ряда. После одного щелчка по легенде можно вносить изменения, затрагивающие всю легенду: изменять ее размер, перемещать, добавлять рамку.

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

Рис. 10

II способ:с помощью клавиатуры.

Когда диаграмма активизирована, для перемещения по ее компонентам можно использовать клавиши управления курсором. При каждом нажатии на клавишу будут последовательно выделяться компоненты диаграммы.

III способ:с помощью списка элементов диаграммы в меню.

Список Элементы диаграммы находится на вкладке Работа с диаграммами – Формат в группе Текущий фрагмент. Для выделения элемента диаграммы надо выбрать его из списка. Но в этом списке отображаются имена элементов только верхнего уровня. Для выделения элементов следующих уровней надо использовать другие способы.

При любом способе выделения в списке Элементы диаграммы отображается название выделенного элемента.

Чтобы отменить выделение диаграммы или отдельного ее компонента, надо нажать клавишу Esc.

Изменение значений в диаграмме

При создании диаграммы точки данных автоматически связываются с соответствующими значениями рабочего листа. Поэтому при изменении значений на рабочем листе автоматически изменяются значения в диаграмме.

Изменение блока данных в диаграмме

Изменить координаты блока ячеек, содержащих данные для построения диаграммы, можно несколькими способами.

I способ. Изменение диапазона данных:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

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

II способ. Добавление, изменение и удаление рядов:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. Для добавления ряда в поле Элементы легенды (ряды) щелкнуть по кнопке Добавить и в открывшемся диалоговом окне указать имя ряда и диапазон данных для нового ряда.

Для изменения значений ряда в поле Элементы легенды (ряды) выделить нужный ряд, щелкнуть по кнопке Изменить и в открывшемся диалоговом окне указать новый диапазон данных для ряда.

Для удаления ряда в поле Элементы легенды (ряды) выделить нужный ряд и щелкнуть по кнопке Удалить.

III способ. Добавление и удаление рядов или точек перетаскиванием границ диапазона данных:

Этот метод работает только с внедренными диаграммами.

1. Выделить внедренную диаграмму. При этом будет выделен диапазон данных в таблице.

2. Установить указатель мыши на углу диапазона так, чтобы он принял вид двунаправленной стрелки и перетащить его, изменяя диапазон данных.

IV способ. Добавление рядов и точек через механизм копирования:

1. Выделить диапазоны ячеек с добавляемыми данными.

2. Скопировать данные в буфер обмена (любым способом).

3. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

4. Выполнить вставку данных из буфера обмена (любым способом).

V способ. Удаление ряда:

1. Выделить на диаграмме ряд данных, который нужно удалить.

2. В контекстном меню выделенного ряда выбрать команду Удалить или нажать клавишу Del.

Изменение порядка построения рядов данных

Чтобы переупорядочить ряды данных в диаграмме, следует:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. В поле Элементы легенды (ряды) выделить ряд и передвинуть его с помощью кнопок Вверх и Вниз

Изменение стандартных текстов диаграммы

К стандартным текстам диаграммы относятся подписи делений на оси категорий, имена рядов данных, текст легенды, подписи данных, заголовки осей и диаграммы.

Для изменения подписей делений по оси категорий надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. В открывшемся диалоговом окне в поле Подписи горизонтальной оси щелкнуть по кнопке Изменить.

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

Чтобы добавить, удалить или изменить размещение легенды, надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Макет в группе Подписи открыть список Легенда.

3. В открывшемся списке выбрать нужный вариант размещения легенды. Для удаления легенды выбрать вариант Нет.

Чтобы изменить текст легенды, надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. В открывшемся диалоговом окне в поле Элементы легенды (ряды) выделить имя ряда, которое необходимо изменить, и щелкнуть по кнопке Изменить.

4. В открывшемся диалоговом окне ввести имя ряда или указать ссылку на ячейку с именем.

Для добавления в диаграмму подписей данных надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Макет в группе Подписи открыть список Подписи данных.

3. В открывшемся списке выбрать нужный вариант размещения подписей данных. Для удаления подписей данных выбрать вариант Нет.

Для добавления в диаграмму названий осей надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Макет в группе Подписи открыть список Подписи осей.

3. В открывшемся списке выбрать строку Название основной горизонтальной оси.

4. В открывшемся списке выбрать строку Название под осью (для удаления названия выбрать вариант Нет).

5. Ввести название оси, нажать Enter.

6. В списке Подписи осей выбрать строку Название основной вертикальной оси.

7. В открывшемся списке выбрать строку вариант положения названия (для удаления названия выбрать вариант Нет).

8. Ввести название оси, нажать Enter.

Чтобы добавить название диаграммы надо:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Макет в группе Подписи открыть список Название диаграммы.

3. В открывшемся списке выбрать вариант размещения названия(для удаления названия выбрать вариант Нет).

4. Ввести название диаграммы, нажать Enter.

Большая часть этих текстов (за исключением заголовков осей и диаграммы) связана с ячейками рабочего листа. Если изменить текст в ячейках, то автоматически изменится и соответствующий текст на диаграмме.

Изменение размещения диаграммы

Чтобы изменить размещение диаграммы, следует:

1. Выделить внедренную диаграмму или перейти к листу с нужной диаграммой.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Расположение щелкнуть по кнопке Переместить диаграмму.

3. В открывшемся диалоговом окне выбрать нужный вариант размещения диаграммы:

- для перемещения внедренной диаграммы на отдельный лист включить переключатель на отдельном листе и ввести в поле имя листа;

- для перемещения диаграммы с отдельного листа на лист с данными включить переключатель на имеющемся листе и выбрать имя листа из списка.

Форматирование диаграмм

Типы диаграмм

В Microsoft Excel 2007 предлагается выбор из 11 типов диаграмм:

- в гистограмме каждая точка данных представляется в виде вертикального столбца, высота которого соответствует значению этой точки; вертикальная ось является осью значений, горизонтальная – осью категорий;

- в графике значения представляются точками, соединенными линиями;

- в круговой диаграмме каждая точка ряда представлена сектором круга, площадь которого пропорциональна значению этой точки; строится только для одного ряда данных;

- линейчатая диаграмма – это гистограмма, у которой вертикальная ось является осью категорий, а горизонтальная – осью значений;

- точечная диаграмма имеет две оси значений: каждая пара значений интерпретируется как координаты точки диаграммы;

- диаграмма с областями – график, в котором вся область под линией графика закрашена;

- в кольцевой диаграмме каждая точка ряда представлена сектором кольца, площадь которого пропорциональна значению этой точки; в отличие от круговой диаграммы может быть построена для нескольких рядов данных: в этом случае ряды представляются в виде концентрических колец;

- лепестковая диаграмма представляет значения каждой категории вдоль отдельной оси, которая начинается в центре диаграммы и заканчивается на внешнем кольце;

- на поверхностной диаграмме показана объемная трехмерная поверхность, которая соединяет точки данных, причем области одного диапазона значений на этой поверхности выделены одинаковым цветом; в отличие от диаграмм других типов цвет используется не для выделения рядов, а для выделения значений;

- пузырьковая диаграмма является разновидностью точечной диаграммы, в которой точки заменены пузырьками, причем их размер служит дополнительным измерением данных;

- биржевая диаграмма чаще всего используется для движения цен на бирже.

У каждого типа диаграмм есть несколько подтипов.

Тип диаграммы выбирается при ее создании.

Чтобы изменить тип диаграммы, надо активизировать ее, затем н ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Типщелкнуть по кнопке Изменить тип диаграммы и в открывшемся диалоговом окне выбрать нужный тип и подтип. Другой способ – щелкнуть по кнопке Изменить тип диаграммы в контекстном меню диаграммы.

Если перед применением команды Тип диаграммы выделить ряд данных, то тип будет изменен только для этого ряда. Таким образом, в одной диаграмме будут сочетаться несколько типов.

Изменение положения и размеров элементов диаграммы

Положение элементов диаграммы можно изменить, выделяя и перетаскивая их мышью.

Размеры элементов диаграммы можно изменить, выделяя их и перетаскивая мышью маркеры выделения.

Форматирование компонентов диаграммы

Microsoft Excel позволяет выполнять следующие операции по форматированию компонентов диаграммы:

- применять цвета и узоры к маркерам данных (столбцам, линиям, секторам);

- изменять форму маркеров в графиках;

- создавать рамки вокруг легенд, названий и текстовых полей, а также применять цвет к области внутри рамки;

- изменять шрифт, размер, стиль и выравнивание текстов;

- форматировать числа в метках данных, засечках и категориях;

- изменять стиль линий и цвет сетки.

Для выполнения этих операций с выделенным компонентом диаграммы имеются следующие средства:

- диалоговое окноФормат выделенного фрагмента, которое открывается соответствующей кнопкой, расположенной в группе Текущий фрагмент на ленточной вкладке Работа с диаграммами – Формат;

- команды группы Стили фигур на ленточной вкладке Работа с диаграммами – Формат;

- команды групп Шрифт и Выравнивание ленточной вкладки Главная;

- команды контекстного меню выделенного фрагмента;

- кнопки мини-панелей.

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

Построение диаграмм

1. По данным листа Расчет зарплаты построить гистограмму: в качестве рядов использовать данные из столбцов Оклад, Пенсионный фонд и Налог, в качестве категорий – данные из столбца Фамилия. Тип – гистограмма. Размещение – на отдельном листе. Листу с диаграммой присвоить имя Диаграмма зарплаты.

2. По данным листа Повременная оплата построить гистограмму с накоплением: в качестве рядов использовать данные из столбцов Оклади Премия, в качестве категорий – данные из столбца Фамилия имя отчество. Размещение – на отдельном листе. Листу с диаграммой присвоить имя Диаграмма с накоплением.

3. По данным листа Повременная оплата построить внедренную круговую диаграмму: в качестве категорий использовать данные из «шапки» таблицы (ячейки Оклад, Премия, Пенсионный фонд, Налог), в качестве рядов – соответствующие им ячейки итоговой строки.

4. По данным листа Сдельная оплата построить график с маркерами: в качестве рядов использовать данные из столбцов Стоимость работы, Пенсионный фонди Налог, в качестве категорий – данные из столбца Фамилия И.О.. Размещение – на отдельном листе. Листу с диаграммой присвоить имя График зарплаты.

5. По данным листа Сдельная оплата построить внедренную кольцевую диаграмму: в качестве рядов использовать данные из столбцов Стоимость работы, Пенсионный фонди Налог, в качестве категорий – данные из столбца Фамилия И.О..

6. Построить архимедову спираль:

Для этого на новом листе ввести таблицу с данными для диаграммы. Столбцы таблицы:n, , x, y.

Столбец n заполнить целыми числами по порядку от 0 до 96.

Остальные столбцы рассчитываются по формулам:

Для получения числа воспользоваться встроенной функцией ПИ().

Построить внедренную точечную диаграмму по данным столбцов x и y.

Присвоить листу имя Кривые.

7. Построить улитку Паскаля:

Для этого на листе Кривые ввести таблицу с данными для диаграммы. Столбцы таблицы:n, , x, y.

Столбец n заполнить целыми числами по порядку от 0 до 24.

Остальные столбцы рассчитываются по формулам:

Построить внедренную точечную диаграмму по данным столбцов x и y.

8. Построить внедренную поверхностную диаграмму для функции двух переменных . Для этого воспользоваться данными из таблицы значений соответствующей функции на листе Смешанные ссылки.

9. Построить гиперболический параболоид

.

Для этого воспользоваться данными из таблицы значений соответствующей функции на листе Смешанные ссылки.

Редактирование диаграмм

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

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

12. В таблицу на листе Сдельная оплата добавить две новые строки и заполнить их данными. Для этого перед итоговой строкой вставить две пустые строки, скопировать в них формулу из столбца Фамилия И.О., ввести новые данные в столбец Выработка (остальные формулы скопируются автоматически). В график на листе График зарплаты добавить новые данные через механизм копирования.

13. Открыть лист Кривые. Добавить к архимедовой спирали еще один виток. Для этого столбец n «растянуть» до n=120 и скопировать в новые строки формулы остальных столбцов. Изменить график методом перетаскивания границ диапазона.

14. Во все диаграммы ввести стандартные тексты: подписи на оси категорий, легенды, подписи данных, названия осей и диаграммы.

15. Переместить гиперболический параболоид с листа Смешанные ссылки на отдельный лист. Листу присвоить имя Гиперболический параболоид.

Форматирование диаграмм

16. Научиться изменять положение и размеры внедренных диаграмм.

17. Для диаграммы на листе Гиперболический параболоид выбрать подтип Проволочная поверхность.

18. Для кольцевой диаграммы на листе Сдельная оплата выбрать подтип Разрезанная кольцевая.

19. Освоить следующие операции по форматированию элементов диаграмм:

- изменение цветов и узоров маркеров данных (столбцов, линий, секторов);

- изменение формы маркеров в графиках;

- изменение шрифта, размера, стиля и выравнивания текстов;

- форматирование чисел в метках данных, засечках и категориях;

- создание рамок вокруг заголовков, названий осей и легенд;

- изменение стиля линий и цвета сетки.

Анализ данных

Подбор параметра

Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.

Математическая суть задачи состоит в решении уравнения f(x)=a, где функция f(x) описывается заданной формулой, x – искомый параметр, a – требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.

2. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Подбор параметра.

3. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).

4. В поле Значение ввести значение, которое нужно получить по заданной формуле.

5. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).

6. Щелкнуть по кнопке ОК.

После выполнения команды в изменяемой ячейке появится значение параметра, при котором результат формулы равняется заданной величине. При этом будет пересчитана вся таблица, т.е. изменятся значения, прямо или косвенно зависящие от изменяемого параметра.

Таблица подстановки данных

Пусть имеется формула, которая зависит от некоторых переменных. Задача состоит в определении результатов формулы при различных значениях этих переменных.

Математическая сущность задачи – табулирование функции.

Эта задача – обратная задаче подбора параметров.

Анализ выполняется при помощи таблицы подстановки данных.

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

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

Таблица подстановки с одной переменной

Анализ формулы начинается с подготовки таблицы подстановки:

1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.

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

3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.

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

4. Выделить таблицу подстановки (в ячейки, расположенные рядом с таблицей, можно ввести пояснительные надписи, но эти ячейки не входят в таблицу подстановки данных и, следовательно, не выделяются).

5. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Таблица данных.

6. Если значения варьируемой переменной расположены в столбце, то надо щелкнуть по полю Подставлять значения по строкам в и ввести в это поле адрес изменяемой ячейки (т. е. ячейки, которая играет роль варьируемой переменной в формуле).

Если значения варьируемой переменной расположены в строке, то адрес изменяемой ячейки вводится в поле Подставлять значения по столбцам.

7. Щелкнуть по кнопке ОК.

Таблица будет заполнена значениями.

Таблица подстановки с двумя переменными

В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:

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

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

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

4. Выделить таблицу подстановки.

5. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Таблица данных.

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

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

8. Щелкнуть по кнопке ОК.

Таблица будет заполнена значениями.

Диспетчер сценариев

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

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

Создание сценария

Чтобы создать сценарий, следует:

1. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.

2. В открывшемся диалоговом окне щелкнуть по кнопке Добавить. Откроется окно Добавление сценария.

3. В поле Названиесценария ввести имя сценария.

4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише Ctrl.

5. Щелкнуть по кнопке ОК.

6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки.

7. Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3−6.

Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.

Рекомендуется сохранить в качестве сценария первоначальные значения изменяемых ячеек, чтобы потом можно было быстро восстановить эти значения.

Просмотр сценария

Для просмотра сценария нужно:

1. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.

2. В поле Сценарии открывшегося диалогового окна выделить имя сценария, который необходимо просмотреть.

3. Щелкнуть по кнопке Вывести.

Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.

Редактирование сценария

Чтобы отредактировать сценарий, надо:

1. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.

2. В поле Сценарии выделить имя сценария, который необходимо отредактировать.

3. Щелкнуть по кнопке Изменить.

4. Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.

5. Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.

Создание итогового отчета по сценариям

Для создания итогового отчета по сценариям следует:

1. На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.

2. В открывшемся диалоговом окне щелкнуть по кнопке Отчет.

3. Выбрать тип отчета: Структура или Сводная таблица.

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

Отчет типа Сводная таблица предоставляет возможность эмпирического анализа сценариев. Этот тип отчета полезен тогда, когда сценарий имеет несколько наборов значений изменяющихся ячеек, заданных различными пользователями. С помощью сводных таблиц можно выполнить анализ для разных комбинаций сценариев.

4. В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише Ctrl. Итоговые отчеты создаются на отдельных листах.

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

Подбор параметра

1. В таблице на листе Повременная оплатаопределить:

- каков должен быть процент премии, чтобы сумма значений столбца Премия была равна заданной величине;

- каков должен быть процент налога, чтобы сумма значений столбца Налог была равна заданной величине;

- каков должен быть МРОТ, чтобы сумма значений столбца К выдаче была равна заданной величине.

2. В таблице на листе Сдельная оплата определить:

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

- каков должен быть процент повышения расценки за превышение нормы выработки, чтобы сумма значений столбца К выдаче была равна заданной величине.

3. В таблице на листе Телефон 1определить:

- каков должен быть тариф будней в дневное время, чтобы сумма стоимостей разговоров всех клиентов была равна заданному значению;

- каков должен быть тариф выходных дней, чтобы сумма стоимостей разговоров всех клиентов была равна заданному значению;

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

4. Открыть новый лист и присвоить ему имя Анализ данных. С помощью инструмента Подбор параметра решить уравнения:

1) 2 x – ln x – 4 = 0 (Ответ: x » 2,45)

2) 4 x = cos x (Ответ: x » 0,24)

3) x³ – 5 x + 0,1 = 0 (Ответ: x » 0,02)

Таблица подстановки данных

5. На листеРасчет зарплаты построить таблицу зависимости суммы значений столбца Налог от процента налога (подставить значения 11%, 13% и 15%).

6. На листеПовременная оплата построить таблицу зависимости суммы значений столбца Налог от процента премии (7,10 и 15%).

7. На листе Повременная оплата построить таблицу зависимости суммы значений столбца К выдаче от процента премии (7,10 и 15%) и процента налога (11%, 13% и 15%).

8. На листеСдельная оплатапостроить таблицу зависимости суммарного значения столбца Стоимость работы от нормы выработки (9, 10, 11 изд.).

9. На листеСдельная оплатапостроить таблицу зависимости суммарного значения столбца К выдаче от расценки (900, 1 000 и 1 200 руб.) и от процента повышения расценки за превышение нормы (15, 20 и 25%).

10. На листеТелефонпостроить таблицы зависимости суммарной стоимости разговоров от:

- тарифа выходных дней (подставить значения 0,11; 0,13; 0,15; 0,17; 0,19 долл.);

- тарифов будней в дневное и ночное время (соответственно 0,35; 0,37; 0,39; 0,41; 0,43 и 0,08; 0,09; 0,10; 0,11; 0,12 долл.);

- верхней границы ночного и дневного времени (7; 8; 9);

- верхней и нижней границ ночного и дневного времени (соответственно 7; 8; 9 и 20; 21).

11. На листеАнализ данных с помощью инструмента Таблица подстановки построить таблицу значений функции для x от 0 до 2,0 с шагом 0,1.

12. На листеАнализ данных с помощью инструмента Таблица подстановки построить таблицу умножения как таблицу значений функции двух переменных при изменении обеих переменных от 1 до 10 с шагом 1.

Сценарии

13. На листе Повременная оплата присвоить имена ячейкам, содержащим значения процента премии, процента налога, процента отчислений в пенсионный фонд, а также ячейкам, содержащим суммарные значения столбцов Премия, Налог и К выдаче.

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

- в первом сценарии сохранить первоначальные значения изменяемых ячеек;

- во втором сценарии: процент премии – 15%, процент налога – 15%;

- в третьем сценарии: процент премии – 5%, процент налога – 10%, процент отчислений в пенсионный фонд – 1,5%.

Создать итоговый отчет типа Структура.

14. На листе Сдельная оплата присвоить имена ячейкам, содержащим значения нормы выработки, расценки и процент повышения расценки, а также ячейкам, содержащим суммарные значения стоимости работы и итоговых выплат.

С помощью Диспетчера сценариев проанализировать, как зависят суммарные значения стоимости работы и итоговых выплат от нормы выработки, расценки и процента повышения расценки. Для этого создать три сценария, в которых задать следующие значения изменяемых ячеек:

- в первом сценарии сохранить первоначальные значения изменяемых ячеек;

- во втором сценарии: норма выработки – 12, расценка – 1200 руб. за одно изделие;

- в третьем сценарии: норма выработки – 12, расценка – 1500 руб. за одно изделие; процент повышения расценки – 15%.

Создать итоговый отчет типа Структура.

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

С помощью Диспетчера сценариев проанализировать, как зависит суммарная стоимость разговоров от тарифов. Для этого создать четыре сценария, в которых задать следующие значения изменяемых ячеек:

- в первом сценарии: тариф выходных дней 0,20 долл.;

- во втором сценарии: тарифы будней в дневное время 0,40 долл. и в ночное время 0,15 долл.;

- в третьем сценарии: тарифы выходных дней 0,20 долл., будней в дневное время 0,30 долл. и в ночное время 0,12 долл.;

- в четвертом сценарии: тарифы выходных дней 0,12 долл., будней в дневное время 0,44 долл. и в ночное время 0,12 долл.

Создать итоговый отчет типа Структура.