Использование Мастера функций. - Лабораторная Работа, раздел Образование, Создание и редактирование таблиц Ms Excel Содержит 228 Встроенных Функций. Простейший Способ Получения Полной ...
MS Excel содержит 228 встроенных функций. Простейший способ получения полной информации о любой из них заключается в использовании меню ?.Для удобства функции в Excel разбиты по категориям (математические, финансовые, статистические и т.д.).
Обращение к каждой функции состоит из двух частей: имени функции и аргументов в круглых скобках. Аргументы функции могут быть следующих типов:
1)числовые константы, например, функция ПРОИЗВЕД(2;3) вычисляет произведение чисел 2 и 3, т.е. 2·3.
2)ссылки на ячейки и блоки ячеек (функция ПРОИЗВЕД (А1;С1:СЗ) вычисляет произведение содержимого ячеек А1,С1,С2иС3, т.е.А1·С1·С2·СЗ
3) текстовые константы (заключенные в кавычки).
4) логические значения.
5) массивы.
6) имена ссылок, например, если ячейке А10 присвоить имя СУММА (последовательность команд Вставка, Имя, Определить...),а блоку ячеек В10:Е10 -имя ИТОГИ , то допустима следующая запись: =СУММ(СУММА; ИТОГИ).
7) смешанные аргументы, например, =СРЗНАЧ (Группа;АЗ;5*3)
Формулы, содержащие функции, можно вводить непосредственно в ячейку, в строку формул или создавать с помощью Мастера функций. Для вызова Мастера функций необходимо выбрать команду Функция в меню Вставка или нажать кнопку Мастер функций (значок fx). B открывшемся диалоговом окне выберите категорию и имя функции, затем в поля с соответствующими подсказками введите аргументы. После нажатия кнопки Закончить готовая функция появится в строке формул
Пример 1. Вычислить значение функции у = ех · sin(x) для -1=< х <=1 c с шагом Δх=0,2. Определить количество отрицательных у.
Заполним столбец Азначениями аргумента функции. Чтобы не вводить их вручную , применим следующий прием. Введите в ячейку А1 начальное значения аргумента -1. В меню Правкавыберите команду Заполнить, затем Прогрессия и в открывшемся диалоговом окне укажите предельное значение
(1),шаг(0.2) и направление (По столбцам). После нажатия кнопки ОКв столбце А будут введены все значения аргумента . В ячейку В1 введите формулу : =exp(А1)* sin(AI). Размножьте эту формулу на остальные ячейки столбца В, ухватив левой мышью черный квадратик в правом нижнем углу рамки выделенной ячейки В1 и протащив рамку до конца изменения аргумента . В итоге будут вычислены соответствующие значения функции.
Для определения количества отрицательных у в ячейку С1введите формулу =СЧЕТЕСЛИ (В 1: В 11; <0). В результате в ячейке С1 будет вычислено количество отрицательных значений в ячейках В1:В11(т.е. у). Принцип действия большинства логических функций Excel заключается в проверке некоторого условия и выполнения в зависимости от него тех или иных действий. Так, функция ЕСЛИ выполняет проверку условия, задаваемого первым аргументом логического выражения: ЕСЛИ (логическое 1; значение_да; значение нет)и возвращает значение да, если условие выполнено (ИСТИНА), и значение нет, в противном случае (ЛОЖЬ).
Например:
1)=ЕСЛИ(В6<10;5; 10). Если значение в ячейке В6<10, то функция вернет результат 5, иначе -10.
2)=ЕСЛИ(В4>80;"Сданы";"Не сданы"). Если значение В4>80, то в ячейке с приведенной формулой будет записано "Сданы", иначе - "Не сданы".
3)=ЕСЛИ(СУММ(А1:А10)>0; СУММ(В1:В10);0).Если сумма значений в столбце А1:А10 >0, то в ячейке, содержащей эту формулу вычислится сумма значений в столбце В1:В10, иначе – запишется 0.
Дополнительные логические функции:
=И(логическое выражение 1; логическое выражение 2;...) – возращает значение ИСТИНА, если все аргументы истинны, и ЛОЖЬ, если хотя бы один аргумент – ЛОЖЬ.
Например, для =ЕСЛИ(СУММ(А1:А10)>0; И; СУММ(В1:В10)>0; СУММ(А1:В10);0).Если суммы и в столбце А1:А10 и в столбце В1: В10 положительны , то вычислить сумму значений в ячейках А1:В10, иначе - 0.
Аналогично используются:
=ИЛИ(логическое 1;логическое 2;...) – возвращает значение ИСТИНА,
если хотя бы один аргумент является - ИСТИНА
=НЕ(флаг) – меняет значение ИСТИНА на ЛОЖЬ и наоборот.
Все темы данного раздела:
Настройка новой книги
После запуска Excel на экране появляется новая рабочая книга с тремя листами (по умолчанию). Некоторые её параметры необходимо настроить, для чего в пункте меню Сервис нужно выбрат
Ввод данных и формул
Ввод данных. Ввести данные можно непосредственно в активную ячейку, либо набрав их в строке формул и нажав клавишу Enter. Если текст не помещается в активной ячейк
Форматирование ячеек
Для удобства представления данных в Excel применяются различные форматы ячеек (числовой, денежный, финансовый, процентный, дата, время и т.д.). Формат влияет только на внешнее представление данных
Построение двумерных и трехмерных диаграмм
.Диаграмма- это графическое представление числовых данных. В Excel используются следующие основные понятия и определения, которые необходимо знать для правильного и эффектив
Редактирование диаграмм
Для редактирования внедренной диаграммы необходимо сначала активизировать редактируемый элемент двойным щелчком мыши, после чего вокруг нее появится рамка, а в строке меню добавятся команды для ред
Построение нестандартных диаграмм
Большой интерес представляют комбинированные диаграммы, на которых разные ряды данных отображаются в виде разных типов диаграмм. Для создания такой диаграммы в окне Мастера диаграмм
Анализ статистических данных
MS EXCEL предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные функции. Рассмотрим некоторые из них.
1 .Вычисление ср
Инструменты пакета анализа
Для решения сложных задач применяется ПАКЕТ АНАЛИЗА.
ПАКЕТ АНАЛИЗА –дополнение EXCEL расширяющее аналитические возможности и позволяющее строить гистограммы, составлять таблицы ранг и перс
Линейный регрессионный анализ
Одним из методов, используемых для прогнозирования, является регрессионный анализ.
Регрессия - это статистический метод, который позволяет найти уравнение, наилучшим о
Линейная регрессия
Линейная регрессия дает возможность наилучшим образом провести прямую линию через точки одномерного массива данных. Уравнение с одной независимой переменной, описывающее прямую линию, имеет вид:
Экспоненциальный регрессионный анализ.
Нелинейная регрессия позволяет подбирать к табличным данным нелинейное уравнение – параболу, гиперболу и др. Excel реализует нелинейность в виде экспоненты, т.е. подбирает кривую вида
Множественная регрессия
Множественная регрессия представляет собой анализ более одного набора данных аргумента х и дает более реалистичные результаты. Множественный регрессионный анализ также может быть как линейным, так
Использование функций регрессии
В Excel имеется 5 функций для линейной регрессии (ЛИНЕЙН(...), ТЕНДЕНЦИЯ(...), ПРЕДСКАЗ(...), НАКЛОН(...), СТОШУХ(...)) и 2 функции дня экспоненциальной регрессии - ЛГРФПРИБЛ(...) и POCT(...). Расс
ИНДЕКС(ЛИНЕЙН(изв_знач_y;изв_знач_x);2)
· Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точными являются модель, используемая функцией ЛИНЕЙ
Линия тренда
Excel позволяет наглядно отображать тенденцию изменения данных с помощью линии тренда, которая представляет собой интерполяционную кривую, описывающую отложенные на диаграмме данные.
Для т
Простая линейная регрессия
Пример 1. Функция ТЕНДЕНЦИЯ.
а) Предположим, что фирма желает приобрести земельный участок в июле. Фирма собирает информацию о ценах за последние 12 месяцев, начиная с
Экспоненциальная регрессия
Пример 3. а) Функция ЛГРФПРИБЛ.
Условие примера 2.
Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не п
Линейный многомерный регрессионный анализ
Пример 4. Предположим, что коммерческий агент рассматривает возможность закупки небольших зданий под офисы в традиционном деловом районе. Агент может использовать множественны
Работа с финансовыми функциями.
Анализ «Что-если»
Цель работы: научиться работать с финансовыми функциями Excel
и выполнять анализ «Что-если»
Со
Сумма займа, руб. 80 000р.
2 Введём в ячейку D2формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. приме
Запуск макроса с клавиатуры сочетанием клавиш
Запись макроса, запускаемого из меню Сервис:
1 В меню Сервис выберите подменю Макроси выберите команду Начать запись.
Абсолютная адресация
Пример 1 Разработать макрос для расчета прогнозируемых значений с помощью функции ТЕНДЕНЦИЯ. Зависимость y = f(x) задана таблицей:
A
Относительная адресация
В примере 1 использованы абсолютные ссылки на адреса ячеек, поэтому, чтобы использовать макрос «Тенденция» для других данных, их нужно предварительно занести в эти же ячейки B1:H1 и B2:E2.
Инструментов. Создание новой панели инструментов
Для запуска макроса можно назначить кнопку или другой элемент управления на существующей или новой панели инструментов:
1 Выберите команду Настройка... в меню Серв
Копирование макроса в другую книгу
Созданный макрос действует во всех листах открытой книги. Для копирования макроса в другую книгу нужно:
1 Открыть книгу, содержащую копируемый модуль, и книгу, в которую нужно его копирова
А B C D E F G H
1 Переменная Х1 Х2 Х3 Х4 Формула Знак Св. член
2 Значение
3 Нули
4 Коэф. ЦФ 60 70 120 130 СП(В2:Е2)(В4
Ввод данных в окно Поиск решения
Выбрать в пункте меню Сервис команду Поиск решения, поставить курсор в поле целевой функции, выделить ячейку F4в форме(или ввести F4
А В С D E F G H
1 Переменная Х1 Х2 Х3 Х4 Формула Знак Св. член
2 Значение 10 0 6 0
3 Нули
4 Коэф. ЦФ 60 70 120 130 132
Вывод разных частей документа
Чтобы можно было одновременно работать с двумя разными частями одного документа (например, Книга.xls, Лист 1, Лист 2, Лист 7), нужно сделать его копию , выбрав команду Новоев меню
Сортировка данных в списке
Сортировка данных в таблице БД Excel является основной операцией, т.к. многие операции группировки, которые выполняются из меню Данные, можно использовать только после сортировки.
Автофильтр
Автофильтрация позволяет выбрать критерий отбора из предлагаемых в приложении для каждого столбца отдельно. Фильтрация поля "Оценка" по условию "4" оставляет от исходного списка
Расширенный фильтр
Расширенный фильтр задаётся командойДанныеФильтрРасширеннй фильтр. При этом используется два типа критериев для фильтрации записей:
▪ критерий сравнения;
&#
Формирование критерия сравнения
Область критериев поиска представляет собой таблицу, которая состоит из строки имён полей и нескольких строк для задания условий по каждому столбцу.
Для создания таблицы рекомендуется снач
Формирование вычисляемого критерия
Вычисляемый критерий – это формула в строке для задания условий (в таблице отбора), которая состоит из адресов ячеек, встроенных функций, констант и операторов отношения.
Примечани
Фильтрация записей расширенным фильтром
После выполнения 1 этапа (пп.3.2.1, 3.2.2) сформированный критерий используется следующим образом ( этап 2):
▪ установить курсор в список и выполнить команду Данные Фильтр
Фильтрация с помощью формы
Форма – это электронный документ, предназначенный для ввода, редактирования, просмотра, удаления и фильтрации данных.
Для вызова формы нужно установить курсор в область списка и выполнить
Структурирование таблиц
структурирование таблицы -это группирование строк и столбцов в таблице. Оно необходимо при работе с большими таблицами , когда часто приходится временно закрывать или открывать вло
Ручной способ структурирования
При ручном способе структурирования выделяется блок смежных строк или столбцов и выполняется командаДанные Группа и Структура Группировать. Появляется окно Группирование, в котором
Автоструктурирование
Автоструктурирование выполняется для таблиц, содержащих формулы, которые ссылаются на ячейки, расположенные выше и (или) левее результирующих ячеек, образуя с ними сплошную смежную
Структурирование с подведением итогов
КомандаДанные Итоги создает структуру таблицы и одновременно вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной функцией.
Мастер сводных таблиц
Сводная таблица (или свод) –этотаблица итогов различных видов, составленная по данным из нескольких таблиц. В качестве таблиц-источников могут выступать списки, др
Построение макета сводной таблицы.
Структура сводной таблицы состоит из следующих областей, определяемых в макете (рис.4):
Область для страниц – для размещения полей, значения которых обеспечивают отбор записей на пе
Консолидация данных по расположению
При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую
Консолидация данных по категориям
При консолидации по категориям область имен входит в выделение, устанавливаются флажки подписи верхней строки или значения левого
столбца (рис. 4). Excel автоматически переносит эти имена
Консолидация по областям
1 Скопировать таблицу своего варианта из л.р.№9 ("например, Показатели в 1 квартале") на новый лист, переименовать лист в "Консолидация" (дважды щелкнуть мышью по имени и ввести
Консолидация по категориям
1 Добавить строку с товаром TV-тюнер с соответствующими числами в первую таблицу "Показатели в 1 квартале"(рис. 6).
2 Вставить новый столбец с именем % реализации и заполн
Новости и инфо для студентов