Лабораторная работа № 6

Лабораторная работа 6. Excel Основы вычислений Создание формул с использованием мастера функций

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

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

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

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

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

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

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

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

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

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

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

Создание формул с использованием кнопки "Сумма"

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

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

Для подтверждения ввода формулы следует нажать клавишу Enter или еще раз нажать кнопку Сумма .

Для вычисления суммы произвольно расположенных ячеек следует выделить ячейку, в которой должна быть вычислена сумма, нажать на кнопку Сумма , а затем на листе выделить суммируемые ячейки и/или диапазоны ячеек. Для подтверждения ввода формулы следует нажать клавишу Enter или еще раз нажать кнопку Сумма .

Синтаксис функции

СУММ(А) ,

где А – список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

В новой книге введите данные по успеваемости.

Вычислите сумму баллов для каждого студента.

Кроме вычисления суммы, кнопку Сумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений. В этом случае необходимо щелкнуть по стрелке кнопки и выбрать необходимое действие:

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

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

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

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

Целью лабораторной работы является изучение и закрепление навыков работы по вводу данных и использованию формул в Microsoft Excel 2007 .

Ввод данных в электронную таблицу

В ячейках электронной таблицы могут находиться данные трех типов: числовые значения (включая время и дату), текст, формулы. На рабочем листе, но в «графическом слое» поверх листа, могут также находиться рисунки, диаграммы, изображения, кнопки и другие объекты.

Ввод чисел

Числа вводятся с помощью верхнего ряда клавиатуры или числовой клавиатуры. В качестве десятичного разделителя применяется запятая или точка, можно вводить знаки денежных единиц. Если перед числом ввести «минус» или скобки, то оно считается отрицательным. Нули, набранные перед числом, игнорируются программой. Если необходимо получить значение с нулями впереди, его необходимо интерпретировать как текстовое.

Для представления чисел в Excel используется 15 цифр, при вводе числа из 16 цифр оно автоматически сохранится с точностью до 15 цифр. Числовые значения автоматически выравниваются по правой границе ячейки.

Ввод значений дат и времени

Excel для представления дат использует внутреннюю систему порядковой нумерации дат. (Так, самая ранняя дата, которую может распознать программа, – 1 января 1900 года, этой дате присвоен порядковый номер 1, следующей дате – порядковый номер 2 и т. д.). Даты вводятся в привычном для пользователя формате и распознаются автоматически. Временные значения также вводятся в одном из распознаваемом форматов времени. Представление даты и времени непосредственно на листе регулируется заданием формата отображения ячейки.

Ввод текста

Как текстовые значения воспринимаются все введенные данные, не распознаваемые как числа или формулы. Текстовые значения выравниваются по левой границе таблицы. Если текст не умещается в одной ячейке, то он располагается поверх соседних ячеек, если они свободны. Параметры расположения текста в ячейке задаются через формат ячеек.

Ввод формулы

Формулой считается любое математическое выражение. Формула всегда начинается со знака «=», может включать в себя, кроме операторов и ссылок на ячейки, встроенные функции Excel.

Форматы данных

После ввода в ячейку данных, Excel автоматически старается определить их тип и присвоить ячейке соответствующий формат – форму представления данных. Важно назначить правильный формат ячейки, чтобы, например, ячейка могла участвовать в вычислениях (быть не текстовой).

В Excel имеется набор стандартных форматов ячеек, которые могут применяться во всех книгах (рисунок 2.2.17). Активизировать его можно, выбрав Главная – Число – Числовой формат, либо по контекстному меню для выделенной ячейки на вкладке Число окна Формат ячеек.

Рисунок 2.2.17. Стандартные форматы

Изначально все ячейки таблицы имеют формат Общий. Использование форматов влияет на то, как будет отображаться содержимое в ячейках: общий – числа отображаются в виде целых чисел, десятичных дробей, если число слишком большое, то в виде экспоненциального; числовой – стандартный числовой формат; финансовый и денежный – число округляется до 2 знаков после запятой, после числа ставится знак денежной единицы, денежный формат позволяет отображать отрицательные суммы без знака «минус» и другим цветом; краткая дата и длинный формат даты – позволяет выбрать один из форматов дат; время – предоставляет на выбор несколько форматов времени; - процентный – число (от 0 до 1) в ячейке умножается на 100, округляется до целого и записывается со знаком %; дробный – используется для отображения чисел в виде не десятичной, а обыкновенной дроби; экспоненциальный – предназначен для отображения чисел в виде произведения двух составляющих: числа от 0 до 10 и степени числа 10 (положительной или отрицательной); текстовый – при установке этого формата любое введенное значение будет восприниматься как текстовое; дополнительный – включает в себя форматы Почтовый индекс, Индекс+4, Номер телефона, Табельный номер; все форматы – позволяет создавать новые форматы в виде пользовательского шаблона.

Использование средств, ускоряющих ввод данных

При вводе данных на листы таблицы могут быть использованы некоторые приемы, позволяющие ускорить их ввод.

1) Автозаполнение при вводе. При вводе одинаковых значений в несколько ячеек с помощью маркера автозаполнения (крестика в нижнем правом углу активной ячейки) можно скопировать значения в смежные ячейки. С помощью открывающегося контекстного меню по нажатию правой кнопки мыши после перетаскивания, можно задать дополнительные параметры автозаполнения (например, введя в ячейки числа 1 и 3, можно получить последовательность чисел с шагом 2 для выделенного диапазона ячеек).

2) Использование прогрессии. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения (получается арифметическая или геометрическая прогрессия, список дат). Чтобы задать прогрессию, нужно выбрать кнопку Заполнить панели Редактирование вкладки Главная и в появившемся диалоговом окне Прогрессия задать параметры для арифметической или геометрической прогрессии.

3) Автозавершение при вводе. При помощи этой функции можно выполнять автоматический ввод повторяющихся текстовых данных. После ввода в ячейку текста Excel запоминает его и при следующем введении после набора первых букв слова предлагает вариант для завершения ввода. Для завершения ввода необходимо нажать «Enter». Доступ к этой команде можно также получить выбрав по контекстному меню по правой кнопке мыши пункт Выбрать из раскрывающегося списка. Функция автозавершения работает только с непрерывной последовательностью ячеек.

4) Использование автозамены при вводе. Автозамена предназначена для автоматической замены одних заданных сочетаний символов на другие при вводе. Например, можно задать ввод одного символа вместо ввода нескольких слов. Команда доступна по кнопке Office – Параметры Excel. В пункте Правописание - Параметры автозамены нужно задать текст и его сокращение.

5) Использование сочетания клавиш Сtrl+Enter для ввода повторяющихся значений. Для введения одних и тех же значений в несколько ячеек можно выделить их, ввести значение в одну ячейку и нажать Сtrl+Enter. В результате одни и те же данные будут введены во все выделенные ячейки.

Проверка данных при вводе

Если необходимо быть уверенным в том, что на лист введены правильные данные, можно указать критерии, которые являются допустимыми для отдельных ячеек или диапазонов ячеек. Для задания проверки выполните команду Данные – Работа с данными – Проверка данных. В появившемся окне (рисунок 2.2.18) задайте критерии проверки на вкладке Параметры, текст сообщения-подсказки пользователю для ввода на вкладке Сообщение для ввода, текст сообщения об ошибке на вкладке Сообщение об ошибке.

После применения команды Данные – Работа с данными – Обвести неверные данные все неверные данные будут обведены красными кружками.

Рисунок 2.2.18. Окно задания параметров проверки данных

Использование формул

Под формулой в Excel понимается математическое выражение, на основании которого вычисляется значение некоторой ячейки. В формулах могут использоваться: числовые значения; адреса ячеек (относительные, абсолютные и смешанные ссылки); операторы: математические (+, -, *, /, %, ^), сравнения (=, <, >, >=, <=, < >), текстовый оператор & (для объединения нескольких текстовых строк в одну), операторы отношения диапазонов (двоеточие (:) – диапазон, запятая (,) –для объединения диапазонов, пробел – пересечение диапазонов); функции.

Ввод формулы всегда начинается со знака «=». Результат формулы отображается в ячейке, а сама формула – в строке формул. Адреса ячеек в формуле могут вводиться вручную, а могут просто с помощью щелчка мыши по нужным ячейкам.

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

Способы адресации ячеек

Адрес ячейки состоит из имени столбца и номера строки рабочего листа (например А1, BM55). В формулах адреса указываются с помощью ссылок – относительных, абсолютных или смешанных. Благодаря ссылкам данные, находящиеся в разных частях листа, могут использоваться в нескольких формулах одновременно.

Относительная ссылка указывает расположение нужной ячейки относительно активной (т. е. текущей). При копировании формул эти ссылки автоматически изменяются в соответствии с новым положением формулы (Пример записи ссылки: A2, С10).

Абсолютная ссылка указывает на точное местоположение ячейки, входящей в формулу. При копировании формул эти ссылки не изменяются. Для создания абсолютной ссылки на ячейку, поставьте знак доллара ($) перед обозначением столбца и строки (Пример записи ссылки: $A$2, $С$10). Чтобы зафиксировать часть адреса ячейки от изменений (по столбцу или по строке) при копировании формул, используется смешанная ссылка с фиксацией нужного параметра. (Пример записи ссылки: $A2, С$10).

Замечания

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

Встроенные функции Excel

Каждая функция имеет свой синтаксис и порядок действия, который нужно соблюдать, чтобы вычисления были верными. Аргументы функции записываются в круглых скобках, причем функции могут иметь или не иметь аргументы, при их использовании необходимо учитывать соответствие типов аргументов. Функция может выступать в качестве аргумента для другой функции, в этом случае она называется вложенной функцией. При этом в формулах можно использовать до нескольких уровней вложения функций.

В Excel 2007 существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию (рисунок 2.2.19)), а можно выбирать в окне Мастер функций, активируемом кнопкой на панели Библиотека функций вкладки Формулы или из групп функций на этой же панели, либо с помощью кнопки панели Редактирование вкладки Главная.

Рисунок 2.2.19. Автозаполнение формул

Формулы можно отредактировать так же, как и содержимое любой другой ячейки. Чтобы отредактировать содержимое формулы: дважды щелкните по ячейке с формулой, либо нажмите F2, либо отредактируйте содержимое в строке ввода формул.

Присвоение и использование имен ячеек

В Excel 2007 имеется полезная возможность присвоения имен ячейкам или диапазонам. Это бывает особенно удобно при составлении формул. Например, задав для какой-либо ячейки имя Итого_за_год, можно во всех формулах вместо адреса ячейки указывать это имя.

Имя ячейки может действовать в пределах одного листа или одной книги, оно должно быть уникальным и не дублировать названия ячеек. Чтобы присвоить имя ячейкам, нужно выделить ячейку или диапазон и в строке названия ввести новое имя. Либо воспользоваться кнопкой Присвоить имя панели Определенные имена вкладки Формулы и вызвать диалоговое окно (рисунок 2.2.20), чтобы задать нужные параметры.

Рисунок 2.2.20. Окно создания имени

Для просмотра всех присвоенных имен используйте команду Диспетчер имен. Также на листе можно получить список всех имен с адресами ячеек по команде Использовать в формуле – Вставить имена панели Определенные имена.

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

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

Отображение зависимостей в формулах

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

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

Зависимая ячейка – это ячейка, которая содержит формулу.

Чтобы отобразить связи ячеек, нужно выбрать команды Влияющие ячейки или Зависимые ячейки панели Зависимости формул вкладки Формулы. Чтобы не отображать зависимости, примените команду Убрать стрелки этой же панели.

Рисунок 2.2.21. Отображение влияющих ячеек

Режимы работы с формулами

В Excel установлен режим автоматических вычислений, благодаря которому формулы на листах пересчитываются мгновенно. При размещении на листе очень большого количества (до несколько тысяч) сложных формул скорость работы может заметно снизиться из-за пересчета всех формул на листе. Чтобы управлять процессом вычисления по формулам, нужно установить ручной режим вычислений, применив команду Формулы – Вычисление – Параметры вычислений – Вручную. После внесения изменений нужно вызвать команду Произвести вычисления (для пересчета данных на листе книги) или Пересчет (для пересчета всей книги) панели Вычисление.

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

Если формула возвращает ошибочное значение, Excel может помочь определить ячейку, которая вызывает ошибку. Для этого нужно активизировать команду Формулы – Зависимости формул – Проверка наличия ошибок – Источник ошибок. Команда Проверка наличия ошибок помогает выявить все ошибочные записи формул.

Для отладки формул существует средство вычисления формул, вызываемое командой Формулы – Зависимости формул – Вычислить формулу, которое показывает пошаговое вычисление в сложных формулах

Практикум:.

1. Сформировать таблицу расчета суммы ряда (варианты заданий по расчету суммы ряда – см. ниже). При формировании таблицы использовать встроенные функции, абсолютную и относительную адресацию, автозаполнение ячеек.

2. В зависимости от числа слагаемых n оформить таблицу следующим образом:

Таблица 19.

Таблица 20.

3. Используя условное форматирование, выделить отрицательные числа синим цветом, числа больше 1,5 – красным цветом.

4. Оформить таблицу. Образец оформления – ниже. Шаг изменения x в зависимости от варианта задания равен 0,1 (либо Pi/*).

5. Построить в одной координатной сетке (на одной диаграмме) графики s=f(x) и y=f(x).

6. Изучить возможности применения функций (список функций см. в варианте задания), привести пример работы каждой функции.

Таблица 21. Варианты заданий

1. Откройте табличный процессор Microsoft Excel 2007 и создайте рабочую книгу с именем Лабораторная работа №1 .

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

3. Для упрощения ввода данных в таблицу создайте раскрывающийся список (рис. 22), содержащий ФИО сотрудников предприятия.

Рис. 22. Раскрывающийся список

4. Вставьте еще один лист в рабочую книгу Excel, используя ярлычок в строке Ярлычок листа .

5. На новом листе создайте список сотрудников (рис. 23).

Рис. 23. Список сотрудников предприятия

6. Для сортировки ФИО по алфавиту выполните команду: вкладка ленты Данные группа Сортировка и фильтр кнопка .

7. Выделите диапазон ячеек А1:А10 и щелкните поле Имя у левого края строки формул. Введите имя для ячеек, например Сотрудники . Нажмите клавишу Enter .

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

9. Правой кнопкой мыши щелкните по ярлычку листа. В контекстном меню выберите команду .

10. В диалоговом окне Защита листа (рис. 24) введите пароль для отключения защиты листа. В разделе Разрешить всем пользователям этого листа снимите флажки со всех элементов. Нажмите кнопку ОК .

Рис. 24. Диалоговое окно Защита листа

11. В диалоговом окне Подтверждение пароля введите пароль еще раз.

12. Правой кнопкой мыши щелкните по ярлычку листа и в контекстном меню выберите команду Скрыть .

13. Перейдите на Лист 1 и создайте таблицу Расчет заработной платы
(рис. 25). Столбец ФИО заполните, используя раскрывающийся список.

Рис. 25. Структура таблицы

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

15. На вкладке Данные в группе Работа с данными выберите команду Проверка данных .

16. В диалоговом окне Проверка данных укажите тип и источник данных (рис. 26).

17. Откройте вкладку Сообщение для ввода (рис. 27). Заполните пустые поля.

Рис. 26. Диалоговое окно Проверка данных

Рис. 27. Сообщение при вводе данных

18. Перейдите на вкладку Сообщение об ошибке (рис. 28). Заполните поля Вид , Заголовок и Сообщение .

Рис. 28. Сообщение при ошибке ввода данных

19. Для заголовков таблицы установите перенос текста (кнопка , расположенная на панели инструментов Выравнивание вкладки ленты Главная ).

20. Закрепите два первых столбца и строку заголовков таблицы. Для этого выделите диапазон ячеек C5:I20 и выполните команду: вкладка ленты Вид группа Окно кнопка .

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

вкладка ленты Главная панель инструментов Число в раскрывающемся списке форматов выберите Денежный формат .

22. Составим формулу для вычисления премии, которая составляет 20% от оклада. Любая формула начинается со знака = , поэтому переходим в ячейку F5 ивводим формулу =E5*20% (или =Е5*0,2 ).

23. С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область
F6: F11 .

24. Между столбцами Премия и Подоходный налог вставьте столбец Итогоначислено , в котором посчитайте сумму Оклад+ Премия .

25. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13% от начисленной суммы.

26. Посчитайте сумму к выдаче в долларах, для этого задайте текущий курс доллара, например 32, и в ячейку J5 введите формулу: =I5/$C$14 . Знак $ используется в формуле для того, чтобы при копировании с помощью маркера автозаполнения, адресация ячейки не изменялась.

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

28. Используя функцию СУММ , посчитайте общую сумму подоходного налога. Для этого:

· установите курсор в ячейку Н12 ;

· поставьте знак =;

· в строке формул нажмите кнопку ;

· в появившемся диалоговом окне мастера функций (рис. 29) выберите категорию Математические , функцию СУММ ;

· в качестве аргумента функции СУММ выделите диапазон суммирования Н5:Н11 ;

· нажмите кнопку ОК.

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

Рис. 29. Мастер функций

30. Найдите среднюю (СРЗНАЧ ), минимальную (MИН ) и максимальную (MAКС ) заработные платы.

31. Используя условное форматирование, обозначьте красным цветом Суммы к выдаче , менее 5 500 руб. Выполните команду: вкладка ленты Главная группа Стили раскрывающийся список Условное форматирование Правила выделения ячеек .

32. Постройте диаграмму Заработная плата сотрудников предприятия
(рис. 30). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Сtrl), и на вкладке ленты Вставка на панели инструментов Диаграммы выберите вид Гистограмма .

33. Используя вкладку ленты Макет, вставьте подписи осей и название диаграммы.

Рис. 30. Пример оформления диаграммы

34. Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом (рис. 31).

Рис. 31. Пример оформления круговой диаграммы