Аппарат встроенных функций MS Excel: статистические функции, финансовые функции и др
Правила применения подпрограммы «Подбор параметра» . Круг задач, решаемых с помощью подпрограммы «Подбор параметра»
Подбор параметра- это специальная функция, которая позволяет определить параметр (аргумент) функции, если известно ее значение. Подбор параметра – средство Excel, позволяющее решать так называемую обратную задачу, когда требуется, меняя значение одного из исходных данных (параметров), получить требуемое значение результата. При этом результат решения задачи должен быть задан в целевой ячейке формулой, содержащей ссылку на изменяемую ячейку с параметром. При подборе параметра его значение изменяется так, чтобы результат в целевой ячейке стал равным заданному числу.
При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение. Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Данные. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат. При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением.
|
|
|
Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р.
В том случае если результат вычисления при помощи формулы уже известен, но неизвестны значения, которые используются для получения данного результата, рекомендуется использовать средство «Подбор параметра».
Правила применения подпрограммы «Подбор параметра».
1.В окне открытого листа создайте нужную формулу, в которой будет происходить подбор параметра. Например, введите в ячейку С1 формулу =А1+В1. А в ячейку А1 введите значение 20.
2. Перейдите к вкладке «Данные» и в группе «Работа с данными» раскройте меню кнопки «Анализ что- если».
3. В списке команд выберите пункт «Подбор параметра».
4. В окне «Подбор параметра» в графе «Установить в ячейке» введите адрес ячейки с формулой. В нашем примере это ячейка С1.
5. В графе «Значение» введите искомый результат (тот, который необходимо достичь). Например, 100.
6. В графе «Изменяя значение ячейки:» введите адрес ячейки, значение которой нужно подобрать. Например, В1.
7. Нажмите кнопку «ОК».
8. В окне «Результат подбора параметра» отобразится найденное решение, а в ячейке В1 результат 80.
|
|
|
9. Закройте окно кнопкой «ОК».
Наглядное представление результатов с помощью диаграмм MS Excel
Диаграммы являются средством наглядного представления данных и облегчают выполнение сравнений, выявление закономерностей и тенденций данных.
Диаграммы создают на основе данных, расположенных на рабочих листах. Как правило, используются данные одного листа. Это могут быть данные диапазонов как смежных, так и не смежных ячеек. Несмежные ячейки должны образовывать прямоугольник. При необходимости, в процессе или после создания диаграммы, в нее можно добавить данные, расположенные на других листах.
Перед созданием диаграммы следует убедиться, что данные на листе расположены в соответствии с типом диаграммы, который планируется использовать. Данные должны быть упорядочены по столбцам или строкам. Не обязательно столбцы (строки) данных должны быть смежными, но несмежные ячейки должны образовывать прямоугольник.
Перед созданием диаграммы следует выделить фрагмент таблицы, для которого создается диаграмма.
Гистограммы. Данные, которые расположены в столбцах или строках, можно изобразить в виде гистограммы. Гистограммы используются для демонстрации изменений данных за определенный период времени или для иллюстрирования сравнения объектов.
|
|
|
В гистограммах категории обычно формируются по горизонтальной оси, а значения — по вертикальной.
Графики. Данные, которые расположены в столбцах или строках, можно изобразить в виде графика. Графики позволяют изображать непрерывное изменение данных с течением времени в едином масштабе; таким образом, они идеально подходят для изображения трендов изменения данных с равными интервалами. На графиках категории данных равномерно распределены вдоль горизонтальной оси, а значения равномерно распределены вдоль вертикальной оси.
Графики можно использовать при наличии текстовых меток категорий и для отображения разделенных равными интервалами значений, например, месяцев, кварталов или финансовых лет. Это особенно важно при наличии нескольких рядов. Для одного ряда можно использовать диаграмму по категориям. Также графики можно использовать при наличии нескольких разделенных равными интервалами числовых меток, главным образом лет. Если числовых меток больше десяти, вместо графика лучше использовать точечную диаграмму.
|
|
|
Круговые диаграммы. Данные, которые расположены в одном столбце или строке, можно изобразить в виде круговой диаграммы. Круговая диаграмма демонстрирует размер элементов одного ряда данных (Ряд данных. Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.) пропорционально сумме элементов. Точки данных (Элементы данных. Отдельные значения, отображаемые на диаграмме в виде полос, столбцов, линий, секторов, точек или других объектов, называемых маркерами данных. Маркеры данных одного цвета образуют ряд данных.) на круговой диаграмме выводятся в виде процентов от всего круга.
Круговые диаграммы рекомендуется использовать, если:
1.Требуется отобразить только один ряд данных.
2.Все значения, которые требуется отобразить, неотрицательны.
3.Почти все значения, которые требуется отобразить, больше нуля.
4.Количество категорий не более семи.
5.Категории соответствуют частям общего круга.
Линейчатые диаграммы. Данные, которые расположены в столбцах или строках, можно изобразить в виде линейчатой диаграммы. Линейчатые диаграммы иллюстрируют сравнение отдельных элементов.
Линейчатые диаграммы рекомендуется использовать, если:
1.Метки осей имеют большую длину.
2.Выводимые значения представляют собой длительности.
Диаграммы с областями. Данные, которые расположены в столбцах или строках, можно изобразить в виде диаграммы с областями. Диаграммы с областями иллюстрируют величину изменений в зависимости от времени и могут использоваться для привлечения внимания к суммарному значению в соответствии с трендом. Например, данные, отражающие прибыль в зависимости от времени, можно отобразить в диаграмме с областями, чтобы обратить внимание на общую прибыль.
Отображая сумму значений рядов, такая диаграмма наглядно показывает вклад каждого ряда.
Точечные диаграммы. Данные, которые расположены в столбцах и строках, можно изобразить в виде точечной диаграммы. Точечная диаграмма показывает отношения между численными значениями в нескольких рядах данных или отображает две группы чисел как один ряд координат x и y.
Точечная диаграмма имеет две оси значений, при этом одни числовые значения выводятся вдоль горизонтальной оси (оси X), а другие — вдоль вертикальной оси (оси Y). На точечной диаграмме эти значения объединяются в одну точку и выводятся через неравные интервалы или кластеры. Точечные диаграммы обычно используются для иллюстрации и сравнения числовых значений, например научных, статистических или технических данных.
Точечные диаграммы рекомендуется использовать, если:
1.Требуется изменять масштаб горизонтальной оси.
2.Требуется использовать для горизонтальной оси логарифмическую шкалу.
3.Значения расположены на горизонтальной оси неравномерно.
4.На горизонтальной оси имеется множество точек данных.
5.Требуется эффективно отображать данные электронной таблицы, которые содержат пары сгруппированных полей со значениями, и вводить независимые шкалы точечной диаграммы для показа дополнительных сведений о сгруппированных значениях.
6.Требуется демонстрировать не различия между точками данных, а аналогии в больших наборах данных.
7.Требуется сравнить множество точек данных без учета времени. Чем больше данных будет использовано для построения точечной диаграммы, тем точнее будет сравнение.
8.Для вывода данных таблицы в виде точечной диаграммы следует поместить данные по оси X в одну строку или столбец, а соответствующие данные по оси Y — в соседние строки или столбцы.
Биржевые диаграммы. Данные, которые расположены в столбцах или строках в определенном порядке, можно изобразить в виде биржевой диаграммы. Как следует из названия, биржевая диаграмма наиболее часто используется для иллюстрации изменений цен на акции. Однако эта диаграмма может использоваться также для вывода научных данных. Например, можно использовать биржевые диаграммы для демонстрации колебаний дневных или годовых температур. Для создания биржевой диаграммы необходимо правильно упорядочить выводимые данные.
Способ расположения данных на листе, которые будут использованы в биржевой диаграмме, очень важен. Например, для создания простой биржевой диаграммы (самый высокий курс, самый низкий курс, курс закрытия) следует поместить данные в столбцы с заголовками "Самый высокий курс", "Самый низкий курс" и "Курс закрытия" в указанном здесь порядке.
Поверхностные диаграммы. Данные, которые расположены в столбцах или строках, можно изобразить в виде поверхностной диаграммы. Поверхностная диаграмма используется, когда требуется найти оптимальные комбинации в двух наборах данных. Как на топографической карте, цвета и штриховки выделяют зоны одинаковых диапазонов значений.
Поверхностные диаграммы можно использовать для иллюстрации категорий и наборов данных, представляющих собой числовые значения.
Кольцевые диаграммы. Данные, которые расположены только в столбцах или строках, можно изобразить в виде кольцевой диаграммы. Как и круговая диаграмма, кольцевая диаграмма отображает отношение частей к целому, но может содержать более одного ряда данных (Ряд данных. Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.).
Примечание. Восприятие кольцевых диаграмм затруднено. Вместо них можно использовать линейчатые диаграммы с накоплением или гистограммы с накоплением.
Пузырьковые диаграммы. В пузырьковой диаграмме могут отображаться данные столбцов электронной таблицы, при этом значения по оси X выбираются из первого столбца, а соответствующие значения по оси Y и значения, определяющие размер пузырьков, выбираются из соседних столбцов.
Лепестковые диаграммы. Данные, которые расположены на листе в столбцах или строках, можно представить в виде лепестковой диаграммы. На лепестковой диаграмме можно сравнить статистические значения нескольких рядов данных (Ряд данных. Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.).
Другие типы диаграмм, которые можно создать в приложении Excel
Если в списке доступных диаграмм нет диаграммы необходимого типа, ее можно создать другим способом в приложении Excel.
Диаграмма Ганта и плавающая гистограмма. Перечисленные выше диаграммы можно использовать для имитации этих типов диаграмм. Например, линейчатую диаграмму можно использовать для имитации диаграммы Ганта, а гистограмму — для имитации плавающей диаграммы, на которой изображены минимальные и максимальные значения. Дополнительные сведения см. в статьях Представление данных в виде диаграммы Ганта и Представление данных в виде гистограммы.
Смешанные диаграммы. Чтобы выделить в диаграмме различные типы данных, можно одновременно использовать два или более типов диаграмм. Например, можно совместить гистограмму и график, чтобы максимально быстро облегчить восприятие диаграммы. Дополнительные сведения см. в статье Представление данных в виде смешанной диаграммы.
Организационные диаграммы. Чтобы создать организационную, плавающую или иерархическую диаграмму, вставьте элемент SmartArt. Дополнительные сведения см. в статье Создание организационной диаграммы.
Гистограммы и диаграммы Парето. Чтобы создать в приложении Excel гистограмму или диаграмму Парето (гистограмму с сортировкой), воспользуйтесь средствами для анализа данных, которые станут доступны после установки надстройки пакета средств анализа для приложения Excel (выбирается во время установки Microsoft Office или приложения Excel).
Аппарат встроенных функций MS Excel: статистические функции, финансовые функции и др
Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке.
Структура функции: имя функции, открывающая скобка, список аргументов, разделенных точками с запятой, закрывающая скобка. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы, или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.
Все функции в Excel характеризуются:
ü названием;
ü предназначением (что, собственно, она делает);
ü количеством аргументов (параметров);
ü типом аргументов (параметров);
ü типом возвращаемого значения.
В Microsoft Excel функции разбиты на категории, среди которых можно выделить:
ü функции для работы с базами данных
ü функции даты и времени
ü инженерные функции
ü финансовые функции
ü логические функции
ü функции ссылки и поиска
ü математические и тригонометрические функции
ü статистические функции
ü текстовые функции
Логические функции. Категория Логические содержит семь функций, в том числе функции ЕСЛИ и ЕСЛИОШИБКА. Использование логических функций делает формулы более гибкими, а использование функции ЕСЛИ наделяет формулу способностью “принимать решения”. Благодаря этому функция ЕСЛИ стала самой используемой логической функцией.
Текстовые функции. Текстовые функции предназначены для обработки текста. Например, с помощью функций ПРОПНАЧ или ДЛСТР можно изменить регистр или определить длину текстовой строки. Используя текстовые функции, можно объединить несколько строк в одну или, наоборот, разделить одну текстовую строку на несколько строк.
Например, формула =СЦЕПИТЬ(A1;A2) объединяет две текстовые строки, содержащиеся в ячейках A1 и A2, в одну.
Функции категории Проверка свойств и значений. Функции этой категории часто называют информационными. Функция ЯЧЕЙКА этой категории позволяет получить информацию о ячейке. Другие информационные функции проверяют выполнение какого либо условия и, в зависимости от результата, возвращают значение ИСТИНА или ЛОЖЬ (или числовое значение). Например, с помощью функции ЕЧИСЛО можно проверить, данные какого типа содержит ячейка. Если в ячейке содержится число, функция ЕЧИСЛО возвращает логическое значение ИСТИНА, в противном случае функция возвращает логическое значение ЛОЖЬ.
Функции Дата и время. Функции, принадлежащие к этой категории, предназначены для работы со значениями даты и времени. По сути, эти функции работают с числовыми значениями, потому что дата и время в Excel являются числами, к которым применен один из числовых форматов даты и времени. С помощью функции этой категории можно вычислить количество рабочих дней между двумя датами (функция ЧИСТРАБДНИ), преобразовать дату в год (функция ГОД), месяц (функция МЕСЯЦ) или день недели (функция ДЕНЬНЕД) и т.п.
Математические функции. Математические функции позволяют выполнять простые и сложные вычисления. В категорию Математические входят тригонометрические функции, например SIN, COS, ACOS; функции, выполняющие арифметические действия, например СУММ, ПРОИЗВЕД, ЧАСТНОЕ; и многие другие функции. К этой же категории относятся функции, позволяющие работать с массивами значений или матрицами, —МУМНОЖ, МОПРЕД и МОБР, а также функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЕ. ИТОГИ, которые используются для получения итоговых значений (суммы, среднегоарифметического, минимального или максимального значений и т.п.) в массивах данных или списках.
Функции категории Ссылки и массивы. Эти функции (другое название этой категории — функции просмотра) используются для поиска значений в списках или таблицах (например, функции ПРОСМОТР, ВПР или ГПР), а также для получения дополнительной информации (например, функции ЧСТРОК, ЧИСЛСТОЛБ и ОБЛАСТИ возвращают количество строк, столбцов или областей в ссылке на диапазон). Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ извлекает данные, хранящиеся в сводной таблице. Некоторые функции этой категории позволяют манипулировать диапазоном данных. Например, функция ТРАНСП транспонирует диапазон ячеек на рабочем листе.
Статистические функции и категория. Статистические функции предназначены для проведения статистического анализа. С помощью статистических функций можно вычислить статистические характеристики набора данных, такие как среднее арифметическое значение (функция СРЗНАЧ), стандартное отклонение (функция СТАНДОТКЛОН.Г или СТАНДОТКЛОН.В) или дисперсия (функции ДИСП.В и ДИСП.Г), построить статистический ряд (функция ЧАСТОТА), вычислить доверительные интервалы (функции ДОВЕРИТ.НОРМ и ДОВЕРИТ.СТЬЮДЕНТ). В категории Статистические имеются также функции для проверки статистических гипотез, вычисления известных статистических распределений и проведения корреляционного и регрессионного анализа.
Финансовые функции. Финансовые функции используются для решения финансовых и экономических задач. С помощью функций этой категории можно рассчитать приведенную стоимость (функция ПС), будущую стоимость (функция БС), оценить эффективность вложений при фиксированной процентной ставке, вычислить внутреннюю норму доходности и др. Анализ зависимости денежных сумм от времени, выполняемый с помощью финансовых функций, позволяет более обоснованно принимать решения экономического характера.
Функции для работы с базами данных. Функции этой категории используются для анализа данных, содержащихся в обычных электронных таблицах (списках данных) и таблицах Excel. С помощью функций баз данных можно быстро получить нужную информацию, например, подсчитать количество записей в базе данных (функция БСЧЁТ) или вычислить сумму значений (функция БДСУММ), удовлетворяющих некоторому условию. На ленте команд эта категория функций не отображается.
Вопросы для самоконтроля
1. Каково назначение подпрограммы Подбор параметра?
2. Назовите правила применения подпрограммы Подбор параметра?
3. Для чего и как создаются диаграммы?
4. Какие виды диаграмм вы знаете? Назовите для чего используется каждый вид диаграммы.
5. Что такое функции? Какова структура функции?
6. Назовите категории встроенных функций, используемых в Excel
Дата добавления: 2021-12-10; просмотров: 27; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!