Сочетание клави ш при работе с формулами

Содержание

Введение

3

Знаки операции в электронных таблицах

3

Работа с формулами

4

Окно MS Excel 2007

6

Копирование и перемещение элементов электронной таблицы

7

Добавление и удаление элементов электронной таблицы

7

Вставка Дата/время

8

Форматирование таблиц в MS Excel 2007

8

Формат ячеек

10

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

10

Ошибки в формулах

12

Excel справочник

14

Практическая работа № 1 «Начальные навыки работы в MS Excel 2007»

17

Практическая работа № 2 «Основные понятия Excel. Создание и форматирование таблиц»

20

Практическая работа № 3 «Организация расчетов в табличном процессоре Excel 2007»

30

Практическая работа № 4 «Создание электронной книги. Абсолютная и относительная адресация»

47

Практическая работа № 5 «Связанные таблицы. Расчет промежуточных итогов».

52

Практическая работа № 6 «Подбор параметра. Организация обратного расчета»

62

Практическая работа № 7 «Создание учебных тестов в MS Excel 2007»

69

Практическая работа № 8 «Связи между файлами и консолидация данных в MS Excel 2007»

72

Практическая работа № 9 «Составление отчета. Составление итоговых отчетов. Составление консолидированных отчетов»

79

Контрольная работа по теме «Табель успеваемости по учебной дисциплине»

86

Контрольные вопросы

100

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

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

Каждая рабочая книга в Excel состоит из рабочих листов. Лист состоит из ячеек, образующих строки и столбцы. На одном листе может содержаться 1 048 576 строк и 16 384 столбцов.

Ячейка - место пересечения столбца и строки.

Адрес ячейки электронной таблицы составляется из заголовка столбца и заголовка строки, например, Al, F12. Ячейка, с которой производятся какие-то работы (или могут производиться в настоящий момент), обычно выделена рамкой и называется активной.

Формула может содержать следующие элементы: операторы, ссылки на ячейки, значения, функции, имена и должна начинаться со знака равенства (=). Например, «=А3*В8+18» (кавычки не вводятся. Здесь и далее они для выделения формулы). Вводится формула на английском языке.

Знаки операций:

+ сложение

* умножение

- вычитание

/ деление

^ возведение в степень.

 

Вся формула пишется в одну строку и обязательно должна начинаться со знака «=».

Например: =2*21+3*32 =(55-1)^2

Сочетание клави ш при работе с формулами

 

Копирование и перемещение

Чтобы скопировать данные из ячейки/строки/столбца, нужно выделить необходимый элемент и по контекстному меню по нажатию правой кнопки мыши выбрать пункт Копировать, затем Вставить, переместив курсор и выделив нужное для вставки место. Также можно воспользоваться сочетаниями клавиш Ctrl+Insert или Ctrl+C (для копирования) и Shift+Insert или Ctrl+V (для вставки), либо с помощью левой кнопки мыши с нажатой одновременно клавишей Ctrl «перетащить» элемент в нужное место для получения там его копии, либо воспользоваться соответствующими кнопками на панели Буфер обмена вкладки Главная.

Чтобы переместить данные из ячейки/строки/столбца, нужно выделить необходимый элемент и по контекстному меню по нажатию правой кнопки мыши выбрать пункт Вырезать, затем Вставит ь, переместив курсор и выделив нужное для вставки место. Также можно воспользоваться сочетаниями клавиш Shift+Delete или Ctrl+X (для вырезания) и Shift+Inser t или Ctrl+V (для вставки), либо просто перетащить на новое место элемент левой кнопкой мыши, либо воспользоваться соответствующими кнопками на панели Буфер обмена вкладки Главная.

Добавление и удаление

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

Чтобы добавить новую строку/столбец, нужно выделить строку/столбец, перед которой будет вставлена новая/новый, и по контекстному меню командой Вставить осуществить вставку элемента, либо использовать команду Главная –Ячейки – Вставить.

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

Для удаления ячеек со сдвигом выберите из контекстного меню команду Удалить и укажите способ удаления.

Для удаления данных из ячеек воспользуйтесь командой контекстного меню Удалить – Очистить содержимое. Также на панели Редактирование вкладки Главная существует кнопка Очистит ь, позволяющая выбрать, что именно вы хотите очистить в ячейке (все, формат, содержимое, примечания).

Вставка Дата/Время

ФОРМАТИРОВАНИЕ ТАБЛИЦ В EXCEL 2007

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

Замечание.

Для любой ячейки может быть задано всплывающее текстовое примечание, появляющееся при наведении на ячейку курсора мыши. Чтобы добавить примечание к ячейке, нужно применить команду Рецензирование – Примечания – Создать примечание. В появившемся желтом окне нужно ввести текст примечания. Чтобы отредактировать примечание, по правой кнопки мыши в контекстном меню ячейки выберите Изменить примечание. Для удаления – выберите пункт Удалить примечание в контекстном меню. Чтобы увидеть все примечания, заданные к ячейкам, отобразите их на листе книги при помощи команды Рецензирование – Примечания – Показать все примечания.

ОШИБКИ В ФОРМУЛАХ

Если при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.

Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.

Ошибка #### появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка #ССЫЛКА! появляется, когда в формуле используется недопустимая ссылка на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено содержимое других ячеек.

Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.

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

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

 

EXCEL СПРАВОЧНИК

Создать

Кнопка «Office» | Создать | Пустой лист

Открыть...

Кнопка «Office» | Открыть

Сохранить

Панель быстрого доступа | Сохранить

Сохранить

Кнопка «Office» | Сохранить

Печать

Кнопка «Office» | Печать | Быстрая печать

Предварительный просмотр

Кнопка «Office» | Печать | Предварительный просмотр

Орфография...

Рецензирование | Проверка орфографии | Проверка правописания

Справочные материалы

Рецензирование | Проверка орфографии | Справочные материалы

Вырезать

Главная | Буфер обмена | Вырезать

Копировать

Главная | Буфер обмена | Копировать

Вставить

Главная | Буфер обмена | Вставить

Формат по образцу

Главная | Буфер обмена | Формат по образцу

Отменить

Панель быстрого доступа | Отменить

Вернуть

Панель быстрого доступа | Вернуть

Рукописные примечания

Рецензирование | Рукописные данные | Начать рукописный ввод

Гиперссылка

Вставить | Связи | Гиперссылка

Автосумма

Главная | Правка | Автосумма

Автосумма

Формулы | Библиотека функций | Автосумма

Сортировка по возрастанию

Данные | Сортировка и фильтр | Сортировка от А до Я

Сортировка по убыванию

Данные | Сортировка и фильтр | Сортировка от А до Я

Мастер диаграмм

Вставить | Диаграммы

Рисование

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

Сводная таблица

Вставить | Таблицы | Сводная таблица | Сводная таблица или диаграмма

Примечание

Рецензирование | Примечания | Создать примечание

Автофильтр

Кнопка «Office» | Параметры Excel | Настройка | Все команды | Автофильтр

Объединить и выровнять по центру

Главная | Выравнивание | Объединить и поместить в центре

Денежный формат

Главная | Число | Финансовый

Процентный формат

Главная | Число | Процентный

Формат с разделителями

Главная | Число | Числовой

Увеличить/ Уменьшить разрядность

Главная | Число | Увеличить/ Уменьшить разрядность

Уменьшить/ Уменьшить отступ

Главная | Выравнивание | Уменьшить/ Уменьшить отступ

Границы

Главная | Шрифт | Границы

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

Работа с диаграммами | Конструктор | Тип | Изменить тип диаграммы...

Диаграмма | Исходные данные

Работа с диаграммами | Конструктор | Данные | Выбрать данные...

Диаграмма | Размещение

Работа с диаграммами | Конструктор | Расположение | Переместить диаграмму...

Диаграмма | Размещение

Работа со сводными диаграммами | Конструктор | Расположение | Переместить диаграмму...

Диаграмма | Добавить данные

Работа с диаграммами | Конструктор | Данные | Изменить источник данных

Диаграмма | Добавление линии тренда

Работа с диаграммами | Макет | Анализ | Линия тренда

Диаграмма | Объемный вид

Работа с диаграммами | Макет | Фон | Объемный вид

Автоформат

Главная | Стили | Форматировать как таблицу

Ячейки

Главная | Ячейки | Формат | Формат ячеек

Увеличить/ Уменьшить размер шрифта

Главная | Шрифт | Увеличить/ Уменьшить размер шрифта

Направление текста

Главная | Выравнивание | Ориентация

*Лист справа налево

Разметка страницы | Параметры листа | Документ с текстом справа налево

Сводная таблица | Формат отчета

Работа со сводными таблицами | Конструктор| Стили сводных таблиц

Сводная таблица | Сводная диаграмма

Работа со сводными таблицами | Параметры | Сервис | Сводная диаграмма

Сводная таблица | Мастер сводных таблиц

Кнопка «Office» | Параметры Excel | Настройка | Все команды | Мастер сводных таблиц и диаграмм

Формат отчета

Работа со сводными таблицами | Конструктор | Стили сводной таблицы

Мастер диаграмм

Кнопка «Office» | Параметры Excel | Настройка | Все команды | Мастер сводных таблиц и диаграмм

Объекты диаграммы

Работа с диаграммами | Макет

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

Работа с диаграммами | Конструктор | Тип | Изменить тип диаграммы...

Примечание

Рецензирование | Примечания | Создать примечание

Начать запись

Разработчик | Код | Запись макроса

Список

Разработчик | Элементы управления | Вставить | Список

Блокировать ячейку

Главная | Ячейки | Формат | Блокировать ячейку

Защитить лист

Рецензирование | Изменения | Защитить лист

Список

Главная | Ячейки | Вставить | Вставить строки таблицы сверху

Ячейки

Главная | Ячейки | Вставить

Строки

Главная | Ячейки | Вставить | Вставить строки на лист

Колонки

Главная | Ячейки | Вставить | Вставить столбцы на лист

Лист

Главная | Ячейки | Вставить | Вставить лист

Диаграмма

Вставить | Диаграммы

Функция

Формулы | Библиотека функций | Вставить функцию

Автоформат

Главная | Стили | Форматировать как таблицу

Условное форматирование

Главная | Условное форматирование

Стиль

Главная | Стиль | Стили ячеек

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

Работа с диаграммами | Конструктор | Тип | Изменить тип диаграммы...

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

ЗАДАНИЕ

1. Создать и выполнить форматирование таблицы «Стоимость вычислительной техники».

Порядок выполнения работы

Запустить EXCEL.

1. Выполнить форматирование и заполнение таблицы в соответствии с образцом:

Сформировать шапку таблицы:

- заполнить шапку таблицы: интервал ячеек А1:Е1;

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

1.2. Занести наименование таблицы:

- выделить первую строку (щелкнуть по номеру строки), нажав правую кнопку мыши выбрать вставить

- в ячейку А1 занести заголовок таблицы Стоимость ноутбуков

Сохранить таблицу с именем Таблица 1.XLS.

2. Сформировать и отформатировать по заданному образцу таблицу «Расчет расходов на командировку»

 

1. Заполнить таблицу в соответствии с образцом (если потребуется, выполните подгон ширины столбцов):

2. Отформатировать заголовок:

- расположить заголовок по центру области А1:Е1;

- изменить шрифт заголовка: полужирный, 16 пт, изменить цвет и заливку.

3. Занести формулы расчета:

- выделить интервал ячеек С11:Е11→ меню Формат → Ячейки → вкладка Число → Числовой → Число десятичных знаков: 0, в ячейку С11 занести формулу расчета количества дней в командировке:

=С9-С8

- скопировать эту формулу в ячейки D11, E11;

- в ячейку С12 ввести формулу расчета суммы суточных:

= С10*С11

- скопировать эту формулу в ячейки D12, E12;

- в ячейку С18 ввести формулу итоговой суммы расходов на транспорт:

= С14+С15+С16+С17

- скопировать эту формулу в ячейки D18, E18;

стоимости проживания (С21):

=С20*С11

- скопировать эту формулу в ячейки D21, E21;

- в ячейку С22 ввести формулу расчета общих расходов:

=С12+С18+С21

- скопировать эту формулу в ячейки D22, E22;

- в ячейку С24 ввести формулу разницы между авансом и потраченной суммой:

=С22-С23

- скопировать эту формулу в ячейки D24, E24;

- в ячейку Е25 ввести формулу:

=С24+D24+Е24.

4. Выполнить форматирование таблицы:

- в итоговых строках и в ячейке Е25 изменить шрифт: 12 пт, полужирный, цвет текста – красный;

- присвоить денежный формат ячейкам С10:Е24 и Е25;

- присвоить числовой формат ячейкам С11:Е11;

- выделить всю таблицу и выполнить обрамление внутри и снаружи.

5. Сохранить с именем Командировочные расходы.

 

Задание

 

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

 

Порядок выполнения работы

1. Запустите редактор электронных таблиц Microsoft Excel.

2. Выполните форматирование и заполнение таблицы в соответствии с образцом:

В открывшемся окне Формат ячеек на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание – по центру, на вкладке Число укажите формат – Текстовый. После этого нажмите кнопку Добавить.

 

· Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

3. Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход – Расход,

для этого в ячейке D4 наберите формулу = В4 - С4 → произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

4. Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением отрицательных чисел красным цветом: ФорматЯчеек → навкладке Число выберитеформат Денежный, в поле Отрицательные числа: красные, число десятичных знаков задайте равное 2.

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

5. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (Главная→ Формулы ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические».

Для расчета функции СРЗНАЧ дохода: установите курсор в ячейку B11, далее запустите мастер Главная→ Формулы → категория Статистические → СРЗНАЧ. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения – В4:В10.

Аналогично рассчитайте «Среднее значение» расхода.

6. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирование () на панели инструментов Стандартная или функцией СУММ, в качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10.

7. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм: выделите интервал ячеек с данными финансового результата → Вставка → Диаграмма →

8. Произведите фильтрацию значений дохода, превышающих 5000 руб.

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

Для установления режима фильтра установите курсор внутри таблицы →на вкладке Главная →Сортировка и Фильтр.

Далее появляется окно→выбираем значения дохода (в данном случае превышающее 5000)

В открывшемся окне Пользовательский автофильтр задайте условие «Больше 5000»:

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

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

Задание 2

1. Создать рабочую книгу «Финансовый отдел».

2. Ввести данные согласно заданию (см. табл.1).

Компьютеры

 

 

Ноутбук

 

1750

3

 

Мышь оптическая

 

50

3

 

Программное обеспечение

 

 

Microsoft Project

 

530

1

 

КонсультантПлюс (верс. Проф)

 

300

1

 

Периферийные устройства

 

 

Принтер лазерный цветной А4

 

2700

1

 

Сканер

 

150

2

 

Оргтехника

 

 

Копировальный аппарат А4

 

470

1

 

Дупликатор

 

3500

1

 

Средства связи

 

 

Факсимильный аппарат

 

110

1

 

Телефонный аппарат (база+трубка DECT)

 

115

4

 

ИТОГО:

 

 

8. Средствами Excel организовать возможность перерасчета стоимости в рублях, если за условную единицу принять $.

9. Средствами Excel рассчитать в рублях размер скидки 5 %, предоставляемой фирме при условии, если сумма покупки превышает 1000 у.е. (с использованием логической функции «ЕСЛИ»). Выполнить оформление листа.

Часть 2 Заполнить таблицу (с учётом форматирования), произвести расчеты, выделить минимальную и максимальную суммы покупки; по результатам расчета построить круговую диаграмму суммы продаж.

1. Выполните форматирование и заполнение таблицы в соответствии с образцом:

2. Произвести расчеты, выделите минимальную и максимальную суммы покупки; по результатам расчета построить круговую диаграмму суммы продаж.

Выходной документ

 

 

Часть 3.

a. заполнить ведомость учёта брака (с учётом форматирования), произвести расчёты, выделить минимальную, максимальную и среднюю сумму брака, а так же средний процент брака;

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

 

1. Выполните форматирование и заполнение таблицы в соответствии с образцом:

 

2. а) произвести расчёты, выделить минимальную, максимальную и среднюю сумму брака, а так же средний процент брака. (Сумма брака = Процент брака * Сумма зарплаты);

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

Выходной документ (а)

 

Выходной документ

 

Часть 4.

a. заполнить таблицу анализа продаж (с учётом форматирования), провести расчёты, выделить минимальную и максимальную продажи (количество и сумму);

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

1. Выполните форматирование и заполнение таблицы в соответствии с образцом:

2. а) провести расчёты (Всего = Безналичные платежи + Наличные платежи, Выручка от продаж = Цена * Всего), выделить минимальную и максимальную продажи (количество и сумму);

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

 

Выходной документ (а)

 

Выходной документ (б)

 

1. Задание 4.2.

Создайте таблицу по образцу (рис. 1).

a. Ячейки в столбце F имеют формат Процентный, а количество десятичных знаков = 0.

b. В ячейке F5 формула должна содержать абсолютную ссылку на ячейку Е9: =Е5/$E$9. В этом случае при копировании ячейки F5 в ячейки F6, F7, F8, абсолютная ссылка $E$9 останется неизменной, а будет меняться только относительная ссылка по столбцу Е (Е6, Е7, Е8).

2. По данным таблицы постройте диаграмму, выберите для данных таблицы ячейки F5:F8. Используйте приведенные ниже виды диаграмм.

3. Отредактируйте диаграмму:

– внесите название диаграммы Продажа мороженного за лето 2011 года;

– задайте подписи оси Х, используя данные диапазона ячеек А5:А8;

– задайте Объемный вариант Круговой диаграммы используя те же данные таблицы;

– измените имя текущего листа на новое – Мороженое.

 

Задание 4.3.

4. Создайте следующую таблицу и следующие диаграммы.

- Для первой диаграммы выберите тип - с областями и накоплением.

- Для второй диаграммы выберите тип – Нормированная с областями и накоплением.

- Добавьте легенду и необходимые подписи к осям.

- У второй диаграммы измените Заливку на Рисунок или текстура.

Рисунок 3

 

Задание 4.4.

5. Создайте следующую таблицу и следующую диаграмму. Тип диаграммы - Лепестковая с маркерами.

 

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

«Создание электронной книги. Относительная и абсолютная адресации в MS EXCEL»

 

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

Абсолютные адреса не изменяются при копировании формул. Такие адреса содержат знак $ перед именем столбца и номером строки – $А$1.

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

Координата строки и координата столбца в адресе ячейки могут фиксироваться раздельно.

Для фиксации адреса ячейки используется знак “$”.

Чтобы относительный адрес ячейки в формуле стал абсолютным, после ввода в формулу адреса этой ячейки нажмите <F4>.

Адресация

По строке

Относительная

Абсолютная

По столбцу

Относительная

B1

B$1

Абсолютная

$B1

$B$1

Изменение адреса ячейки в формуле, при ее копировании.

Значение адреса в исходной формуле

Вниз

Вправо

B1

B2

C1

$B1

$B2

$B1

B$1

B$1

C$1

$B$1

$B$1

$B$1

 

 

Задание

Порядок выполнения работы

1. Запустите редактор электронных таблиц Microsoft Excel.

2. Выполните форматирование и заполнение таблицы в соответствии с образцом:

3. Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула: Премия = Оклад × % Премии. В ячейке D5 наберите формулу = $D$4*C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Формула для расчета «Всего начислено»: Всего начислено = Оклад + Премия.

При расчете Удержания используется формула: Удержание = Всего начислено × % Удержания (для этого в ячейке F5 наберите формулу = $F$4*E5).

Формула для расчета столбца «К выдаче»: К выдаче = Всего начислено – Удержания.

4. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка → Функция → категория: Статистические функции).

5. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата январь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.

6. Скопируйте содержимое листа «Зарплата октябрь» на новый лист.

7. Присвойте скопированному листу название «Зарплата Февраль». Исправьте название месяца в названии таблицы. Измените значение премии на 32 %. Убедитесь, что программа произвела пересчет формул.

8. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле Доплата = Оклад × % Доплаты. Значение доплаты примите равным 5 %.

9. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата.

10. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта; меньше 7000 – красным; больше или равно 10000 – синим цветом шрифта (Главная → Условное форматирование).

11. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию

12. Защитите лист «Зарплата Февраль» от изменений (Рецензирование→ Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.

13. Сохраните книгу под именем «Зарплата».

Задание 2

1. Создайте таблицу следующего вида:

 

 

2. Номера позиций введите, используя автозаполнение.

3. Напишите, используя абсолютную адресацию, в ячейку D5 формулу для вычисления цены товара в рублях (=C5*$B$3), затем скопируйте ее до D12.

4. Напишите, используя относительную адресацию, в ячейке G5 формулу для стоимости, затем за маркер заполнения скопируйте ее до G12.

5. Используя автосуммирование, вычислите «Итого» в ячейке G13.

6. Нанесите сетку таблицы там, где это необходимо.

7. Сохраните документ под именем Прайс.xls.

8. Измените курс доллара на 2,5. Посмотрите, что изменилось.

9. Поменяйте произвольно количество товара. Посмотрите, что изменилось.

 

Задание 3

1. Создайте следующую таблицу:

 

 

2. Вместо ФИО введите свои фамилию, имя и отчество.

3. В ячейку D5 введите формулу расчета прибыли – Приход-Расход. Настройте ячейки так, чтобы в случает отрицательного баланса цифры выделялись красным цветом.

4. При помощи маркера заполнения скопируйте формулу до D16.

5. «Итого» подсчитайте при помощи автосуммирования.

6. Среднее, максимальное и минимальное значения – с использованием функций СРЗНАЧ, МАКС и МИН.

7. Нанесите сетку.

8. Примените шрифтовое оформление и заливку шапки таблицы.

9. Сохраните документ под именем Plus.xls.

{ Совет 1. Для введения названия месяцев используйте функцию автозаполнения.

Совет 2. При заполнении столбцов прихода и расходы настройте предварительно денежный формат ячейки.}

Задание 4 Решить задачу

Дядя Федор, кот Матроскин и пес Шарик летом жили в Простоквашино, а папа с мамой слали им письма, посылки, телеграммы и бандероли, которые доставлял почтальон Печкин. Каждое письмо весило в среднем – 100 гр, каждая посылка – 5 кг, каждая телеграмма – 50 г, каждая бандероль – 500 г.

Дядя Федор получил 10 писем, 2 посылки, 10 телеграмм, 1 бандероль. Кот Матроскин – 4 письма, 1 посылку, 2 телеграммы, 1 бандероль. Пес шарик не получил ни одного письма, ни одной телеграммы, зато получил 4 посылки и 2 бандероли.

Определить, сколько килограммов и какой почты получил каждый житель Простоквашино; сколько весила вся доставленная Печкиным почта одного вида; какой общий груз пришлось перенести почтальону Печкину?

Ваши задачи:

1.

1. Выбрать оптимальную структуру таблицы для решения этой задачи

2. Занести все необходимые данные в таблицу

3. Произвести расчеты с использованием формул.

После заполнения таблицы задается вопрос:

2. - В какой формуле будет использоваться абсолютная ссылка и почему? (так, для данного вырианта таблицы, в формуле, содержащейся в ячейке F2 = B2*$B$8+C2*$B$9+D2*$B$10+E2*$B$11, т.к. ячейки B8,B9, B10,B11 в формулах при копировании не должны изменяться)

 

Задание 5

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

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

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе 5 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист).

3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в названии таблицы.

4. Измените значения Премии на 46 %, Доплаты — на 8 %. Убедитесь, что программа произвела пересчет формул (рис. 2.1).

5. По данным таблицы «Зарплата декабрь» постройте гистограмму дохода сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 2.2.

Рис. 2.1. Ведомость зарплаты за декабрь

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

7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/ Скопировать л