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

Организационный момент, постановка цели урока.

Знакомство учащихся с новой темой. Лекция учителя.

Объяснение учителем, последовательности самостоятельной работы учащихся.

Выполнение практической работы.

Подведение итогов урока, релаксация.

Лекция учителя:

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

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

Каждая функция в Excel включает в себя две части: имя функции, например, СРЗНАЧ (среднее значение) и необходимые аргументы. Аргументы — это информация, которую функция использует, чтобы вычислить новое значение или произвести действие. Аргументы всегда находятся справа от имени функции и заключены в скобки.

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

Основные правила записи формул:

• запись формул в Excel начинается со знака равенства;
• формулы могут включать обращение к одной или нескольким функциям;
• после имени каждой функции в скобках задаются аргументы, используемые функцией. Если функция не использует аргументов, то за ее именем следуют пустые скобки () — без пробела между ними, например, текущая дата: СЕГОДНЯ ();
• аргументы в функциях отделяются друг от друга точками с запятой (например, =СРЗНАЧ(1;2;3;4;5) равно 3, но =СРЗНАЧ(;;1;2;3;4;5) равно 2,14);

На примере функции СУММ, которая вычисляет сумму своих аргументов и может воспринять их от 1 до 30, рассмотрим возможные варианты задания аргументов.

Пример использования аргументов

Аргументы функции СУММ могут быть любого из ниже следующих четырех видов.

Число, например:

Формула, которая своим результатом имеет число, например:

Функции, которые используются в качестве аргументов других функций, как в предшествующем примере, называются вложенными функциями. В этом примере вложенная функция СРЗНАЧ является аргументом функции СУММ.

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

Во втором примере в качестве аргументов данных функций задается интервал клеток. Он задается адресом клетки, в которой находится первая цифра колонки, затем оператор связи — двоеточие ( : ) и адрес клетки, в которой находится последняя цифра. Второй пример эквивалентен формуле СУММ(A1;A2;A3;A4;A5). Преимущество использования интервала состоит в том, что аргумент A1:A5 принимается за один аргумент, в то время как А1, А2, А3, А4, А5 — за пять аргументов. Если требуется сложить более 30 чисел, то придется использовать интервалы, потому что функция не может иметь более 30 аргументов.

Имя, которое ссылается на константу, формулу, клетку или интервал клеток, содержащие числа или формулы, доставляющие числа, например:

В данном примере использованы собственные имена «Приращение» и «Основание». Имена назначаются константам, интервалам, формулам и делают формулы более удобными для чтения, понимания и работы.

22.1 Использование округления think-cell

Округление think-cell полностью интегрируется с Microsoft Excel и предоставляет набор функций, которые похожи на стандартные функции округления Excel. Вы легко можете применять эти функции к собственным данным, используя кнопки на панели инструментов на вкладке Формулы в think-cell.

22.1.1 Параметры округления

Как и функции Excel, функции округления think-cell принимают два параметра.

x Значение, которое необходимо округлить. Это может быть константа, формула или ссылка на другую ячейку. n Точность округления. Значение этого параметра зависит от используемой функции. Параметры функций think-cell эквивалентны параметрам соответствующих функций Excel. Примеры см. в таблице ниже.

Округление think-cell позволяет округлять значения не только до целых чисел, но и до любых кратных чисел. Например, если вы хотите представить данные в последовательности 5-10-15. просто округляйте значения до чисел, кратных пяти. Используя раскрывающееся меню на панели инструментов think-cell, просто введите или выберите нужную точность округления. Затем think-cell выберет требуемые функцию и параметры. В следующей таблице представлен ряд примеров округления определенных значений x с использованием панели инструментов вместе с соответствующим параметром n .

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

Для получения оптимальных результатов и минимального отклонения от базовых значений, следует использовать TCROUND , когда это возможно. Используйте более ограничительные функции TCROUNDDOWN , TCROUNDUP или TCROUNDNEAR , только если это необходимо.

Внимание! Не следует использовать недетерминированные функции, такие как RAND() , с какими-либо формулами TCROUND . Если функции возвращают разные значения при каждом вычислении, округление think-cell будет совершать ошибки при вычислении значений.

22.1.2 Макет вычисления

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

22.1.3 Размещение функций TCROUND

Так как функции TCROUND предназначены для управления выходными данными ячейки, они должны быть крайними функциями:

Если вы введете что-то в строках неправильных примеров, think-cell уведомит вас о значении ошибки Excel #VALUE! .

Способ 2: Добавление функции степени

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

Активируйте ячейку для расположения функции, кликнув по ней левой кнопкой мыши. Затем нажмите по значку fx для открытия соответствующего окна.

В нем выберите категорию, отображающую полный перечень функций. Отыщите «‎СТЕПЕНЬ» и дважды кликните по этой строке.

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

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

Редактирование формул при помощи Мастера

После того как Вы нажали кнопку OK и формула вставилась на страницу, вызвать снова окно Мастера для внесения изменений очень легко. Для этого выделите ячейку с формулой (если она ещё не выделена) и снова нажмите кнопку «Fx» на панели инструментов, как было уже показано выше. В открывшемся окне Мастера функций внесите необходимые изменения (значение аргументов, добавление новых аргументов) и подтвердите их кнопкой OK.

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

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

В комментариях после статьи Вы можете поделиться своим опытом использования всплывающего окна Мастера формул.

Работа с ненормализированными данными

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

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

Работа в программе MS Excel

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

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

Функции рационализируют написание формул.

Скачать цикл лабораторных работ по MS Excel

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

• сэкономить время, затрачиваемое на ввод данных;
• снизить вероятность допущения ошибок.

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

Вызов Мастера функций:

Вставка функции MS Excel

• Вставка → Функция;
• f_x на панели инструментов;
• f_x в строке формул;
• Shift + F3.

Категория функций

Функция: поля ввода (поля для ввода аргументов)

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

Синтаксис функций описывает структуру выбранной функции.

= название функции (набор аргументов)

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

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

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

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

Аргументы

обязательные

необязательные

Аргументы перечисляются через запятую.

Формулы могут включать обращение к одной или нескольким функциям (вложение функций).

В формулах недопустимы пробелы.

Завершение ввода формулы — Enter.

Результатом работы формулы является вычисленное значение.

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

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

Пример функция вида =ОКРУГЛ(3,546455;0) —

даст в ячейке оругленое до целого число, в данном случае 4.

При вводе функций «от руки», без мастера, необходимо правильно набирать наименование функции ( бывает русскими, бывает английскими буквами), набор обязательных аргументов. Названия столбцов в формулах должны быть только АНГЛИЙСКИМИ буквами.

Автосуммирование:

автосуммирование MS Excel

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

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

Сообщения об ошибках . Причины возникновения и способы устранения ошибок в формулах.

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

Для того чтобы ввести в ячейку текст или число необходимо:

• выделить ячейку, в которую нужно ввести данные (нажать F2);
• набрать текст или число;
• нажать Enter.

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

• нажать Enter;
• выполнить щелчок на любой другой ячейке;
• воспользоваться стрелками управления курсором;
• нажать кнопку √ строке формул.

Если размер текста превышает размер ячейки, то:

• текст занимает соседнюю ячейку (справа или слева в зависимости от выравнивания текста), если она пустая;
• на экране отображается только часть текста, помещающаяся в ячейке, если соседняя ячейка заполнена.

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

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

Если в ячейку ввести число, то в ней отобразится это число или символы #### (если число не помещается в ячейку).

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

Приемы автоввода (автозаполнение)

При вводе текста и чисел в ячейках отображаются сами текст и числа.

Для того чтобы ввести в ячейку формулу необходимо:

• выделить ячейку, в которую нужно ввести формулу (F2);
• набрать формулу;
• нажать Enter.

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

• нажать Enter;
• нажать кнопку √ строке формул.
  Это объясняется тем, что в процессе ввода формулы вместо набора адреса ячейки можно выполнить щелчок по этой ячейке и адрес автоматически пропишется в формулу. Так же действует и использование стрелок управления курсором.

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

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

Помимо копирования данных функция автозаполнения позволяет создавать списки. Например, январь, февраль, март …

Производя автозаполнение в обратном порядке (вверх или влево) и элементы списка будут расположены в обратном порядке.

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

Например, ввести 1 и воспользоваться автозаполнением, удерживая Ctrl (получится последовательный ряд чисел), а для создания ряда четных чисел нужно ввести в первую ячейку 2, во вторую 4, выделить обе ячейки и протащить маркер на последующие ячейки.

Редактирование данных (редактирование содержимого ячеек)

Перемещение внутри листа.

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

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

Чтобы удалить содержимое ячейки – выделить ее и нажать Delete.

Между данными, расположенными на различных листах, устанавливаются связи.

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

Приемы выделения в MS Excel

Структурный

элемент

Действия

мышью

клавишами

Форматирование

• Форматирование данных
• Форматирование таблицы (структуры таблицы)

Вставка, удаление строки (строк), столбца (столбцов), ячейки (ячеек). Скрытие строки (строк), столбца (столбцов).

Первоначально все столбцы имеют одинаковую ширину и все строки одинаковую высоту.

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

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

Шаг 1: расчет стандартного отклонения

Стандартное отклонение, или, как его называют по-другому, среднеквадратичное отклонение, представляет собой квадратный корень из дисперсии. Для расчета стандартного отклонения используется функция СТАНДОТКЛОН. Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…) = СТАНДОТКЛОН.В(Число1;Число2;…)

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

Урок: Формула среднего квадратичного отклонения в Excel

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция — СРЗНАЧ. Вычислим её значение на конкретном примере.

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

Урок: Как посчитать среднее значение в Excel

Шаг 3: нахождение коэффициента вариации

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

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

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

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

Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.