Примеры задач по теме «Табличный процессор MS Excel. Создание диаграмм»

41 42 43 44 45 46 47

1. Заполните данными и рассчитайте таблицу согласно варианту.

2. Создайте нумерацию столбиков и строк, с помощью маркера автозаполнения и клавиши CTRL.

3. Создайте автосписок для заполнения первой или второй колонки таблицы.

4. Проанализируйте данные с помощью диаграмм.

5. Скопируйте таблицу на Лист 2. Установите режим вывода формул.

6. Переименуйте Лист 2 дав ему имя Формулы.

7. Подготовьте созданные документы в печать в режиме Предварительный просмотр:

8. ориентация страниц – альбомная;

9. колонтитулы: верхний - дата создания, нижний – фамилия студента и группа.

10. Распечатайте созданные документы.

11. Сохраните файл на диске, дав ему имя (фамилия студента).

Вариант №1

Сведения реализации товаров

№ п/п	Наименование товара	Количество упаковок	Цена упаковки (руб.)	Сумма товара	Сумма наценки	Сумма продажи с учетом торговой наценки

1.
				*	*	*
				*	*	*
Итог:	*		*	*	*
Торговая наценка	20%

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

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

Вариант №2

Расчет прибыли

Статья дохода	Первый квартал	Вместе	Удельный вес
январь	февраль	март
Компьютеры				*	*
Комплектующие				*	*
Программное обеспечение				*	*
Доход всего:	*	*	*	*
Статьи затрат
Реклама				*	*
Аренда				*	*
Налоги				*	*
Затраты всего:	*	*	*	*
Прибыль:	*	*	*	*

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

2. Построить гистограмму сравнения затрат февраля и марта. Расположить на отдельном письме.

Примеры задач по теме «Табличный процессор MS Excel. Анализ данных с помощью сводных таблиц»

1. Заполните таблицу согласно варианту.

2. Рассчитайте ячейки где содержатся звездочки (*).

3. Создайте сводные таблицы согласно вариантам задач.

4. Для третьей сводной таблицы постройте объемную диаграмму.

5. Выполните фильтрацию данных в начальной таблице с помощью автофильтра.

6. Сохраните файл на диске, дав ему имя (фамилия студента).

Вариант № 1 Сведения о поставке продукции

Дата

Количество

Товар

Страна поставщик

Цена за единицу

Сумма

Январь

30 000

Пуговицы

Франция

Январь

240 000

Застежки

Италия

Февраль

121 000

Пуговицы

Франция

1,7

Январь

98 000

Пуговицы

Франция

1,7

Март

345 000

Застежки

Италия

Февраль

215 000

Пуговицы

Канада

Март

480 000

Застежки

Франция

6,8

Январь

212 000

Пуговицы

Италия

3,6

Апрель

420 000

Пуговицы

Канада

4,5

Май

540 000

Пуговицы

Франция

1,7

Май

311 000

Пуговицы

Италия

3,6

Июнь

120 000

Застежки

Канада

Май

98 000

Пуговицы

Франция

1,7

Май

300 000

Пуговицы

Италия

3,6

Июнь

120 000

Застежки

Италия

Вместе:

Используя мастер сводных таблиц создайте сводные таблицы:

a) Суммы поставок товаров по странам поставщицам и товарам в разрезе дат.

b) Поставок товаров с определением минимальной цены и среднего количества.

c) Максимального количества продукции по товарам и странам в разрезе наименований товаров.

Вариант № 2 Данные о прибыли за год

Месяц

Отдел

Доход

Затраты

Рентабельность (гр.3/гр.4)

Прибыль (гр.3 - гр.4)

Январь

обувь детская

127 735

45 495

Февраль

обувь детская

127 246

47 710

Март

обувь детская

127 289

48 402

Апрель

обувь детская

127 169

47 217

Май

обувь детская

131 330

49 082

Июнь

обувь детская

130 996

49 862

Июль

обувь детская

131 054

51 872

Август

обувь детская

135 284

61 427

Сентябрь

обувь детская

138 903

62 342

Октябрь

обувь детская

136 368

62 353

Ноябрь

обувь детская

135 199

60 571

Декабрь

обувь детская

135 144

59 848

Январь

одежда женская

400 000

256 354

Февраль

одежда женская

558 654

425 862

Вместе

Используя мастер сводных таблиц создайте сводные таблицы:

a) Общего дохода и общих затрат по отделам в разрезе месяцев.

b) Средней прибыли и средней рентабельности по месяцам в разрезе отделов.

c) Минимальных затрат и доходов по отделам в разрезе месяцев.

Примеры задач по теме «Табличный процессор MS Excel. Использование стандартных функций»

Вариант 1.

1. Создайте таблицу и отформатируйте ее по образцу.

2. В столбик Наименования продукции ввести наименование продукции с помощью созданного автосписка.

3. В столбике Дата изготовления введите дату (текущий год) согласно формату дд/мм/гггг.

4. В столбике Срок пригодности введите дату согласно формату дд/мм/гггг (дата текущего года. Дата изготовления < Срок пригодности).

5. В столбике Цена ввести цену произвольно (5¸50)

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

7. В столбике Скидка с помощью функции с категории Логические предоставить уценкупо следующим условиям:

для товарной группы пирог и булка, где количество дней от даты изготовления более 4 дней – 40%;

для товарной группы пирожки, где количество дней от даты изготовления от 2 дней до 4 дней – 20%;

в другом случае – 5%.

8. Рассчитать столбик Цена с учетом акционной скидки.

9. В ячейках где указанная * рассчитать данные с помощью функции с категории Статистические.

10. Скопировать таблицу на новый лист, переименовать его в Формулы и вывести данные в режиме формул.

Наименование продукции	Товарная группа	Дата изготовления	Цена	Срок пригодности	Количество дней товара от даты изготовления	Скидка (%)	Цена с учетом акционной скидки
Пирог Ромашка ассорти	Пирог
Пирог с маком	Пирог
Сочник	Булка
Ватрушки	Булка
Кулич	Булка
Плетенка	Булка
Бутерброд	Булка
Булочка с изюмом	Булка
Булочка со сгущенным молоко	Булка
Пирог с повидлом	Пирог
Пирожки с мясом	Пирожок
Пирожки с капустой	Пирожок
Пирог с мясом и рисом	Пирог
Максимальный срок пригодности	*
Количество изделий, которым была предоставленная акционная скидка	*

Вариант 2.

1. Создайте таблицу и отформатируйте ее по образцу.

2. В столбик ФИО вкладчика ввести данные с помощью созданного автосписка.

3. В столбике Количество полных лет рассчитать количество лет с помощью функций с категории Дата и время.

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

5. В столбике Сумма по процентам с помощью функции с категории Логические рассчитать сумму по процентампо следующим условиям:

для типа вклада А, что находится в банке больше 5 лет – 18% за каждый месяц текущего года;

для типа вклада А, что находится в банке меньше 5 лет – 10% за каждый месяц текущего года;

для типа вклада В, что находится в банке больше 3 лет – 20% за каждый месяц текущего года;

для типа вклада В, что находится в банке меньше 3 лет – 13% за каждый месяц текущего года;

в другом случае – 5%.

6. В ячейках где указана * рассчитать данные с помощью функций с категории Статистические.

7. Скопировать таблицу на новый лист, переименовать его в Формулы и вывести данные в режиме формул.

ФИО вкладчика	Тип вклада	Дата регистрации вклада	Сумма вклада	Количество полных лет	Количество месяцев	Сумма по процентам
	А	01/12/2008
	В	03/06/2007
	В	12/05/2010
	А	01/05/2003
	А	03/02/2003
	С	09/08/2010
	А	15/01/2011
	С	02/03/2012
	В	20/01/2011
Общая сумма на счетах	*
Максимальная сумма вклада	*
Средняя сумма по процентам	*

4.8 Контрольные вопросы по теме “Табличный процессор MS Exсel”

1. Табличный процессор MS Excel. Основные понятия.

2. Что такое адрес ячейки? Где отображается адрес активной ячейки?

3. Маркер заполнения. Создание автосписков. Автозаполнения.

4. Работа с листами. Перемещение по листу.

5. Понятие диапазона. Виды диапазонов? Выделение диапазонов. Выделение несопредельных диапазонов.

6. Типы данных в ячейках. Как выравниваются разные типы данных в ячейках?

7. Форматирование ячеек.

8. Редактирование содержимого ячеек.

9. Работа с формулами. Диагностика ошибок в формулах.

10. Типы ссылок в формулах.

11. Как вывести таблицу в режиме формул?

12. Диаграммы. Создание диаграмм.

13. Форматирование диаграмм. Как добавить легенду к созданной диаграмме? Как изменить вид диаграммы?

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

15. Как построить диаграмму по сводной таблице? Как изменить вид диаграммы?

16. Функция. Виды функций. Форматы логических функций.

17. Найдите в формуле ошибки =ЕСЛИ ((В3 “доллар;S4*5,05;S4*6,2

18. Печать таблиц. Изменение ориентации таблицы. Как центрировать таблицу для печати. Как добавить колонтитулы.

19. Пересчитает основные типы задач экономического планирования.

Литература

1. ВЕЙССКОПФ ДЖ. Excel 2000: Базовый курс/Пер. с англ.. - Киев, М., СПб.: Век, Энтроп, Корона- Принт, 2000. - 400 с.

2. Спіцина Н. М. Інформаційні системи і технології. [Текст]: навч. посіб. по базовій підготовці студ. рівня бакалавр і спеціаліст ден. і заоч. форм навчання / М-во освіти і науки України, Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, каф. інформ. систем і технологій упр.; Н. М. Спіцина, Т. В. Шабельник, Бондаренко С.В. – Донецьк: [ДонНУЕТ], 2011.- 304 c.

3. Холи, Д. Excel 2007. Трюки / Д. Холи, Р. Холи; пер. с англ. А. Струсевич. ─ СПб.: Питер, 2008. ─ 363 с.: ил. ─ 978-5-91180-494-7

4. ЭЙТКЕН П. Microsoft Word 2000: Пер. с англ.. - М., СПб., Киев: Вильямс, 2000. - (Освой самостоятельно!). - 208 с.

5. Юдін В.І. Основи роботи в Microsoft Excel XP: навч. посіб. для студ. вищ. навч. закл. /В. І. Юдін, В.С. Рижиков, В.В. Ровенська - К.:Центр учбової літератури, 2007. - 272с.

6. Інформатика та комп’ютерна техніка: Навчальний посібник. – К.: НМЦ “Укоопосвіта”, 2000 – 335 с. Оліфіров О.В. Інформаційні системи і технології підприємства [Текст]: навч. посібник для студентів екон. напрямів підготов. ден. і заоч. форм навчання / О.В. Оліфіров, Н.М. Спіцина, Т.В. Шабельник; Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, каф. інформац. систем і технологій упр. - Донецьк: [ДонНУЕТ], 2010.- 312 с..

7. Оліфіров О.В. Інформаційні системи в менеджменті: навч. посібник для студентів екон. напрямів підготов. ден. і заоч. форм навчання / О.В. Оліфіров, Н.М. Спіцина, Т.В. Шабельник; Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, каф. інформац. систем і технологій упр. - Донецьк: 2014. - 215 с.

8. Уокенбах Дж. Формулы в Microsoft Excel 2010. – «Диалектика»,2011.– 704с.

9. Шабельник, Т. В. Комп'ютерні системи обробки обліково-економічної інформації [Текст]: навч. посіб. для студ. спеціалізації 7.050104 «Фінанси» рівня магістр ден. і заоч. форм навчання / Т. В. Шабельник; Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, Каф. інформ. систем і технологій управління – Донецьк: [ДонНУЕТ], 2009. - 120 с.

10. Шабельник Т.В. Економічна кібернетика [Текст]: навч. посіб. для студ. екон. напрямів підготов. ден. і заоч. форм навчання / Т.В. Шабельник, Н.М. Спіцина; Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, Каф. інформац. систем і технологій упр. - Донецьк: [ДонНУЕТ], 2010.- 134 с.

11. Microsoft Office 2010, 2007 and 2003 Tutorials For Word, Excel, PowerPoint and Outlook. Режим доступа: <www.msoffice-tutorial-training.com>

12. Project Expert 7.21 - Project Expert: программа для разработки бизнес-планов и анализа инвестиционных проектов. Режим доступа: freesoft.ru›Project Expert

13. Методы анализа и прогнозирования временных рядов; казуальные (причинно-следственные) методы. Режим доступа: rbsoft.ru

14. Методы экспертных оценок; методы анализа и прогнозирования временных рядов; казуальные (причинно-следственные) методы. Режим доступа: marketing.r-cons.ru›.

15. http://ru.wikipedia.org – независимая энциклопедия.

16. http:// donnuet.dn.ua – официальный сайт ДонНУЭТ.

17. http:// library.donetsk.ua - официальный сайт Донецкой научной библиотеки им. Крупской

№ п/п	Чтобы выделить	Выполните действия
1.	Слово	Дважды щелкните слово.
2.	Рисунок	Щелкните рисунок.
3.	Предложение	Удерживая нажатой клавишу CTRL, щелкните на предложении.
4.	Строка текста	Переместите указатель на полосу выделения^***перед предложением и щелкните кнопкой мыши.
5.	Абзац	Переместите указатель на полосу выделения^***перед абзацем, и дважды щелкните кнопкой мыши. Другой образ: трижды щелкните абзац.
6.	Несколько абзацев	Переместите указатель на полосу выделения^*** перед абзацем, и дважды щелкните кнопкой мыши, а потом перетяните указатель вверх или вниз.
7.	Небольшой фрагмент текста	Используйте перетаскивание. Установите курсор в начало фрагмента, нажмите клавишу SHIFT и удерживая ее нажимайте соответствующие клавиши управления курсором ®¯
8.	Большой блок текста	Щелкните начало фрагмента, прокрутите документ так, чтобы на экране появился конец фрагмента, а потом щелкните его, удерживая нажатой клавишу SHIFT. Другой образ. Установите курсор в начало фрагмента, нажмите клавишу SHIFT и удерживая ее нажимайте соответствующие клавиши управления курсором ®¯
9.	Весь документ	Переместите указатель на полосу выделения^*** перед текстом, после чего трижды щелкните кнопкой мыши.
10.	Колонтитулы	В обычном режиме выберите Вид - Колонтитулы. В режиме разметки дважды щелкните неяркий текст колонтитула. Переместите указатель на полосу выделения^*** перед колонтитулом, после чего трижды щелкните кнопкой мыши.
11.	Вертикальный блок текста (кроме текста внутри ячейки таблицы)	Удерживая нажатой клавишу ALT, перетяните указатель.

^***Полоса выделения - левое поле документа. На полосе выделения указатель мыши принимает вид белой стрелки, направленной вправо.

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

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

† #ИМЯ? - невозможность распознать имя, которое используется. Это значение ошибки возникает, когда неправильно указано имя объекта или имеются ссылки на имя, которое было изъято; когда неверно записана функция; когда при записи адресов вместо латыни использована кириллица и т.д.

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

† #ЧИСЛО! – значение ошибки, которые означает проблему, связанную с представлением или с использованием чисел. Не исключено, что в функции с числовым аргументом используется аргумент нечислового формата. Возможно также, что в ячейку введена формула, которая возвращает слишком большое значение по модулю (свыше 1037).

† #ССЫЛКА! - означает наличие проблемы с интерпретацией ссылок, которые имеются в формуле. Возможно, что формула содержит ссылку на ячейку, которая уже изъята, или ссылку на ячейку, в которую скопировано содержимое других ячеек.

† #ДЕЛ/0! - попытка деления на нуль. Такая ситуация чаще возникает не из-за того, что в ячейке записано явное деление на нуль (оператор /0), а как следствие использования ссылки на пустую ячейку или ячейку, которая содержит нулевое значение.

† #ПУСТО! - значение ошибки, которое появляется в случае задания в ссылке пустого множества ячеек.

† #Н/Д – сокращение от термина «неопределенные дани». Это значения ошибки обычно специально вводится в ячейки, чтобы предотвратить вычисления, которые не могут быть сделаны из-за отсутствия данных.

Клавиши	Перемещение
<Home>	В начало текущей строки
<Ctrl+Home>	В ячейку A1
<Ctrl+End>	В последнюю заполненную ячейку таблицы
<>	На одну ячейку вверх
<¯>	На одну ячейку вниз
<®>	На одну ячейку вправо
<>	На одну ячейку влево
<Ctrl+>	Вверх к первой заполненной ячейке
<Ctrl+¯>	Вниз к первой заполненной ячейке
<Ctrl+®>	Вправо к первой заполненной ячейке
<Ctrl+>	Влево к первой заполненной ячейке
<Page Up>	Вверх на один экран
<Page Down>	Вниз на один экран
<Alt+Page Up>	Влево на один экран
<Alt+Page Down>	Вправо на один экран
<Ctrl+Page Up>	К предыдущему листу рабочей книги
<Ctrl+Page Down>	К следующему листу рабочей книги

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

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

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

В Excel различают два типа адресации: абсолютную и относительную. Оба типа можно применять в одном ссылке - смешанная ссылка. Тип адресации аргумента, который применяется в формуле, играет существенную роль при копировании или перемещении формулы.

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

Абсолютная ссылка задает абсолютные координаты клеточки в рабочем листе (относительно левого верхнего угла таблицы). Можно приказать Excel интерпретировать номера строки и (или) столбца как абсолютные путем введения символа доллара ($) перед именами строки и (или) столбца. Например, $A$7. При перемещении или копировании формулы абсолютную ссылку на клеточку (или диапазон клеточек) измененное не будет, и на новом месте скопированная формула будет ссылаться на ту же именно клеточку (диапазон клеточек).

Нажатие <F4>	Адрес	Ссылка
Один раз	$A$7	Абсолютная ссылка
Два раза	A$7	Абсолютная ссылка на строку
Трижды	$A7	Абсолютная ссылка на столбец
Четыре раза	A7	Относительная ссылка