Практическая работа № 20
Выполнение расчетов в Excel
Цель: научиться выполнять расчеты в Excel
Сведения из теории
Все вычисления в программе MS Excel выполняются с помощью формул. Ввод формулы всегда начинается со знака равенства “=”, затем располагают вычисляемые элементы, между которыми стоят знаки выполняемых операций:
+ сложение / деление * умножение
− вычитание ^ возведение в степень
В формулах в качестве операндов часто используют не числа, а адреса ячеек, в которых хранятся эти числа. В этом случае при изменении числа в ячейке, которая используется в формуле, результат пересчитается автоматически
Примеры записи формул:
=(А5+В5)/2
=С6^2* (D 6- D 7)
В формулах используют относительные, абсолютные и смешанные ссылки на адреса ячеек. Относительные ссылки при копировании формулы изменяются. При копировании формулы знак $ замораживает: номер строки (А$2 - смешанная ссылка), номер столбца ($F25- смешанная ссылка) или то и другое ($A$2- абсолютная ссылка).
Формулы динамичны и результаты автоматически изменяются, когда меняются числа в выделенных ячейках.
Если в формуле несколько операций, то необходимо учитывать порядок выполнения математических действий.
Автозаполнение ячеек формулами:
ввести формулу в первую ячейку и нажать Enter;
выделить ячейку с формулой;
установить указатель мыши в правый нижний угол ячейки и протянуть в нужном направлении (автозаполнение ячеек формулами можно делать вниз, вверх, вправо, влево).
Использование функций:
установить курсор в ячейку, где будет осуществляться вычисление;
раскрыть список команд кнопки Сумма (рис. 20.1) и выбрать нужную функцию. При выборе Другие функции вызывается Мастер функций.
Рисунок 20.1 – Выбор функции
Задания для выполнения
Задание 1 Подсчет котировок курса доллара
В папке с названием своей группы создайте документ MS Excel, имя задайте по номеру практической работы.
Лист 1 назовите по номеру задания;
Создайте таблицу подсчета котировок курса доллара (рис. 20.2), столбец Дата заполните с помощью автозаполнения, столбцы Курс покупк и и Курс продажи отформатируйте как числа с двумя знаками после запятой.
Рисунок 20.2 – Образец таблицы (Лист 1)
Выполните расчет дохода в ячейке D4. Формула для расчета: Доход = Курс продажи – Курс покупки
Сделайте автозаполнение ячеек D5:D23 формулами.
В столбце «Доход» задайте Денежный (р) формат чисел;
Отформатируйте таблицу.
Сохраните книгу.
Задание 2 Расчет суммарной выручки
Лист 2.
Создайте таблицу расчета суммарной выручки по образцу, выполните расчеты (рис. 20.3).
В диапазоне B3:E9 задайте денежный формат с двумя знаками после запятой.
Формулы для расчета:
Всего за день = Отделение 1 + Отделение 2 + Отделение 3;
Итого за неделю = Сумма значений по каждому столбцу
Отформатируйте таблицу. Сохраните книгу.
Рисунок 20.3 – Образец таблицы (Лист 2)
Задание 3 Расчет надбавки
В этом же документе перейдите на Лист 3. Назовите его по номеру задания.
Создайте таблицу расчета надбавки по образцу (рис. 20.4). Колонке Процент надбавки задайте формат Процентный , самостоятельно задайте верные форматы данных для колонок Сумма зарплаты и Сумма надбавки.
Рисунок 20.4 - Образец таблицы (Лист 3)
Выполните расчеты.
Формула для расчета
Сумма надбавки = Процент надбавки * Сумма зарплаты
После колонки Сумма надбавки добавьте еще одну колонку Итого . Выведите в ней итоговую сумму зарплаты с надбавкой.
Введите: в ячейку Е10 Максимальная сумма надбавки, в ячейку Е11 − Минимальная сумма надбавки. В ячейку Е12 − Средняя сумма надбавки.
В ячейки F10:F12 введите соответствующие формулы для вычисления.
Сохраните книгу.
Задание 4 Использование в формулах абсолютных и относительных адресов
В этом же документе перейдите на Лист 4. Назовите его по номеру задания.
Введите таблицу по образцу (рис. 20.5), задайте шрифт Bodoni MT, размер 12, остальные настройки форматирования − согласно образцу. Ячейкам С4:С9 задайте формат Денежный, обозначение «р.», ячейкам D4:D9 задайте формат Денежный, обозначение «$».
Рисунок 20.5 - Образец таблицы (Лист 4)
Вычислите Значения ячеек, помеченных знаком “?”
Сохраните книгу.
Контрольные вопросы
Как осуществляется ввод формул в Excel?
Какие операции можно использовать в формулах, как они обозначаются?
Как сделать автозаполнение ячеек формулой?
Как вызвать Мастер функций?
Какие функции вы использовали в этой практической работе?
Расскажите, как вы вычисляли значения Максимальной, Минимальной и Средней сумм надбавки.
Как формируется адрес ячейки?
Где в работе вы использовали абсолютные адреса, а где – относительные?
Какие форматы данных вы использовали в таблицах этой работы?
Иногда, для того, чтобы найти ошибку, в формуле, или понять принцип работы, необходимо произвести ее пошаговое вычисление. В зависимости от потребностей и сложности вычисления можно воспользоваться различными способами, также, можно быстро преобразовать формулу в результат, который она выдает. Рассмотрим несколько примеров пошагового вычисления формулы в Excel.
Преобразование формулы в результат вычисления
Если в ячейке нужна не формула, а результат ее вычисления, то можно, конечно, скопировать результат и вставить в ту же ячейку в режиме сохранить только текст, однако есть более быстрый способ. Достаточно просто выделить формулу и нажать клавишу F9 . Когда такое вычисление может пригодиться? Вариантов можно подобрать несколько, ярким примеров будет работа с функциями Excel, которые возвращают случайные значения или , поскольку по умолчанию в Excel настроено автоматическое вычисление, то значения функций постоянно обновляются, а с помощью такого нехитрого способа можно результат вычисления быстро зафиксировать.
Преобразование части формулы в результат вычисления
Если выделить только часть формулы и нажать клавишу F9, то эта часть будет преобразована в результат, а все, что осталось в формуле, будет рассчитываться. Как и в предыдущем примере, можно таким образом зафиксировать вычисление одной из функций Excel, генерирующих случайные значения, но такое преобразование подойдет и для формул, которые не меняют свои значения при каждом пересчете листа Excel.
Пошаговое вычисление сложной формулы
Несомненно, самой интересной возможностью является пошаговое вычисление сложной формулы в Excel, когда нужно найти ошибку в вычислениях.
Что такое сложная формула в Excel?
Сложные формулы — это формулы, которые, как правило, содержат в себе несколько функций, которые, в свою очередь, также могут оказаться не самыми простыми к пониманию, но чрезвычайно полезными. Ярким примером является чрезвычайно популярная функция
Формулы в Excel - это его основная суть, то, ради чего и была создана эта программа компанией Microsoft. Формулы позволяют произвести расчеты значений ячеек на основе данных других ячеек, причем если исходные данные поменяются, то результат вычислений в ячейке, где стоит формула пересчитается автоматически!
Создание формул в Excel
Рассмотрим работу формул на самом простом примере - сумме двух чисел. Пусть в одной ячейке Excel введено число 2, а в другой 3. Нужно, чтобы в третье ячейке появилась сумма этих чисел.
Суммой 2 и 3 является, конечно же, 5, но вносить пятерку вручную в следующую ячейку не надо, иначе теряется смысл расчетов в Excel. В ячейку с итогом необходимо ввести формулу суммы и тогда результат будет вычислен программой автоматически.
В примере вычисление выглядит простым, но когда числа большие или дробные без формулы просто не обойтись.
Фомулы в Excel могут содержать арифметические операции (сложение +, вычитание -, умножение *, деление /), координаты ячеек исходных данных (как по отдельности, так и диапазон) и функции вычисления.
Рассмотрим формулу для суммы чисел в примере выше:
СУММ(A2;B2)
Каждая формула начинается со знака «равно». Если Вы хотите добавить в ячейку формулу, написав ее вручную, то именно этот знак следует написать первым.
Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:
СУММ(А4;B4;C4)
Когда требуется сложить большое количество ячеек, то указывать каждую из них в формуле займет очень много времени, поэтому вместо простого перечисления можно использовать указание диапазона ячеек:
СУММ(B2:B7)
Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.
Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:
ЕСЛИ(СУММ(А2:С2)
Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый - условие, второй - действие в случае, если условие верно, третий - действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».
ЕСЛИ(условие; верно; неверно)
В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.
Встроенные функции в Excel
Функций в Excel огромное количество и знать все просто невозможно. Некоторые часто используемые запомнить можно, а некоторые Вам понадобятся лишь изредка и помнить их название и тем более форму записи очень сложно.
Но в Экселе имеется стандартный способ вставки функций с их полным списком. Если Вы хотите добавить какую-то функцию в ячейку, то кликните по ячейке и выберите в главном меню вставку функции. Программа отобразит список функций и Вы сможете выбрать ту, которая необходима для решения задачи.
Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.
В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.
В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».
Функция в Excel разделены по категориям. Если Вы знаете к какой категории может относится предполагаемая Вами функция, то выбирайте отбор по ней. В противном случае выберите «Полный алфавитный перечень». Программа отобразит все имеющиеся функции в списке функций.
Пролистывайте список и выделяйте мышью наименование, заинтересовавшей Вас функции. Чуть ниже списка появится ее форма записи, требуемые аргументы и краткое описание, которое разъяснит Вам предназначение функции. Когда найдете то, что нужно, кликните по кнопке «OK» для перехода к указанию аргументов.
В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.
Окно аргументов примет упрощенный вид. Теперь необходимо кликнуть мышью на первую исходную ячейку с данными, а затем снова на значок таблица в окне аргументов.
Поле «Число 1» заполнится координатами выбранной ячейки. Ту же самую процедуру следует проделать для поля «Число 2» и для следующих полей, если число аргументов функции у вас более двух.
Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.
Рассмотренный способ вставки функции является универсальным и позволяет добавлять любую функцию из общего списка стандартных функций Excel.
| Нравится |
Для выполнения различных вычислений в таблицах Excel 2007 используют формулы и функции. Основные понятия о формулах и функциях в Excel 2003 изложены на страничке . Изложенные сведения о формулах и функциях для Excel 2003 являются характерными и для Excel 2007/2010.
Выполнение вычислений с помощью формул в Excel 2007 представлено на рисунке 1.
Рис. 1
Вычисления с помощью формул всегда начинаются со знака равенства "=". Кроме того, в формулу могут входить ссылки на ячейки, в которых указаны определенные значения в одном из числовых форматов, имена, числовые величины и функции, объединенные знаками арифметических действий (^; *; /; +; -;). В ячейке, в которой осуществлены вычисления, отображаются значения, а формула отображается в строке ввода.
Ссылки на ячейки бывают относительными и абсолютными. Абсолютные ссылки отличаются от относительных тем, что содержат символ “$” (например, $B$2) перед всеми атрибутами адреса ячейки. К смешанным ссылкам относятся ссылки на ячейки, в которых перед одним из атрибутов адреса ячейки установлен символ “$” (например, B$2 или $B2).
Образец формулы (Y=2sin(пx)cos 5 (2пx) + sin(5 пx) 2 , где п - число ПИ = 3, 14), которая содержит функции sin(), cos(), ПИ(), переменную x - относительную ссылку на ячейку "A2", представлен на скриншоте рисунок 2.
Рис. 2
В формуле могут быть использованы и имена диапазона ячеек, но для этого надо присвоить имена диапазонам ячеек. Чтобы присвоить имя диапазону ячеек в Excel 2007, необходимо выполнить следующие операции. Выделить диапазон требуемых ячеек, перейти на вкладку "Формулы" и щелкнуть на кнопке раскрывающегося списка "Присвоить имя" в группе "Определенные имена".
Из списка выбрать надпись "Присвоить имя...", откроется окно диалога "Создание имени". В строке "Имя" ввести имя, например "Закупка1" и щелкнуть на кнопке ОК. В результате выбранному диапазону ячеек будет назначено соответствующее имя (рисунок 3).
Рис. 3
Для использования имени диапазона ячеек в формуле надо выделить ячейку и вставить требуемую формулу, например СУММ(). В качестве аргумента используем имя диапазона ячеек, например "Закупка1". Для этого на вкладке "Формулы" в группе "Определенные имена" надо щелкнуть на команде "Использовать в формуле", и в открывшемся списке имен выбрать требуемое имя (рисунок 4).
Рис. 4
Формулы в Excel можно перемещать и копировать. Абсолютные ссылки при копировании формулы (распространении формулы) не изменяются, а изменятся только те атрибуты адреса ячейки, перед которыми не установлен символ “$” (т.е. относительные ссылки изменяются). При перемещении формулы в другое место таблицы абсолютные и относительные ссылки в формуле не изменяются.
Если вы никогда раньше не использовали табличный процессор для создания документов, советуем прочитать наше руководство Эксель (Excel) для чайников.
После этого вы сможете создать свой первый табличный лист с таблицами, графиками, математическими формулами и форматированием.
Подробная информация о базовых функциях и возможностях табличного процессора MS Excel. Описание основных элементов документа и инструкция для работы с ними в нашем материале.
Работа с ячейками. Заполнение и форматирование
Прежде чем приступать к конкретным действиям, необходимо разобраться с базовым элементом любого документа в Excel. Файл Эксель состоит из одного или нескольких листов, разграфленных на мелкие ячейки.
Ячейка – это базовый компонент любого экселевского отчета, таблицы или графика. В каждой клеточке содержится один блок информации. Это может быть число, дата, денежная сумма, единица измерения или другой формат данных.
Чтобы заполнить ячейку, достаточно просто кликнуть по ней указателем и ввести нужную информацию. Чтобы отредактировать ранее заполненную ячейку, нажмите на неё двойным кликом мышки.
Рис. 1 – пример заполнения ячеек
Каждая клеточка на листе имеет свой уникальный адрес. Таким образом, с ней можно проводить расчеты или другие операции. При нажатии на ячейку в верхней части окна появится поле с ее адресом, названием и формулой (если клеточка учуствует в каких-либо расчетах).
Выберем ячейку «Доля акций». Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое. Формул у этой клетки нет, поэтому они не показываются.
Больше свойств ячейки и функций, которые можно задействовать по отношению к ней, доступны в контекстном меню. Кликните на клеточку правой клавишей манипулятора. Откроется меню, с помощью которого можно отформатировать ячейку, провести анализ содержимого, присвоить другое значение и прочие действия.
Рис. 2 – контекстное меню ячейки и ее основные свойства
Сортировка данных
Часто пользователи сталкиваются с задачей сортировки данных на листе в Эксель. Эта функция помогает быстро выбрать и просмотреть только нужные данные из всей таблицы.
Перед вами уже заполненная таблица (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию. Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.
Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:
- Выделить таблицу или блок информации;
- Открыть кладку «Данные»;
- Кликнуть на иконку «Сортировка»;
Рис. 3 – вкладка «Данные»
- В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь).
- Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
- Подтвердите действие, нажав на «ОК».
Рис. 4 – настройка параметров сортировки
Произойдет автоматическая сортировка данных:
Рис. 5 – результат сортировки цифр в столбце «Январь»
Аналогичным образом можно проводить сортировку по цвету, шрифту и другим параметрам.
Математические расчеты
Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы. К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?
Для этого, вам необходимо кликнуть на третью ячейку, в которую будет вписан конечный результат вычислений. Затем нажмите на значок функции f(x), как показано на рисунке ниже. В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее. Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.
Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ».
Рис. 6 – выбор функции «СУММ»
В окне аргументов функции есть два поля: «Число 1» и «Число 2». Выберите первое поле и кликните мышкой на ячейку с цифрой «2». Её адрес запишется в строку аргумента. Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно. Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3», «Число 4» и так далее.
Если в дальнейшем значение суммируемых ячеек будет изменяться, их сумма будет обновляться автоматически.
Рис. 7 – результат выполнения подсчетов
Создание таблиц
В экселевских таблицах можно хранить любые данные. С помощью функции быстрой настройки и форматирования, в редакторе очень просто организовать систему контроля личного бюджета, список расходов, цифровые данные для отчетности и прочее.
Таблицы в Excel имеют преимущество перед аналогичной опцией в Word и других офисных программах. Здесь у вас есть возможность создать таблицу любой размерности. Данные заполняются легко. Присутствует панель функций для редактирования содержимого. К тому же, готовую таблицу можно интегрировать в файл docx с помощью обычной функции копирование-вставка.
Чтобы создать таблицу, следуйте инструкции:
- Откройте вкладку «Вставка». В левой части панели опций выберите пункт «Таблица». Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
- С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
- Нажмите ОК, чтобы подтвердить действие.
Рис. 8 – создание стандартной таблицы
Чтобы отформатировать внешний вид получившейся таблички, откройте содержимое конструктора и в поле «Стиль» кликните на понравившийся шаблон. При желании, можно создать собственный вид с другой цветовой гаммой и выделением ячеек.
Рис. 9 – форматирование таблицы
Результат заполнения таблицы данными:
Рис. 10 – заполненная таблица
Для каждой ячейки таблицы также можно настроить тип данных, форматирование и режим отображения информации. Окно конструктора вмещает в себя все необходимые опции для дальнейшей конфигурации таблички, исходя из ваших требований.
Добавление графиков/диаграмм
Для построения диаграммы или графика требуется наличие уже готовой таблички, ведь графические данные будут основываться именно на информации, взятой из отдельных строк или ячеек.
Чтобы создать диаграмму/график, нужно:
- Полностью выделить таблицу. Если графический элемент нужно создать только для отображения данных определенных ячеек, выделите только их;
- Откройте вкладку вставки;
- В поле рекомендуемых диаграмм выберите иконку, которая, по вашему мнению, наилучшим образом визуально опишет табличную информацию. В нашем случае, это объемная круговая диаграмма. Поднесите к иконке указатель и выберите внешний вид элемента;
Аналогичным образом можно создать точечные графики, линейные диаграммы и схемы зависимости элементов таблицы. Все полученные графические элементы также можно добавить в текстовые документы Ворд.
В табличном редакторе Excel присутствует множество других функций, однако, для начальной работы будет достаточно и приемов, которые описаны в этой статье. В процессе создания документа многие пользователи самостоятельно осваивают более расширенные опции. Это происходит благодаря удобному и понятному интерфейсу последних версий программы.
Тематические видеоролики:
Loading...