Курс практических работ по изучению MS Excel 2007

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 2007»

10 данными по образцу, приведенному на рис.2.2.а, или воспользуйтесь результатами предыдущего занятия и сохраните созданный файл.

1.1. Озаглавьте столбцы.

10.

10 вводить не надо.

1.4. Одну из строк диапазона сделайте дублирующей любую другую строку диапазона.

Рис.2.2.а

 

Рис.2.2.б

.

2.1. Установите курсор внутрь диапазона.

и в диалоговом окне Создание таблицы проверьте расположение данных таблицы и нажмите ОК.

После преобразования в таблицу диапазон представлен на рис.2.2.б.

3. Познакомьтесь с контекстной вкладкой Работа с таблицами – Конструктор, которая доступна при переходе к любой ячейке таблицы.

3.1. Убедитесь в возможности прокрутки строк таблицы при сохранении на экране заголовков столбцов таблицы.

и проследите за результатом.

и предложенными командами-флажками для применения особого форматирования для отдельных элементов таблицы.

и примените один из них.

3.5. Удалите из таблицы одну из строк.

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

4. Познакомьтесь с особенностями ввода формул в таблицу.

.

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

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

будет записана одинаковая формула =[Количество]*[Цена].

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

формул.

Формулы – Определенные имена – Диспетчер имен.

среднее значение.

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

6.1. Отсортируйте таблицу по наименованию продукции (в алфавитном порядке).

6.2. Отсортируйте таблицу в порядке убывания цены на продукцию.

6.3. С помощью фильтрации найдите данные таблицы для бетона и дверей.

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

.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

»

Знакомство с возможностями условного форматирования таблиц.

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

«три сигнала светофора без обрамления», а к диапазону С3:С14 — «пять четвертей».

.

. Ознакомьтесь с возможностями данного окна.

, установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.

Рис 4.5

2. Создайте таблицу, приведенную на рис.4.6.

2.1. С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.

:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.

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

.

и выделите значение Гермес.

Рис.4.6

 

 

 

 

 

 

 

 

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

»

Знакомство с организацией вычислений в таблицах.

2. Научитесь использовать различные приемы заполнения ячеек формулами.

.

, предварительно выделив диапазон вставки).

 

Рис.5.1

9.

10.

4).

.

2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).

.

, определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.

).

значения в таблице и выполните ручной пересчет.

11).

5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.

5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.

.

 

Рис.5.2

9.

10 в четыре раза.

, помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.

Рис.5.3

.

 

 

 

 

 

 

 

 

 

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

»

 

Excel.

.

1.1.Создайте таблицу, приведенную на рис.6.1.

 

Рис.6.1

(столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.

1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.

функциями.

2.1. Активизируйте второй лист созданной книги.

2.2. Введите таблицу, приведенную на рис.6.2.

:

если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,

в противном случае — 10%.

и скопируйте ее в диапазон D3:D6:

если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,

в противном случае — 2%.

 

 

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

если стоимость товара <2000, то скидка составляет 5% от стоимости товара,

если стоимость товара >5000, то скидка составляет 15% от стоимости товара,

в противном случае — 10%.

2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

ЕСЛИ, выполните следующие действия:

, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

вывод даты рождения, взятой из соответствующей клетки,

.

3.1. Активизируйте третий лист книги Имя_6_1.

функцию, отображающую сегодняшнюю дату.

3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.

(понедельник, вторник, среда…).

запишите аналогичную функцию для даты, введенной в клетку С3.

3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:

) и СЕГОДНЯ().

. Зафиксируйте результаты и объясните различие.

3.9. Определите номер текущей недели и выведите сообщение:

3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.

3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.

. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.

 

Рис.6.3

   РАНГ и ПРЕДСКАЗАНИЕ.

4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.

4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.

4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.

Рис.6.4

5. Научитесь использовать текстовые функции.

5.1. Используйте формулу

Проанализируйте полученный результат и измените аргумент функции ТЕКСТ, применяющий формат.

).

Рис.6.5

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

срок 4 года, размер ссуды 70 000 руб., процентная ставка составляет 6% годовых. Для вычислений используйте функцию ПЛТ.

2. Вычислите общее количество выплат по ссуде размером 70 000 руб. Ссуда взята под 6% годовых. Объем ежемесячных выплат по ссуде 1 643,95 руб. Для вычислений используйте функцию КПЕР.

6.3. Вычислите объем ссуды, которую можно получить на 4 года под 6% годовых, если объем выплат не превышает 1 643,95 руб. Для вычислений используйте функцию ПС.

. Для вычислений используйте функцию ОСПЛТ.

. Для вычислений используйте функцию ПРПЛТ. Просуммируйте результаты вычислений функций ОСПЛТ и ПРПЛТ за соответствующие периоды и сделайте выводы.

7. Предъявите результаты работы преподавателю.

 

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

»

Excel.

1. Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.

 

Рис.7.1

2. Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.

11 для быстрого построения гистограммы на отдельном листе.

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

, измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.

2.4. Познакомьтесь с экспресс — макетами диаграммы и примените один из них, для возврата используйте команду экспресс – макет 11.

.

.

текущем.

и используйте для него вспомогательную ось. Снабдите гистограмму всеми элементами диаграммы (п.2.5) и оформите ее по своему усмотрению. Сохраните книгу.

их на отдельных листах. Каждый лист должен иметь имя, соответствующее типу диаграммы, расположенной на нем.

).

).

).

).

).

).

3.7.Постройте точечную диаграмму (XY).

3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).

3.9.Постройте объемную гистограмму (3-D_Column).

3.10.Постройте объемную диаграмму с областями (3-D_Area).

.

4.3. Отредактируйте линейные графики так, как показано на рис.7.3.

 

 

Рис.7.3

4.4. Научитесь редактировать объемные диаграммы.

);

;

;

4.4.2. Измените перспективу, сужая и расширяя поле зрения.

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

5. Предъявите результаты преподавателю.

 

 

 

 

 

 

 

 

 

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

»

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

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

Премия = Оклад * %Премии

Всего начислено = Оклад + Премия

сего начислено * %Удержания

сего начислено – Удержания

»

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

. Убедится, что был произведен перерасчет формул.

)

Доплата = Оклад * %Доплаты

Изменить формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата

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

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

Поставить к ячейке Премия комментарий «Премия пропорциональна окладу».

Построить круговую диаграмму начисленной суммы «К выдаче» всех сотрудников за ноябрь месяц.

Защитить лист «Зарплата за ноябрь» от изменений. Задать пароль на лист, сделать подтверждение пароля. Убедиться в том, что лист защищен и невозможно удаление данных.

Предъявите результаты преподавателю.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

»

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

Теоретические сведения:

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

5

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

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

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

 

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

 

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

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

Изменить значение премии на 46%, Доплаты – на 8%. Убедится в том, что был произведен перерасчет формул.

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

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

Скопировать содержимое листа «Зарплата за октябрь» на новый лист электронной книги. Присвоить этому листу название «Итоги за квартал». Исправить название таблицы на «Ведомость начисления зарплаты 4 квартал».

сего. Между названием таблицы и самой таблицей вставить пустую строку. Вставить новый столбец Подразделение.

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

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

«–».

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

Сохранить файл Зарплата с произведенными изменениями.

Предъявите результаты преподавателю.

 

 

 

 

 

 

 

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

»

Цель работы: Изучение информационной технологии быстрого и удобного извлечения данных и формирования различных обобщающих сводок.

1. Создание исходной таблицы.

. Создадим таблицу с заголовками: ФИО; Наименование товаров; Выручка; Дата

соответствует одной продаже

   

2. Сортировка данных.

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

– Сортировка

В этом окне указывается иерархия сортировки:

Сортировка по – ФИО (по возрастанию)

Затем по – Наименование товара (по возрастанию)

В последнюю очередь по — Дата (по возрастанию)

После выполнения необходимых действий таблица будет отображена в следующем виде (рис.2)

 

Фильтрация.

, в которой предлагается выбрать:

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

– отмена фильтра.

– устанавливает сложный фильтр.

), тогда в заголовке таблицы будут отображены значки выбора.

. Тогда таблица выглядит следующим образом:

3.3. Теперь из полученного списка отберем 5 максимальных значений выручки.

.

 

 

 

 

Тогда в таблице останутся записи:

 

 

 

 

 

 

 

» установим параметры отображения строк:

Т

 

 

 

 

 

 

исходная таблица примет вид:

 

 

 

 

 

 

.

 

Итоги.

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

Для формирования итогов необходимо отсортировать данные.

4.1. Найдем суммарную выручку каждого из работников организации.

» — указываются столбцы, содержащие обрабатываемые значения.

В нашем случае устанавливаем в полях:

При каждом изменении в» — ФИО

«Операция» – Сумма

» – Выручка

таблица примет вид (рис.1):

 

 

 

 

 

 

 

 

 

 

4.2. А теперь найдем итог продаж по видам товаров. Для этого

Наименование товара

Сумма

.

В этом случае таблица принимает вид (рис.2)

 

 

 

 

 

 

 

 

 

 

 

 

 

Предъявите результаты преподавателю.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Тема «Подбор параметров»

Изучение технологии экономических расчетов в табличном процессоре.

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

Оценка рентабельности рекламной кампании фирмы.

Создать таблицу оценки рентабельности рекламной кампании фирмы по образцу.

Присвоить ячейке с процентной ставкой имя «Ставка».

Произвести расчеты по следующим формулам:

)

7 формула примет вид:

7

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

6:

6 * (1 + Ставка / 12) ^ (1 – $А6)

6):

7

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

6

Ввести условное форматирование результатов расчета колонки Н: отрицательных чисел – синим курсивом, положительных чисел – красным цветом шрифта.

На какой месяц приходится точка окупаемости?

Рассчитать количество месяцев, в которых сумма покрытия имеется (использовать функцию Счет из раздела Статистические).

).

Построить графики по результатам расчетов:

нарастающим итогом» по результатам расчетов колонки Н;

);

По графикам сделать вывод об окупаемости рекламной кампании.

Сохранить файл.

 

Фирма поместила в коммерческий банк 45000 руб. на 6 лет под 10,5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопилось 250000 руб.?

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

) двумя способами:

С помощью формулы

n

С помощью функции БС

Используя режим Подбор параметра рассчитать, какую сумму надо поместить в банк

   

= 12) составит 15%.

Формула для расчета доходности:

– 1

Используя режим Подбор параметра произвести обратный расчет

 

ъявите результаты преподавателю

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

. Построение гистограммы»

.

 

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

*100%

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

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

ака * Сумма зарплаты)

 

 

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

Всего = Безналичные платежи + Наличные платежи

сего

Предъявите результаты преподавателю

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *