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

БЕТАРАСП	Возвращает интегральную функцию плотности бета - вероятности
БИНОМРАСП	Возвращает отдельное значение биномиального распределения
ВЕРОЯТНОСТЬ	Возвращает вероятность того, что значение из интервала находится внутри заданных пределов
ГАММАНЛОГ	Возвращает натуральный логарифм гамма - функции
ГАММАОБР	Возвращает обратное гамма-распределение
ГАММАРАСП	Возвращает гамма-распределение
ДИСП	Оценивает дисперсию по выборке
ДИСПА	Вычисление несмещенной дисперсии (учитываются текстовые и логические значения)
ДИСПР	Вычисляет дисперсию для генеральной совокупности
ДИСПРА	Вычисление смещенной дисперсии (учитываются текстовые и логические значения)
ДОВЕРИТ	Возвращает доверительный интервал для среднего генеральной совокупности
КВАДРОТКЛ	Возвращает сумму квадратов отклонений точек данных от их среднего
КОВАР	Возвращает ковариацию, то есть среднее произведений отклонений для каждой пары точек данных
КОРРЕЛ	Возвращает коэффициент корреляции между интервалами ячеек
ЛГРФПРИБЛ	Вычисляет экспоненциальную кривую, аппроксимирующую данные, и возвращает массив, описывающий эту кривую
ЛИНЕЙН	Используя метод наименьших квадратов, вычисляет прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные
ЛОГНОРМОБР	Возвращает обратную функцию логарифмического нормального распределения
ЛОГНОРМРАСП	Возвращает интегральное логарифмическое нормальное распределение
МАКС	Возвращает максимальное значение из списка аргументов
МАКСА	Возвращает наибольшее значение из набора (учитываются логические и текстовые значения)
МЕДИАНА	Возвращает медиану заданных чисел
МИН	Возвращает наименьшее значение в списке аргументов
МИНА	Возвращает наименьшее значение из набора (учитываются логические и текстовые значения)
МОДА	Возвращает наиболее часто встречающееся значение в массиве или интервале данных
НАИБОЛЬШИЙ	Возвращает k-ое наибольшее значение из множества данных
НАИМЕНЬШИЙ	Возвращает k-ое наименьшее значение в множестве данных
НАКЛОН	Возвращает наклон линии линейной регрессии для точек данных в аргументах
НОРМАЛИЗАЦИЯ	Возвращает нормализованное значение для распределения, характеризуемого средним и стандартным отклонениями
НОРМОБР	Возвращает обратное нормальное распределение для указанного среднего и стандартного отклонений
НОРМРАСП	Возвращает нормальную функцию распределения для указанного среднего и стандартного отклонений
НОРМСТОБР	Возвращает обратное значение стандартного нормального распределения
НОРМСТРАСП	Возвращает стандартное нормальное интегральное распределение
ОТРЕЗОК	Возвращает отрезок, отсекаемый на оси линией линейной регрессии, проведен- ной через точки данных, определяемых аргументами известные значения х и известные значения у
ПЕРЕСТ	Возвращает количество перестановок для заданного числа объектов, которые выбираются из общего числа объектов
ПРЕДСКАЗ	Возвращает предсказанное значение функции в точке х на основе линейной регрессии для массивов известных значений х и у или интервалов данных
ПРОЦЕНТРАНГ	Возвращает процентное содержание значения в множестве данных
ПУАССОН	Возвращает распределение Пуассона
РАНГ	Возвращает ранг числа в списке чисел
СРЗНАЧ	Возвращает среднее (арифметическое) своих аргументов
СРОТКЛ	Возвращает среднее абсолютных значений отклонений точек данных от среднего
СТАНДОТКЛОН	Оценивает стандартное отклонение по выборке
СТАНДОТКЛОНА	Оценивает стандартное отклонение по выборке с учетом текстовых и логических значений
СТАНДОТКЛОНП	Вычисляет стандартное отклонение по генеральной совокупности
СТАНДОТКЛОНПА	Вычисляет стандартное отклонение по генеральной совокупности с учетом текстовых и логических значений
СТЬЮДРАСП	Возвращает распределение Стьюдента
СЧЁТ	Подсчитывает количество чисел в списке аргументов
СЧЁТЕСЛИ	Подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию
СЧЁТЗ	Подсчитывает количество непустых значений в списке аргументов
ТЕНДЕНЦИЯ	Возвращает значения в соответствии с линейным трендом
ЧАСТОТА	Возвращает распределение частот в виде вертикального массива
ЭКСПРАСП	Возвращает экспоненциальное распределение
ЭКСЦЕСС	Возвращает эксцесс множества данных

Функции для работы со ссылками и массивами

АДРЕС	Создает адрес ячейки в виде текста, используя номер строки и номер столбца
ВЫБОР	Использует номер индекса, чтобы в брать и вернуть значение из списка аргументов-значений
ГИПЕРССЫЛКА	Создает ссылку, открывающую документ, который находится на жестком диске, сервере сети или INTERNET
ИНДЕКС	Выбирает по индексу значение из ссылки или массива
ОБЛАСТИ	Возвращает количество областей в ссылке. Область - это интервал смежных ячеек или отдельная ячейка
ПОИСКПОЗ	Возвращает относительную позицию элемента массива
ПРОСМОТР	Ищет значения в векторе или массиве
СМЕЩ	Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов
СТОЛБЕЦ	Возвращает номер столбца по заданной ссылке
СТРОКА	Возвращает номер строки, определяемый ссылкой
ТРАНСП	Возвращает транспонированный массив
ЧИСЛСТОЛБ	Возвращает количество столбцов в ссылке или массиве
ЧСТРОК	Возвращает количество строк в ссылке или массиве

Текстовые функции

ДЛСТР	Возвращает количество символов в текстовой строке
ЗАМЕНИТЬ	Заменяет часть текстовой строки на другую текстовую строку
ЗНАЧЕН	Преобразует текст в число
КОДСИМВ	Возвращает числовой код первого символа в текстовой строке
ЛЕВСИМВ	Возвращает первые (самые левые) символы текстовой строки
НАЙТИ	Находит вхождение одной текстовой строки в другую строку и возвращает номер символа, с которого начинается первое вхождение искомой строки
ПЕЧСИМВ	Удаляет все непечатаемые символы из текста
ПОВТОР	Повторяет текст заданное число раз
ПОДСТАВИТЬ	Подставляет в строку новый текст вместо старого
ПОИСК	Возвращает позицию первого вхождения символа или текстовой строки при поиске слева направо
ПРАВСИМВ	Возвращает последние (самые правые) символы текстовой строки
ПРОПИСИ	Делает все буквы в тексте прописными
ПГОПНАЧ	Первая буква в тексте и все первые бук- вы, следующие за символами, отличными от букв, делаются прописными (верхний регистр). Все прочие буквы в тексте де- лаются строчными (нижний регистр)
ИСТР	Возвращает заданное число символов из строки текста, начиная с указанной позиции
РУБЛЬ	Преобразует число в текст, используя денежный формат с округлением до заданного числа десятичных знаков
СЖПРОБЕЛЫ	Удаляет из текста все пробелы, за исключением одиночных пробелов между словами
СИМВОЛ	Возвращает символ с заданным кодом
СОВПАД	Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ - в противном случае
СТРОЧН	Преобразует символы в текстовой строке из верхнего регистра в нижний
СЦЕПИТЬ	Объединяет несколько текстовых элементов в один
Т	Возвращает текст, ссылка на который задается аргументом
ТЕКСТ	Преобразует значение в текст в заданном числовом формате

Логические функции

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

Имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ. Результатом работы логических функций И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ является логическое значение ИСТИНА или ЛОЖЬ, а результатом работы логической функции ЕСЛИ может быть число, текст или ссылка на выполнение каких-либо действий.

Результатом работы логических функций ИСТИНА() и ЛОЖЬ() являются логические значения ИСТИНА или ЛОЖЬ.

Функция НЕ(арг) изменяет логическое значение своего аргумента на противоположное. Она часто используется в тех случаях, когда необходима уверенность в том, что значение не равно

некоторой величине.

Аргумент функции арг - это значение или выражение, результатом вычисления которого является ИСТИНА или ЛОЖЬ. Если арг имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА, а если арг имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.

Функция И(арг1, арг2,...) возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Функция ИЛИ(арг1, арг2, ...) возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

Аргументы арг1, арг2, ... должны быть логическими значениями, массивами или ссылками, которые содержат логические значения. Если аргумент, который является ссылкой или массивом, содержит тексты или пустые ячейки, то такие значения игнорируются. Если указанный интервал не содержит логических значений, то возвращается значение ошибки #ЗНАЧ!

Функция ЕСЛИ(арг_лог, арг1, арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог при вычислении принимает значение ЛОЖЬ. При конструировании более сложных проверок в качестве значений аргументов арг1 и арг2 могут быть вложены до семи функций ЕСЛИ.

Диаграммы.

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

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

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

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

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

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

Кроме этого, для построения диаграммы можно воспользоваться командой Вставка => Диаграмма.

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

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

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

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

Выбор диаграммы.

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

Типы диаграмм:

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

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

Форматирование диаграмм:

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

К элементам, которые можно изменять, относятся:

- "пол" объемной диаграммы;

- "стенки" объемной диаграммы;

- стрелки;

- оси;

- диаграммы;

- серия данных (любая);

- линии сноски;

- линии сетки;

- габаритные линии;

- легенды;

- графические области;

- линии серии;

- текст;

- полосы прокрутки.

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

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

Ввод присоединенного текста заголовка. Для задания заголовков осей и/или всей диаграммы служит пункт меню Диаграмма | Параметры диаграммы. Во вкладке Заголовки нужно выбрать объекты, которые требуется озаглавить.

Перечень объектов различается в зависимости от типа диаграммы.

Такой текст, относящийся к конкретному объекту, входящему в состав диаграммы, называется присоединенным.

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

Ввод неприсоединенного текста диаграммы. В отличие от присоединенного текста, неприсоединенный текст легко перемещать в пределах диаграммы, так как он не привязан ни к одному из объектов. Для его ввода достаточно просто начать печатать в режиме редактирования диаграммы. Ввод текста завершается нажатием клавиши Еsс. После этого вокруг текста появляется рамка с маркерами, и текст можно форматировать стандартными средствами.

Краткий обзор команд меню и наиболее важных инструментов:

Для создания диаграмм обязательно надо определиться, соотношение между какими числовыми данными ей предстоит отображать. Следует учитывать следующие обстоятельства:

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

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

3. если рабочий лист содержит вычисляемые итоговые значения, то весьма вероятно, что их не захотите отображать на диаграмме.

Создание диаграммы.

Существует 15 типов диаграмм, имеющих несколько видов. Построение мастером диаграмм:

1. Выбрать диапазон.

2. Вставка => Диаграмма (Мастер диаграмм).

С помощью окон и вкладок выбирают параметры диаграммы. При наведении указателя – всплывает подсказка.

Элементы диаграммы

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

Диапазон – указывается диапазон ячеек, значение которых используется построением диаграмм.

Выбрать – тип и вид диаграмм.

Текст в колонках и строках – название колонок и строк.

Для редактирования диаграмм:

1. Активизировать диаграмму.

2. Щелкнуть по элементу

↑,↓ - перемещение между элементами

←,→выбор точек ряда

Имя выделенного объекта появляется в строке формул – в поле имя.

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

Формат ряда данных

Манипуляция данными.

Excel поддерживает несколько инструментальных средств СУБД: формы ввода, сортировка и т. д.

Сортировка данных.

1. Выбрать любую непустую ячейку.

2. Данные => Сортировка (появится окно «Сортировка диапазона»).

3. В списке «Сортировать по » указать поле, по которому сортируется рабочий лист.

4. Если выбран «Идентифицировать поля по подписям» (1-я строка диапазона), то в выпадающем списке приводятся названия столбцов первой строки; опция «обозначением столбцов листа», 1-я строка рассматривается как обычная запись – в списке: Столбец А …

5. Выбрать порядок сортировки: по возрастанию или по убыванию. Сортировка сразу по нескольким полям – используются списки «Затем по» и «В последнюю очередь, по». Указать в них поля, по которым надо упорядочивать данные, если значения предыдущих полей совпадут.

6. «ОК».

Быстрая сортировка: щелкнуть в любой ячейке столбца и по кнопке или .

При выполнении этой команды отображается диалоговое окно Сортировка диапазона, в котором имеются следующие основные разделы:

- Сортировать по - выбор столбца, по которому нужно сортировать в первую очередь;

- Затем по - определение столбца, по ячейкам которого следует выполнять сортировку в том случае, если равны соответствующие значения в столбце, указанном в поле Сортировать по.

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

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

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

Фильтрация.

1. Выбрать непустую ячейку; Данные => Фильтр => Автофильтр. В 1-ой строке столбца появится стрелка выпадающего списка. При помощи этих кнопок можно выбрать строки таблицы, которые необходимо вывести на экран.

2. Активизировать заголовок столбца, который надо отфильтровать.

3. < Alt+a> или стрелка выпадающего списка – критерий фильтра.

4. Указать критерий:

а) (Все) – отобразить все данные,

б) (Первые 10) – вывод записи с наибольшим (наименьшим) значением указанного поля (просмотр от 2 до 500 записей),

в) (Условие…) – использовать пользовательский Автофильтр - указывается то значение, которое «пропускается» через фильтр,

г) (Пустые) – отображает на экране записи, которые ничего не содержат,

д) (Непустые) – содержащие строки.

5. После выбора – выводятся отфильтрованные данные (числа становятся синими в номерах строк).

Опция (Все) – возврат всех записей.

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

Данные => Фильтр => Автофильтр (повторно) – возврат в обычный режим просмотра (без фильтра). Стрелки исчезнут.

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

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

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

Оба режима фильтрации вызываются в результате выбора команды Данные => Фильтр.

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

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

В частности, в левой части окна следует выбрать требуемое условие (равно, больше, меньше и т. д.), а в правой - либо выбрать значение из списка, либо задать собственное. Наряду с этим, при выделении какого-либо значения (а не команды) в раскрывающемся списке Excel временно скрывает строки, которые не содержат данный элемент. Для возвращения исходного списка следует выбрать команду Данные => Фильтр => Отобразить все или Данные => Фильтр => Автофильтр.

Команда Данные => Фильтр => Расширенный фильтр позволяет отыскивать строки с помощью более сложных критериев, по сравнению с пользовательскими автофильтрами.

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

Например, можно вывести на экран только те строки, у которых значение стоимости продажи больше, чем среднее за квартал.

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

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

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

В поле ввода Исходный диапазон указывается интервал, содержащий список, который подлежит фильтрации. В это поле следует вводить ссылки на таблицу или базу данных.

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

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

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

Объекты.

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

1. Вставка => Объект.

2. Выбрать тип объекта во вкладке «Создание» (запускается соответствующая программа, работающая с объектом).

Правка => Вставить – вставка объекта из буфера обмена.

3. Вкладка «Создание из файла» -указывается файл, в котором находится объект, который вставляется.

4. Вставка => Рисунок – вставка графики из графического файла: выбирается тип рисунка и выбирается сам рисунок, или указывается файл с рисунком, а в поле «Рисунок» отображается сам рисунок.

Создание несложных рисунков – панель инструментов «Рисование». Щелчок по объекту – выделяет его. Объект можно перемещать, удалить, очистить, скопировать.

Двойной щелчок над объектом – запуск программы, которая работает с ним.

Страницы: 1, 2, 3, 4