Часть 2. Анализ и управление данными. Работа с массивами. Часть 1. Создание, редактирование, форматирование таблицы

Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм

Создание таблицы

1. Создать таблицу под названием «Операционный дневник» в соответствии с образцом (рис. 5). Ввести в таблицу следующие названия столбцов: № п/п, Фамилия И.О., Вид вклада, Остаток входящий, Приход, Расход, Остаток исходящий, % вклада, Остаток с учетом % вклада.Ввести 10 записей.

2. В столбец № п/п ввести ряд чисел с использованием команды контекстного меню Заполнить;

3. Столбец Фамилия И.О. заполнить значениями, используя дополнительную таб. 1 и функции СЦЕПИТЬ и ЛЕВСИМВ;

4. Столбец Вид вклада заполнить значениями из дополнительной таб. 2;

5. Столбец Остаток входящий заполнить произвольными значениями от 10000р. до 80000р.;

6. Столбцы Приход и Расходзаполнить произвольными значениями в пределах от 1000р. до 20000р.;

7. Значения остальных столбцов рассчитать по формулам:

- Остаток исходящий = Остаток входящий + ПриходРасход;

- % вкладарассчитать, используя дополнительную таб. 2 и функцию ПРОСМОТР;

- Остаток с учетом % вклада = Остаток исходящий + Остаток исходящий * % вклада.

 

Рис. 1

8. В соответствии с образцом в выделенных ячейках рассчитать итоговое, максимальное и минимальное значения столбцаОстаток с учетом % вклада. При вводе функций использовать различные средства: Мастер функцийиАвтосуммирование.

 

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

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

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

- изменить шрифт «шапки» на жирный, размер 12 pt, выполнить заливку, расположить текст по центру;

- столбец Остаток с учетом % вклада выделить светло-серым фоном;

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

- добавить строку для заголовка таблицы и ввести в заголовок текст «Операционный дневник». Заголовок таблицы отцентрировать в пределах блока таблицы, назначить жирный цветной шрифт Arial, размер 12 pt, увеличить высоту строки. После заголовка перед шапкой вставить пустую строку;

- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной пунктирной линией, «шапку» таблицы – двойными красными линиями, столбцы и строки – тонкими черными линиями;

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

- пометить выноской ячейку смаксимальным значением столбца Остаток с учетом % вклада.

10. Присвоить листу имя Операционный Дневник. Изменить цвет ярлычка листа.

 

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

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

12. На отдельном листе создать нестандартную диаграмму в виде плоскости с конусами, отображающую значения ряда Остаток с учетом % вклада;в качестве категорий использовать значения ряда Фамилия И.О.. Ввести подписи данных для минимального и максимального значений. Изменить способ заливки фигур. Ввести в диаграмму заголовок. Листу с диаграммой присвоить имя Диаграмма_Остаток. Добавить на лист подложку.

13. Сохранить файл под именем Фамилия_№ задания.xls.

 

Часть 2. Анализ и управление данными. Работа с массивами

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

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

 

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

15. При помощи таблицы подстановки данных проанализировать зависимость итоговой суммы столбца Остаток с учетом % вкладаот процента вклада Пенсионный (подставить значения 3, 3,5, 4, 4,5, 5%). Исходные значения выделить цветом.

 

16. Сохранить файл под именем Фамилия_№ задания_2.xlsx.