Электронные таблицы. Ввод математических формул и вычисления по ним

Сегодня на уроке мы вспомним:

· как произвести простейшие расчёты в Microsoft Excel;

· как организована адресация в формулах;

· как присваивать имена ячейкам;

· и как работать со встроенными функциями.

Электронные таблицы, или табличный процессор, – это прикладная программа, которая предназначена для организации табличных вычислений на компьютере.

Вычисление – это процесс расчёта по формуле.

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

Производить вычисления в Microsoft Excel очень удобно и понятно, для этого необходимо установить курсор в ячейку листа, нажать на клавишу = и ввести формулу, а затем нажать клавишу Enter.

Как мы помним в ячейке появится результат расчётов, а формула будет видна в строке формул. Но если необходимо увидеть все формулы, введённые на листе, то нужно на вкладке «Формулы» в группе «Зависимости формул» нажать на «Показать формулы». В ячейках теперь будут отображены не значения, а формулы.

Завершить ввод формулы можно ещё несколькими способами:

· нажав на кнопку «Ввод», тем самым подтвердив формулу;

· нажав на кнопку «Отмена», то есть отказаться от ввода формулы;

· нажав на клавишу Esc.

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

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

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

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

К арифметическим операциям, применяемых в формулах относятся:

· сложение;

· вычитание;

· умножение;

· деление;

· процент;

· возведение в степень.

Результатом арифметических операция являются числа.

К операторам сравнения относятся:

· равно;

· больше;

· меньше;

· больше либо равно;

· меньше либо равно;

· не равно.

Результатом операции сравнения является одно из двух логических значений – истина или ложь.

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

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

Формула может содержать константу. Она может быть числовой и текстовой. Текстовую константу необходимо заключать в кавычки.

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

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

Вспомним как организована адресация в формулах.

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

Если удалить ячейку, адрес которой входит в формулу, то формула вернёт вот такое значение: #ССЫЛКА!.

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

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

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

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

Часто требуется, чтобы адрес ячейки в формуле не менялся. Тогда используют абсолютный адрес – перед кодом столбца и номером строки добавляют знак доллара ($).

Абсолютные адреса применяют, когда необходимо сделать расчёты, в которых есть постоянные величины.

Также можно воспользоваться в расчётах смешанным адресом ячейки – это когда в адресе ячейки зафиксирована только одна часть, то есть знак доллара стоит или перед номер строки, или перед кодом столбца. В таком случае при транслировании формулы, в адресе ссылки будет меняться только не зафиксированная часть.

В Microsoft Excel можно присвоить ячейке, диапазону ячеек, таблице, формуле или постоянному значению собственные имена. В качестве имени можно использовать уникальные последовательности длиной не более 255 символов, состоящие из букв, цифр, точек и знака подчёркивания.

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

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

Конечно же, имена не должны быть схожи с адресами ячеек, строк или столбцов.

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

Также на вкладке «Формулы» в группе «Определённые имена» можно задать имя, посмотреть диспетчер имён или же выбрать из списка имя и использовать его в формуле.

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

Здесь они расположены по категориям. Если раскрыть список функций и навести курсор на одно из названий, то можно увидеть описание функции.

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

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

На вкладке «Главная» в группе «Редактирование» и на вкладке «Формулы» в группе «Библиотека формул» мы может увидеть две схожие кнопки: «Автосумма» и «Сумма». Если нажать на одну их них, то можно найти сумму значений ячеек – это самая распространённая функция. А если раскрыть выпадающий список кнопок «Автосумма» или «Сумма», то мы увидим часто используемые функции.

Давайте рассмотрим, как найти среднее значение.

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

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

Нажимаем Enter. Среднее значение найдено. Обратите внимание, что сама функция называется по-другому, не как в списке.

Если в выпадающем списке нажать на команду «Другие функции», или на вкладке «Формулы» в группе «Библиотека формул» нажать на кнопку «Вставить функции», то на экране появится диалоговое окно, в котором можно выбрать любую из встроенных в Excel функцию. Также это окно можно открыть, если нажать на кнопку «Вставить функцию», которая находится перед строкой формул.

Чтобы вставить функцию, сначала необходимо выбрать категорию, а затем в основной части окна «Выберите функцию» указать нужную функцию, после нажать кнопку «ОК». При нажатии на функцию внизу можно увидеть аргументы функции и её краткое описание.

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

Если неизвестно как именно называется функция, то можно ввести в поле «Поиск функции», что необходимо выполнить и нажать кнопку «Найти» - Excel выведет подходящие функции.

Функции, которые использовались недавно можно найти, если выбрать «10 недавно использовавшихся» или на вкладке «Формулы» в группе «Библиотека формул» нажать на кнопку «Последние».

Найдём среднее значение некоторого количества чисел.

Чтобы выбрать диапазон, нужно нажать вот на эту кнопку.

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

Затем нажимаем на эту кнопку, чтобы окно раскрылось.

Если необходимо внести значения ещё одного диапазона, то проделываем те же действия с полем ниже, которое называется «Число2».

У нас появилось третье поле для ввода аргументов, но его можно оставить пустым.

Нажимаем кнопку «ОК». В ячейке появился результат.

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

Дана таблица с названиями услуг, ценами и количеством выполненных услуг. Необходимо рассчитать итоговую стоимость по каждой услуге. Найти суммарное количество услуг. Затем вычислить среднюю цену, количество наименований услуг и вывести цены самой дорогой и самой дешёвой услуг. Рассчитайте стоимость каждой услуги с 10 процентной скидкой. Выясните какие цены выше средней, а какие ниже.

Сначала рассчитаем итоговую стоимость. Чтобы её найти нужно цену умножить на количество:

· ставим курсор в ячейку D3;

· набираем знак равно;

· нажимаем на ячейку В3;

· вводим знак умножить;

· нажимаем на ячейку С3;

· нажимаем на клавишу Enter.

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

Теперь вычислим суммарное количество услуг.

Ставим курсор в ячейку С10, нажимаем кнопку «Автосумма» и клавишу Enter.

Рассчитаем среднюю цену:

· Ставим курсор в ячейку В11;

· Нажимаем на кнопку «Вставить функцию»;

· В поле «Поиск функции» вводим «среднее значение» и нажимаем кнопку «Найти»;

· Прокручиваем появившийся список функций в поле «Выберите функцию» и находим функцию «СРЗНАЧА». Нажимаем на неё и изучив описание понимаем, что эта нужная нам функция. Конечно, тут легко интуитивно догадаться, что нам необходима именно эта функция, иначе пришлось бы пересмотреть описания всех выданных функций.

· Нажимаем кнопку «ОК».

· В появившемся окне вводим необходимые данные и нажимаем на кнопку «ОК».

В появившемся результате после запятой стоит много знаков, но хватит и двух. Для этого выделяем ячейку В12, затем на вкладке «Главная» в группе «Число» нажимаем на кнопку уменьшить разрядность столько раз, чтобы у числа осталось всего два знака после запятой. Также сделаем у ячейки В12 денежный формат.

Вычислим количество наименований услуг. Для этого нам понадобится функция СЧЁТЗ.

Ставим курсор в ячейку В13.

На вкладке «Формулы» в группе «Библиотека функций» нажимаем на кнопку «Другие функции».

В выпадающем списке находим «Статистические» и находим функцию СЧЁТЗ, которая подсчитывает количество непустых ячеек в диапазоне.

Для нахождения цены самой дорогой услуги нам понадобится функция МАКС:

· ставим курсор в ячейку В14;

· на вкладке «Формулы» в группе «Библиотека функций» в выпадающем списке кнопки «Автосумма» выбираем команду «Максимум», которая вызывает функцию МАКС;

· так как Excel автоматически неправильно выделил диапазон значений, то нажимаем на кнопку «Вставить функцию»;

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

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

Для нахождения самой дешёвой цены необходимо проделать те же действия, только выбрать команду Минимум, которая вызывает функцию МИН.

Чтобы вычислить цену услуги со скидкой 10 %, необходимо от изначальной цены отнять произведение цены на процент скидки:

· ставим курсор в ячейку F3;

· вводим следующую формулу: =B3-B3*E3;

· протягиваем маркер заполнения до ячейки F9 включительно;

· нажимаем «Показать формулы» и видим, что во всех ячейках кроме F3 формула неправильная, так как значения скидки берутся из не тех ячеек. Значит адрес ячейки со значением скидки нужно сделать абсолютным. Делаем его абсолютным в ячейке F3, и снова протягиваем маркер заполнения до ячейки F9 включительно, чтобы данные пересчитались.

Теперь выясним какие цены выше средней, а какие ниже.

Для этого нужна логическая функция ЕСЛИ, которая выполняет логические сравнения значений и ожидаемых результатов. Поэтому у функции ЕСЛИ возможны два результата: один возвращается, если сравнение истинно, а второй – если сравнение ложно.

Для начала вставим ещё один столбец после столбца «Цена услуги» и назовём его «Сравнение цен со средней ценой».

Ставим курсор в ячейку С3.

На вкладке «Формулы» в группе «Библиотека функций» в выпадающем списке кнопки «Логические» находим функцию ЕСЛИ.

Итак, нам необходимо ввести 3 аргумента.

В поле первого аргумента мы должны ввести условие, то есть логическое выражение, принимающее значение ИСТИНА или ЛОЖЬ. Не сворачивая окно «Аргументы функций» нажимаем на ячейку В3, затем вводим оператор сравнения больше и нажимаем на ячейку В12. Адрес ссылки ячейки со значением средней цены должен быть абсолютным.

Во втором поле вводим текст, который должен появится, если цена больше средней цены, а в третьем поле вводим текст, который должен появится, если цена меньше средней цены.

Нажимаем кнопку «ОК».

При необходимости расширяем столбец С.

Протягиваем маркер заполнения до ячейки С9 включительно.

Всё, задача решена.

В конце урока попробуйте ответить на следующие вопросы:

Что такое формула?

Чем отличается относительная ссылка от абсолютной?

Как произвести вычисления в Microsoft Excel?

Внимательно посмотрев урок, вам не составит труда ответить на вопросы.