Л а б ораторная работа 1. Интерфейс Microsoft Excel 2007.

Л а б ораторная работа 1. Интерфейс Microsoft Excel 2007.

 

Цель работы:

 

1. Изучение ленты – главного элемента пользовательского интерфейса

 

Microsoft Excel 2007.

 

2. Получение навыков работы с основными вкладками ленты и элемен-

 

тами управления: кнопками, списками, счетчиками, флажками.

 

3. Знакомство с контекстными вкладками для работы с таблицами, ри-

 

сунками и диаграммами.

 

4. Рассмотрение основных возможностей настройки панели быстрого

 

доступа и кнопки «Office».

 

5. Изучение способов и основных правил ввода и редактирования дан-

 

ных в документах Microsoft Excel 2007.

 

6. Освоение методов ввода и редактирования чисел, календарных дат и

 

времени, использования автозавершения и автозаполнения стандарт-

 

ными списками при вводе данных.

 

7. Применение технологии проверки вводимых данных.

 

З адание 1.

 

1. Запустите Microsoft Excel 2007.

 

2. Сверните ленту с использованием Панели быстрого доступа.

 

3. Разверните ленту двойным щелчком по названию вкладки Главная.

 

4. Сверните и разверните ленту с использованием комбинации клавиш

 

Ctrl + F 1.

 

5. Добавьте в Панель б ы строго доступа кнопку Открыть.

 

6. Из вкладки В с т авка (группа Д и аграммы) добавьте в Панель быст-

 

р о г о доступа кнопку Гистограмма.

 

7. Поочередно перейдите во вкладки Г л авная, Вс т а вка, Р азметка

 

страницы, Формулы, Данные, Р е ц ензирование, В и д, посмотрите

 

группы внутри каждой вкладки.

 

 

9

 

 

8. Во вкладке Главная в группе Ш рифт нажмите кнопку П о л у жир-

 

н ы й (Ж). Убедитесь, что кнопка осталась нажатой.

 

9. Во вкладке Главная в группе Ш рифт щелкните по стрелке кнопки

 

З аливка и выберите красный цвет. Убедитесь, что для выделенной

 

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

 

10.Во вкладке Главная в группе Ш р и ф т в раскрывающемся списке

 

Р азмер шрифта выберите размер 26. Убедитесь, что высота выде-

 

ленной ячейки увеличилась.

 

11.Во вкладке Главная в группе С т и л и щелкните по кнопке Стили

 

я чеек и выберите стиль Х ороший. Убедитесь, что оформление выде-

 

ленной ячейки изменилось.

 

12.Во вкладке Главная отобразите диалоговое окно группы Ш р и ф т.

 

Закройте диалоговое окно Ш р и ф т.

 

13.Во вкладке Главная отобразите всплывающую подсказку для кноп-

 

 

ки Перенос т екста  .

 

З адание 2.

 

1. На Листе 1 в ячейку А1 введите текст Товары.

 

2. В ячейку В1 введите текст Количество.

 

3. В ячейку С1 введите в две строки текст Дата поступления.

 

4. В ячейку D1 введите текст Время.

 

5. В ячейку А2 введите текст Яблоки.

 

6. В ячейку А3 введите текст Груши.

 

7. Ячейку А4 заполните содержимым ячейки А3.

 

8. В ячейки А5:А6 одновременно введите текст: Персики.

 

 

15

 

 

9. В ячейку А7 с использованием автозаполнения введите текст Ябло-

 

ки.

 

10.В ячейки В2:В7 введите числа: 12; 14,7; 9; 0; 0,17; 50.

 

11.В ячейку С2 введите дату 01.10.2007.

 

12.С использованием автозаполнения заполните ячейки С3:С7 после-

 

дующими датами.

 

13.В ячейки D2:D7 одновременно введите текущую дату комбинацией

 

клавиш C trl + S hift + 4 и текущее время комбинацией клавиш Ctrl +

 

S h ift + 6.

 

14.Перейдите к листу Л ист 2.

 

15.С использованием автозаполнения заполните ячейки А2:А24 после-

 

довательными числами.

 

16.С использованием автозаполнения заполните ячейки С2:С24 после-

 

дующими датами.

 

17.С использованием автозаполнения заполните ячейки D2:D24 после-

 

дующими днями недели.

 

 

Л а б ораторная работа 2. Создание и форматирование

 

Т а б л иц.

 

Цель работы:

 

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

 

2. Освоение навыков практической работы по созданию, редактированию

 

и форматированию электронных таблиц

 

3. Выполнение простейших вычислений в таблицах Excel. Знакомство с

 

элементарными функциями.

 

4. Использование абсолютной и относительной адресации ячеек в фор-

 

мулах.

 

5. Контроль правильности введенных ранее значений с помощью ус-

 

ловного форматирования

 

Существует два способа организации данных на листе: таблица и

 

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

 

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

 

пересечении строки и столбца помещаются данные (рис.2.1).

 

Рис. 2.1. Табличный способ организации данных.

 

Таблицы могут иметь весьма сложную структуру с несколькими

 

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

 

 

анализировать ее данные и управлять ими независимо от данных за преде-

 

лами таблицы. На листе можно создать любое количество таблиц.

 

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

 

данных на листе, для быстрой сортировки, отбора, суммирования, графи-

 

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

 

держащихся в ней.

 

Создание и ф о р м а т и р о в ание т аблицы:

 

Таблица обычно создается на основе имеющихся на листе данных.

 

1. Выделите любую ячейку в диапазоне данных.

 

2. Нажмите кнопку Форматировать как т аблицу в группе Стили

 

вкладки Главная и выберите стиль оформления.

 

3. В поле окна Ф орматирование т аблицы будет автоматически указан

 

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

 

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

 

ячеек с данными, которые оформляются в виде таблицы. Нажмите

 

кнопку ОК.

 

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

 

добавляется значок автофильтра. Автоматически будет отображена кон-

 

текстная вкладка Р абота с т аблицами/Конструктор (рис.2.2.).

 

 

Рис. 2.2. Контекстная вкладка Р абота с т аблицами/Конструктор.

 

 

23

 

 

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

 

ню кнопки «Вставить», которая расположена в группе «Ячейки» на вклад-

 

ке «Главная».

 

Рис. 2.3. Меню кнопки Вс т авить.

 

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

 

ки диалогового окна Формат я чеек, а также элементы группы Ш рифт

 

вкладки Главная, мини-панель инструментов.

 

,

 

Рис. 2.4. Мини-панель инструментов для форматирования.

 

При необходимости можно выполнить обрамление таблицы, пред-

 

 

варительно ее выделив и нажав кнопку    , для выделенного диапазона

 

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

 

ние, шрифт, границы и т.д. Ширину столбца можно изменить, перетащив

 

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

 

щей подсказке отображается устанавливаемая ширина столбца (в знаках и

 

пикселях).

 

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

 

ки строки с заголовком по ширине таблицы и нажать кнопку О бъединить

 

24

 

 

помещенную на вкладке Главная в группе Выравнивание

 

 

Рис. 2.5. Элементы группы В ы равнивание на вкладке Главная.

 

Для выполнения необходимых расчетов в таблицах используют фор-

 

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

 

работе в любой вкладке Excel. С использованием клавиатуры вводят опе-

 

раторы (знаки действий), константы, скобки и, иногда, функции. С исполь-

 

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

 

мулу.

 

Порядок действий:

 

1. Выделите ячейку, в которую требуется ввести формулу.

 

2. Введите = (знак равенства).

 

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

 

4. Введите знак оператора.

 

5. Выделите мышью ячейку, являющуюся вторым аргументом форму-

 

лы.

 

6. При необходимости продолжайте ввод знаков операторов и выделе-

 

ние ячеек.

 

7. Подтвердите ввод формулы в ячейку: нажмите клавишу Enter или

 

T ab или кнопку В в о д (галочка) в строке формул.

 

У д аление ф орматирования.

 

Можно удалить сразу все параметры оформления (числовые форматы,

 

параметры выравнивания, параметры шрифта, заливки, границы и т. д.).

 

Выделите ячейку или диапазон ячеек, для которых удаляется оформ-

 

ление.

 

В группе Редактирование вкладки Главная щелкните по кнопке

 

Очистить и выберите команду Очистить форматы.

 

Рис.2.6. Элементы группы Редактирование вкладки Главная.

 

11. Сохраните результаты работы в своей папке.

 

М ат

 

3. Выполните одно из следующих действий:

 

В группе В и димость выделите пункт Скрыть или

 

отобразить, а затем выберите команду С к ры т ь с т р о к и или

 

Скрыть с т о л б ц ы.

 

 

33

 

 

В группе Р азмер я чейки щелкните пункт В ы с ота

 

строки или Ш и рина с т о л бца, а затем введите «0» в поле В ы -

 

сота с т р оки или Ш и рина с т о л б ц а.

 

4. Можно щелкнуть правой кнопкой строку или столбец (либо

 

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

 

Скрыть.

 

Требуется:

 

1. Наложить на С1:С15 условный формат: если число лежит в диапазо-

 

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

 

не от 20 до 40, то выводится полужирным шрифтом в рамке; если

 

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

 

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

 

формат ячеек в зависимости от выводимого значения.

 

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

 

D1:D15, E1:E15, F1:F15, G1:G15.

 

3. С использованием условного форматирования в столбце D выделите

 

10 ячеек с максимальными значениями

 

4. Примените условное форматирование с использованием гистограмм

 

для ячеек столбца Е.

 

5. Примените условное форматирование с использованием цветовых

 

схем для ячеек столбца F

 

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

 

ячеек столбца G.

 

З адание 4. Откройте файл Tabl2.xls.

 

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

 

заливки ячеек поля «Сумма на 1.12.2009»:

 

– зеленым цветом, если сумма равна нулю;

 

– красным цветом, если сумма больше нуля;

 

 

34

 

 

– желтым цветом, если сумма меньше нуля.

 

2. Скопируйте таблицу на второй лист. Удалите ранее созданные ус-

 

ловия.

 

3. Обеспечьте возможность заливки зеленым цветом записей, значе-

 

ния в которых отвечают следующему условию: значение в поле «Сумма на

 

1.11.2009» не меньше среднего значения по данному полю.

 

4. Выполните пункт 3, учитывая дополнительное условие, что значе-

 

ние в поле «Сумма на 1.12.2009» должно быть положительным.

 

5. Сохраните выполненное задание.

 

З адание 5. Построить таблицу расчета размера платы за электро-

 

энергию в течение 12 месяцев по значениям показаний счетчика в конце

 

каждого месяца и стоимости одного киловатт-часа энергии. Числовые дан-

 

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

 

З адание 6. Для составления налоговой карточки нужно внести в

 

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

 

с начала года.

 

янв.  фев. мар.  ... дек.    Итого Доход      500.00               500.00...  500.00 4500.00 с нач. года 500.00 500.00 1000.00... 4500.00 4500.00

 

 

Если месячный доход отсутствовал, то отображать доход по

 

нарастающей за этот месяц не нужно. Сконструируйте для этой цели

 

условный формат.

 

35

 

З адание 1.

 

1. Создайте таблицу.

 

А         В      С   D   E    F   G      H       I 1               Продажа компьютеров крупнейшими производителями

2 Название Средняя    Объем продаж по годам (млн. шт.)

3 фирмы  Цена тыс.руб.

 

2005 2006 2007 2008 2009 Всего  Доход продано

 

4 IBM      20,01   6,2 5  NEC       12,073  2

6 Dell       18        4,8 7  Compaq  15         3,5 9  Gatewey  21         1,1

6,9 7,2 7,6 7,9 2,1  2,5  3,2  4,1 5,11  5,5  5,7  6,2 4,2  5,0  6,0  7,5 1,8  2,4  3,0  3,3

 

10 Итого

 

 

43

 

 

2. В ячейке Н4 рассчитайте сумму ячеек С4:G4.

 

3. В ячейке I4 рассчитайте произведение ячеек В4 и H4. Присвойте

 

формуле имя «Доход».

 

4. В ячейке C10 с использованием кнопки (Сумма) рассчитайте сум-

 

му ячеек C4:C9.

 

5. В ячейке B10 с использованием кнопки (Сумма) рассчитайте сред-

 

нее значение ячеек B4:B9.

 

6. Скопируйте формулу ячейки H4 на ячейки H5:H9.

 

7. Скопируйте формулу ячейки I4 на ячейки I5:I9.

 

8. Скопируйте формулу ячейки C10 на ячейки D10:I10.

 

9. Отредактируйте формулу в ячейке B10: среднее значение должно

 

быть рассчитано для ячеек B4:B7.

 

10.В ячейке А11 введите текст «курс доллара», а в ячейке А12 укажите

 

значение курса доллара по отношению к рублю на текущую дату.

 

11.Присвойте ячейке А12 имя «Курс_доллара».

 

12.В ячейке J4 рассчитайте частное от деления ячейки I4 на ячейку A12

 

так, чтобы эту формулу можно было копировать на ячейки J5:J10.

 

13.Удалите из книги имя ячейки Доллар.

 

14.Сохраните файл под именем «Продажа_компьютеров.xlsx».

 

Все функции на вкладке «Ф о р м улы» распределяются по темам на сле-

 

дующие группы:

 

«М атематические». Предназначены для решения алгебраических

 

задач: функции для округления данных, тригонометрические и т. д.

 

«Логические». Применяются для решения задач с условиями.

 

«Финансовые». Применяются для выполнения финансовых расче-

 

тов.

 

«Текстовые». Предназначены для работы с текстовыми значениями.

 

 

44

 

 

«Даты и в ремени». Применяются для работы с данными в формате

 

«Дата/время».

 

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

 

хранящейся в больших списках и таблицах.

 

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

 

Округление чисел особенно часто требуется при денежных расчетах.

 

Например, цену товара в рублях, как правило, нельзя устанавливать с точ-

 

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

 

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

 

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

 

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

 

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

 

Наиболее часто используют функции ОКРУГЛ, ОКРУГЛВВЕРХ и

 

ОКРУГЛВНИЗ.

 

Синтаксис функции ОКРУГЛ(А;В), где A – округляемое число; В –

 

число знаков после запятой (десятичных разрядов), до которого округляет-

 

ся число. Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно та-

 

кой же, что и у функции ОКРУГЛ. Функция ОКРУГЛ при округлении от-

 

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

 

разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округля-

 

ет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбра-

 

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

 

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

 

значения аргумента В.

 

Для округления чисел в меньшую сторону можно использовать так-

 

же функцию ОТБР.

 

 

45

 

 

Синтаксис функции ОТБР(А;В), где A – округляемое число; В – чис-

 

ло знаков после запятой (десятичных разрядов), до которого округляется

 

число.

 

Фактически функция ОТБР отбрасывает лишние знаки, оставляя только

 

количество знаков, указанное в аргументе В.

 

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

 

функцию ЦЕЛОЕ(А), где A – округляемое число.

 

Наконец, для округления до ближайшего четного или нечетного числа

 

можно использовать функции ЧЕТН(А) и НЕЧЕТН(А), а для ближайшего

 

кратного большего или меньшего числа – функции ОКРВЕРХ и

 

ОКРВНИЗ.

 

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

 

можно использовать функцию ABS(А), где А – число, для которого опре-

 

деляется абсолютное значение.

 

Простоесумм ирование содержимого заданного интервала ячеек

 

осуществляется функцией СУММ(А), где A – список от 1 до 30 элементов,

 

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

 

ном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые

 

или логические значения игнорируются.

 

Фактически данная функция заменяет непосредственное суммирование с

 

использованием оператора сложения (+). Формула =СУММ(В2:В7), тож-

 

дественна формуле =В2+В3+В4+В5+В6+В7. Однако есть и некоторые от-

 

личия. При использовании функции СУММ добавление ячеек в диапазон

 

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

 

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

 

диапазон суммирования. Аналогично формула будет изменяться и при

 

уменьшении диапазона суммирования.

 

 

46

 

 

Для ум ножения используют функцию ПРОИЗВЕД(А), где A – список

 

от 1 до 30 элементов, которые требуется перемножить. Элемент может

 

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

 

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

 

Фактически данная функция заменяет непосредственное умножение с

 

использованием оператора умножения (*). Так же как и при использовании

 

функции СУММ, при использовании функции ПРОИЗВЕД добавление

 

ячеек в диапазон перемножения автоматически изменяет запись диапазона

 

в формуле. Например, если в таблицу вставить строку, то в формуле будет

 

указан новый диапазон перемножения. Аналогично формула будет изме-

 

няться и при уменьшении диапазона.

 

З адание 2.

 

1. Создать новую книгу. Сохранить ее на диске в своей папке с именем

 

«Ассортимент».

 

2. На первом листе книги набрать данные таблицы 1, приведенной ниже.

 

A          B                          C                           D         E     F       G

 

1 Товар  Модель            Название          СтоимостьЦенаКол-воСумма 2 Ксерокс С100 GLSПерсональный                         5270р.             564

3 Ксерокс С110 GLSПерсональный                          6939р.             632 4 Ксерокс С200 GLSПерсональный Плюс              4300р.             438 5 Ксерокс С210 GLSПерсональный Плюс              7168р.             645 6 Ксерокс С300 GLSДеловой                                    8700р.             437 7 Ксерокс С310 GLSДеловой                                    9650р.             534 8 Ксерокс С400 GLSПрофессиональный              12799р.             409 9 Ксерокс С410 GLSПрофессиональный              11250р.             395 10 Ксерокс С500 GLSПрофессиональный              10415р.             298 11 Ксерокс С510 GLSПрофессиональный Плюс   13789р.             328 12 Ксерокс С520 GLSПрофессиональный Плюс   16080р.             567

 

 

У к азание: При вводе данных используйте Выпадающие списки (Один

 

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

 

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

 

нажать сочетание клавиш Alt+стрелка в н и з).

 

47

 

 

3. На втором листе текущей рабочей книги создать таблицу 2:

 

A         B                     C                           D            E       F        G

 

1 Товар Модель Название                Стоимость  Цена Кол-во Сумма

2 Факсы F100 G 3  Факсы F150 G 4  Факсы F200 G 5  Факсы F250 G 6  Факсы F300 G 7  Факсы F350 G 8  Факсы F400 G

9 Факсы F450 G

Персональный                        5840р.                420 Персональный                       7300р.                634 Персональный Плюс            3760р.                432 Персональный Плюс            5999р.                297 Деловой                                  8610р.                437 Деловой                                  5199р.                234 Профессиональный            13150р.                289

Профессиональный             14780р.                211

 

10 Факсы F500 G Профессиональный             15614р.                108

 

 

4. Листам дать название «Ксероксы» и «Факсы» соответственно.

 

5. На листах «Ксероксы» и «Факсы» в столбиках «Цена», используя абсо-

 

лютную ссылку, создать формулу для вычисления Цены, при условии,

 

что она на 30% больше стоимости.

 

6. На листах «Ксероксы» и «Факсы» в столбцах «Сумма» создать формулу

 

=количество*цена;

 

7. На листах «Ксероксы» и «Факсы» отформатировать таблицы:

 

шрифт – Times New Roman; размер 13; задать границы – тон-

 

кая пунктирная линия;

 

в столбцах «Товар», «Модель», «Название» установить вырав-

 

нивание по левому краю;

 

в столбцах «Стоимость», «Цена», «Количество», «Сумма» ус-

 

тановить выравнивание по центру;

 

в ячейках, содержащих денежные суммы, установить денеж-

 

ный формат.

 

8. Над таблицами добавить строку, в столбце А1 набрать «Курс $», в

 

ячейке В1 набрать текущий курс доллара и установить денежный фор-

 

мат. Присвоить этой ячейке имя «Курс_$».

 

 

48

 

 

9. к таблицам добавить столбец с названиями «Стоимость в у.е.», где соз-

 

дать формулу с абсолютной ссылкой =D3/Курс_$.

 

10.В Столбце «Стоимость в у.е.» установить форматирование как в преды-

 

дущих столбцах, воспользовавшись кнопкой «Формат по образцу».

 

11.Над таблицами добавить три строки в первой строке объединить столб-

 

цы, которые занимает ваша таблица, и набрать в объединенной ячейке

 

«Ассортимент ксероксов фирмы «Флагман», «Ассортимент факсов

 

фирмы «Флагман».

 

12.Используя «Буфер обмена», скопировать обе таблицы на третий лист

 

текущей книги и назвать его «Полный ассортимент».

 

13.На листе «Полный ассортимент», используя У словное ф о р м а т и р ова-

 

н и е, выделить те суммы, величина которых от 6000 руб. до 450$.

 

14.В ячейке E2 округлите указанную там цену товара до ближайшего цело-

 

го. Скопируйте формулу на ячейки Е3:Е22.

 

15.В основной таблице под столбиками «Цена», «Количество», «Сумма»

 

найти: минимальный элемент, максимальный элемент и среднее значе-

 

ние.

 

16.Вычислите итоговое значение столбцов Количество и Сумма.

 

17.Сохраните файл под именем «Ксероксы и факсы».

 

 

49

 

A                 B                                    C

 

1                         Налоги н а д оходы ф и з и ческих лиц 2 размер налога                 13%

необлагаемая база

 

3

для лиц, имеющих доход    меньше

 

400руб.

 

20000 рублей

 

Ф амилия

 

5 Белоус С.В.

 

Доход                                Налог

 

20050    ЕСЛИ(В5<20000; (В5-$B$3)*$B$2; B5*$B$3)

 

54

 

 

 

6 Котощук Л.В.    15000 7 Ха<