Т а б л ичном п роцессоре MS E x c e l 2007 .

 

Цель работы:

 

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

 

нию формул с использованием мастера функций и кнопки "Сумма".

 

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

 

мулах при перемещении и копировании формул. Использование ссы-

 

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

 

3. Знакомство с особенностями использования некоторых стандартных

 

функций Excel: функций преобразования и округления чисел, стати-

 

стических функций для расчета средних значений, поиска наиболь-

 

ших и наименьших значений, расчета количества ячеек, выборочного

 

суммирования.

 

4. Закрепление материала предыдущих лабораторных работ.

 

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

 

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

 

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

 

ные или сложные. Функции используют не только для непосредственных

 

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

 

ния, для поиска значений, сравнения и т. д.

 

Для создания формул с функциями обычно используют группу Б и б-

 

л и отека функций вкладки Ф ормулы.

 

Рис. 3.1. Вкладка Ф о р м улы.

 

 

36

 

 

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

 

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

 

2. Щелкните по кнопке нужной категории функций в группе Б и блио-

 

т ека функций и выберите нужную функцию.

 

3. В окне Аргументы функции в соответствующем поле (полях) вве-

 

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

 

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

 

этого поставьте курсор в соответствующее поле и на листе выделите

 

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

 

ячеек окно А ргументы функции можно сдвинуть или свернуть.

 

Текст, числа и логические выражения в качестве аргументов обычно

 

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

 

значение функции, а в нижней части окна отображается описание ар-

 

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

 

ет иметь в виду, что некоторые функции не имеют аргументов.

 

4. В окне Аргументы функции нажмите кнопку ОК.

 

Для выбора нужной функции можно использовать М астер функций

 

при работе в любой вкладке (рис. 3.2.).

 

 

37

 

 

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

 

Для упрощения процесса создания и снижения количества опечаток ис-

 

пользуйте автозавершение формул:

 

1. В ячейку или в строку формул введите знак "=" (знак равенства) и

 

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

 

крутки возможных элементов отображает наиболее близкие значе-

 

ния. Значки указывают типы вводимых данных, такие как функция

 

или ссылка на таблицу (рис. 3.3.).

 

Рис. 3.3. Ввод формулы с использованием автозавершения.

 

2. Выберите нужную функцию, для чего дважды щелкните по ней мы-

 

шью.

 

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

 

Подтвердите ввод формулы.

 

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

 

функций без запуска мастера функций можно использовать кнопку Сум-

 

м а. Эта кнопка, помимо группы Б и блиотека функций вкладки Формулы

 

 

38

 

 

(там она называется А втосумма), имеется также в группе Р е д а к т и р ова-

 

н и е вкладки Главная.

 

Кроме вычисления суммы чисел в ячейках, кнопку Сумма можно ис-

 

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

 

числовых значений, нахождения максимального и минимального значе-

 

ний. В этом случае необходимо щелкнуть по стрелке кнопки и выбрать не-

 

обходимое действие:

 

С реднее – расчет среднего арифметического;

 

Число – определение количества численных значений;

 

М аксимум – нахождение максимального значения;

 

М инимум – нахождение минимального значения.

 

Перемещать и копировать ячейки с формулами можно точно так же,

 

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

 

Кроме того, при копировании ячеек с формулами можно пользоваться

 

возможностями с п ециальной в ставки. Это позволяет копировать только

 

формулу без копирования формата ячейки.

 

При перемещении ячейки с формулой содержащиеся в формуле ссыл-

 

ки не изменяются. При копировании формулы ссылки на ячейки могут из-

 

меняться в зависимости от их типа (относительные или абсолютные).

 

По умолчанию ссылки на ячейки в формулах относительные, то есть

 

адрес ячейки определяется на основе расположения этой ячейки относи-

 

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

 

тельная ссылка автоматически изменяется. Именно возможность использо-

 

вания относительных ссылок и позволяет копировать формулы. В некото-

 

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

 

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

 

пользовать абсолютные сс ылки. Абсолютная ссылка ячейки имеет фор-

 

мат $A$1, где $ – служебный символ, показывающий абсолютную ссылку.

 

 

39

 

 

Чтобы ссылка на ячейку была абсолютной, после указания ссылки на

 

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

 

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

 

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

 

символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолют-

 

ной, необходимо ввести $А$20.

 

Ссылка может быть не только относительной или абсолютной, но и

 

смешанной.

 

Ссылка формата A$1 является относительной по столбцу и абсолют-

 

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

 

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

 

изменяться заголовок столбца.

 

Ссылка формата $A1 является относительной по строке и абсолютной

 

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

 

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

 

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

 

Абсолютную адресацию в формулах обеспечивает также применение

 

и м енячеекиихд и апазонов. Например, формула =Март+Апрель+Май

 

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

 

щими именами (вне зависимости от того, в каком месте книги находится

 

именованные диапазоны и формула, которая на них ссылается).

 

Имя – слово или строка знаков, представляющих ячейку, диапазон

 

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

 

книги.

 

Присваивание и м ени я чейке и л и д и апазону я чее к:

 

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

 

2. В группе О пределенные и м е н а вкладки Ф ормулы нажмите кнопку

 

Присвоить и м я.

 

 

40

 

 

3. В окне С о з д ание и м е н и в поле Имя введите имя ячейки или диапа-

 

зона (рис. 3.4.).

 

4. Для задания области действия имени в поле со списком Область вы-

 

берите К н ига или имя листа в книге.

 

5. При желании в поле П римечание можно ввести примечание к име-

 

ни, которое затем будет отображаться в окне Диспетчера и мен.

 

 

Рис. 3.4. Присваивание имени ячейке.

 

При задании имен следует соблюдать некоторые правила:

 

В именах можно использовать только буквы (в том числе русско-

 

го алфавита), знак подчеркивания, точки и цифры. Имя может содержать

 

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

 

Excel их не различает;

 

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

 

вания;

 

пробелы не допускаются;

 

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

 

на ячейки, например Z$100 или R1C1;

 

41

 

 

в имени может быть больше одного слова. В качестве разделите-

 

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

 

мер: Год_2010 или Год.2010;

 

при именовании выделенного диапазона в качестве его имени

 

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

 

выше диапазона.

 

Имя можно присвоить формуле или постоянному значению (кон-

 

станте). Например, имя "Скидка", которому присвоено значение 33 про-

 

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

 

кой. Для присвоения имени формуле или константе в поле Д и апазон окна

 

Создание и ме н и следует ввести знак равенства (=) и формулу или кон-

 

станту (рис. 3.5.).

 

 

Рис. 3.5. Присвоение имени константе.

 

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

 

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

 

можных ошибок при использовании имен в процессе создания формулы 42

 

 

следует в группе О пр е д еленные и ме н а вкладки Формулы щелкнуть

 

кнопку Использовать в ф ормуле и выбрать нужное имя в списке имен

 

(рис. 3.6.).

 

 

Рис. 3.6. Вставка имени в формулу.

 

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

 

имена (см. рис. 3.6.), а затем в окне Вс т авка и м е ни выберите вставляемое

 

имя.

 

Ненужное или ошибочное имя можно удалить:

 

1. В группе О пределенные и м е н а вкладки Ф ормулы нажмите кнопку

 

Диспетчер и ме н.

 

2. В окне Диспетчер и ме н выделите имя и нажните кнопку У д алить.

 

З адание 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