Лабораторная работа 2. Организация вычислений в табличном процессоре MS Excel 2010

Цель работы:

1. Освоение навыков практической работы по созданию и редактированию формул с использованием мастера функций и кнопки "Сумма".

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

3. Знакомство с особенностями использования некоторых стандартных функций Excel: функций преобразования и округления чисел, статистических функций для расчета средних значений, поиска наибольших и наименьших значений, расчета количества ячеек, выборочного суммирования.

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

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

 

Рисунок 2.1. – Вкладка Формулы.

 

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

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

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

3. В окне Аргументы функции в соответствующем поле (полях) введите аргументы функции. Ссылки на ячейки можно вводить с клавиатуры, но удобнее пользоваться выделением ячеек мышью. Для этого поставьте курсор в соответствующее поле и на листе выделите необходимую ячейку или диапазон ячеек. Для удобства выделения ячеек окно Аргументы функции можно сдвинуть или свернуть. Текст, числа и логические выражения в качестве аргументов обычно вводят с клавиатуры. В качестве подсказки в окне отображается назначение функции, а в нижней части окна отображается описание аргумента, в поле которого в данный момент находится курсор. Следует иметь в виду, что некоторые функции не имеют аргументов.

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

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

Рисунок 2.2

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

1. В ячейку или в строку формул введите знак "=" (знак равенства) и первые буквы используемой функции. По мере ввода список прокрутки возможных элементов отображает наиболее близкие значения. Значки указывают типы вводимых данных, такие как функция или ссылка на таблицу (рис. 2.3.).

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

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

Для быстрого выполнения некоторых действий с применением функций без запуска мастера функций можно использовать кнопку Сумма. Эта кнопка, помимо группы Библиотека функций вкладки Формулы (там она называется Автосумма), имеется также в группе Редактирование вкладки Главная. Кроме вычисления суммы чисел в ячейках, кнопку Сумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений.

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

 

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

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

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

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

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

 

Перемещать и копировать ячейки с формулами можно точно так же, как и ячейки с текстовыми или числовыми значениями. Кроме того, при копировании ячеек с формулами можно пользоваться возможностями специальной вставки. Это позволяет копировать только формулу без копирования формата ячейки. При перемещении ячейки с формулой содержащиеся в формуле ссылки не изменяются. При копировании формулы ссылки на ячейки могут изменяться в зависимости от их типа (относительные или абсолютные). По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы. В некоторых случаях использование относительных ссылок недопустимо. Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ – служебный символ, показывающий абсолютную ссылку.

Чтобы ссылка на ячейку была абсолютной, после указания ссылки на ячейку следует нажать клавишу F4. Ссылку можно преобразовать из относительной в абсолютную и при редактировании ячейки с формулой. К заголовкам столбца и строки в адресе ячейки следует добавить служебный символ $. Например, для того чтобы ссылка на ячейку А20 стала абсолютной, необходимо ввести $А$20. Ссылка может быть не только относительной или абсолютной, но и смешанной. Ссылка формата A$1 является относительной по столбцу и абсолютной по строке, т.е. при копировании ячейки с формулой выше или ниже, ссылка изменяться не будет. А при копировании влево или вправо будет изменяться заголовок столбца. Ссылка формата $A1 является относительной по строке и абсолютной по столбцу, т.е. при копировании ячейки с формулой влево или вправо выше или ниже ссылка изменяться не будет. А при копировании выше или ниже будет изменяться заголовок строки. Абсолютную адресацию в формулах обеспечивает также применение имен ячеек и их диапазонов. Например, формула =Март+Апрель+Май предполагает сложение данных, содержащихся в ячейках с соответствующими именами (вне зависимости от того, в каком месте книги находится именованные диапазоны и формула, которая на них ссылается). Имя – слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу. Имена можно использовать в любом листе книги.

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

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

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

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

3. В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 2.4.).

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

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

Рисунок 2.4. - Присваивание имени ячейке.

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

· В именах можно использовать только буквы (в том числе русского алфавита), знак подчеркивания, точки и цифры. Имя может содержать до 255 знаков. Имя может состоять из строчных и прописных букв, но Excel их не различает;

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

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

· не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1;

· в имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2010 или Год.2010;

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

Имя можно присвоить формуле или постоянному значению (константе). Например, имя "Скидка", которому присвоено значение 33 процента, можно использовать в любом месте для вычисления цены со скидкой. Для присвоения имени формуле или константе в поле Диапазон окна Создание имени следует ввести знак равенства (=) и формулу или константу (рис. 2.5.).

Рисунок 2.5. - Присвоение имени константе.

Присвоенные имена можно использовать в формулах. При создании формул короткие имена можно вводить с клавиатуры. Во избежание возможных ошибок при использовании имен в процессе создания формулы следует в группе Определенные имена вкладки Формулы щелкнуть кнопку Использовать в формуле и выбрать нужное имя в списке имен (рис. 2.6.).

 

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

Если нужное имя не отображается в списке, выберите команду Вставить имена (см. рис. 2.6.), а затем в окне Вставка имени выберите вставляемое имя. Ненужное или ошибочное имя можно удалить:

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

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

 

Задание 1.

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

 

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».

 

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

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

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

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

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

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

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

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

Округление чисел особенно часто требуется при денежных расчетах. Например, цену товара в рублях, как правило, нельзя устанавливать с точностью более двух знаков после запятой. Если же в результате вычислений получается большее число десятичных разрядов, требуется округление. В противном случае накапливание тысячных и десятитысячных долей рубля приведет в итоге к ошибкам в вычислениях. Для округления чисел можно использовать целую группу функций. Наиболее часто используют функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ. Синтаксис функции ОКРУГЛ(А;В), где A – округляемое число; В – число знаков после запятой (десятичных разрядов), до которого округляется число. Синтаксис функций ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ точно такой же, что и у функции ОКРУГЛ. Функция ОКРУГЛ при округлении отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда. Функция ОКРУГЛВВЕРХ при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ при округлении отбрасывает любые цифры. Эти функции можно использовать и для округления целых разрядов чисел. Для этого необходимо использовать отрицательные значения аргумента В. Для округления чисел в меньшую сторону можно использовать также функцию ОТБР.

Синтаксис функции ОТБР(А;В), где A – округляемое число; В – число знаков после запятой (десятичных разрядов), до которого округляется число. Фактически функция ОТБР отбрасывает лишние знаки, оставляя только количество знаков, указанное в аргументе В. Для округления числа до меньшего целого можно использовать функцию ЦЕЛОЕ(А), где A – округляемое число. Наконец, для округления до ближайшего четного или нечетного числа можно использовать функции ЧЕТН(А) и НЕЧЕТН(А), а для ближайшего кратного большего или меньшего числа – функции ОКРВЕРХ и ОКРВНИЗ. Для преобразования результатов вычислений в положительное число можно использовать функцию ABS(А), где А – число, для которого определяется абсолютное значение.

Простое суммирование содержимого заданного интервала ячеек осуществляется функцией СУММ(А), где A – список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула =СУММ(В2:В7), тождественна формуле =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Для умножения используют функцию ПРОИЗВЕД(А), где A – список от 1 до 30 элементов, которые требуется перемножить. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения (*). Так же как и при использовании функции СУММ, при использовании функции ПРОИЗВЕД добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона.

Задание 2.

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

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

 

Указание: При вводе данных используйте Выпадающие списки (Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка или нажать сочетание клавиш Alt+стрелка вниз).

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

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

5. На листах «Ксероксы» и «Факсы» в столбиках «Цена», используя абсолютную ссылку, создать формулу для вычисления Цены, при условии, что она на 30% больше стоимости.

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

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

шрифт – Times New Roman; размер 13; задать границы – тонкая пунктирная линия;

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

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

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

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

набрать текущий курс доллара и установить денежный фор-мат. Присвоить этой ячейке имя «Курс_$».

9. к таблицам добавить столбец с названиями «Стоимость в у.е.», где создать формулу с абсолютной ссылкой =D3/Курс_$.

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

11. Над таблицами добавить три строки в первой строке объединить столбцы, которые занимает ваша таблица, и набрать в объединенной ячейке «Ассортимент ксероксов фирмы «Флагман», «Ассортимент факсов фирмы «Флагман».

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

13. На листе «Полный ассортимент», используя Условное форматирование, выделить те суммы, величина которых от 6000 руб. до 450$.

14. В ячейке E2 округлите указанную там цену товара до ближайшего целого. Скопируйте формулу на ячейки Е3:Е22.

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

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

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