использование статистических функций

МАКС (функция МАКС)- Возвращает наибольшее значение из набора значений.

МАКСА (функция МАКСА)- Возвращает наибольшее значение в списке аргументов.

Функция МАКСА аналогична функции МИНА. Дополнительные сведения см. в примерах, приведенных для функции МИНА.

МИН (функция МИН)- Возвращает наименьшее значение в списке аргументов.

МИНА (функция МИНА)- Возвращает наименьшее значение в списке аргументов.

Функция СРЗНАЧ- Возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон A1:A20 содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.

СРЗНАЧА (функция СРЗНАЧА)- Вычисляет среднее арифметическое для значений заданных в списке аргументов.

Функция СЧЁТ- Функция СЧЁТ подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция используется для получения количества числовых ячеек в диапазонах или массивах ячеек.

Функция СЧЁТЗ- подсчитывает количество непустых ячеек в диапазоне.

Функция СЧЁТЕСЛИ-подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или в которых содержатся числа, большие или меньшие указанного значения.

РОСТ (функция РОСТ)- Рассчитывает прогнозируемый экспоненциальный рост на основе имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих значений x и y. Функцию РОСТ также можно использовать для аппроксимации существующих значений x и y экспоненциальной кривой.

ТЕНДЕНЦИЯ (функция ТЕНДЕНЦИЯ)- Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы "известные_значения_y" и "известные_значения_x". Возвращает значения y, соответствующие этой прямой для заданного массива "новые_значения_x".

58.

ГОД (функция ГОД)- Возвращает год, соответствующий заданной дате. Год определяется как целое число в диапазоне от 1900 до 9999.

Функция ДАТА- возвращает целое число, представляющее определенную дату.

ДЕНЬ (функция ДЕНЬ)- Возвращает день даты, заданной в числовом формате. День возвращается как целое число в диапазоне от 1 до 31.

СЕГОДНЯ (функция СЕГОДНЯ)-Возвращает текущую дату в числовом формате. Числовой формат даты — это код даты и времени, с помощью которого в Microsoft Excel производятся вычисления над датами и промежутками времени. Если до ввода этой функции для ячейки был задан формат Общий, результат будет отформатирован как Дата. Если должно отображаться число, выберите для ячейки Общий или Числовой формат.

Функция РАЗНДАТ

Показать все

Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция предназначена для обеспечения совместимости с Lotus 1-2-3.

Даты должны вводиться с использованием функцииФункция ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. следует воспользоваться выражением =ДАТА (2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.

 

 

59.

Функция ЕСЛИ- Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

ЕСЛИ (лог_выражение; значение_если_истина;значение_если_ложь)

Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения.

Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Например, если данный аргумент — строка «В пределах бюджета», а аргумент «лог_выражение» имеет значение ИСТИНА, то функция ЕСЛИ отобразит текст «В пределах бюджета». Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль). Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Аргумент «значение_если_истина» может быть формулой.

Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Например, если данный аргумент — строка «Превышение бюджета», а аргумент «лог_выражение» имеет значение ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней — закрывающая скобка), то возвращается значение 0 (ноль). Аргумент «значение_если_ложь» может быть формулой.

Функция И- Возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.

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

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

 

60.

СЦЕПИТЬ (функция СЦЕПИТЬ)- Функция СЦЕПИТЬ объединяет до 255 текстовых строк в одну. Объединяемые элементы могут быть текстом, числами, ссылками на ячейки или сочетанием этих элементов. Например, если в ячейке A1 листа содержится имя человека, а в ячейке B1 — его фамилия, объединить эти два значения в третней ячейке можно с помощью следующей формулы:

=СЦЕПИТЬ(A1," ",B1)

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

Функция ТЕКСТ- Функция ТЕКСТ преобразует численное значение в текст и позволяет задать формат отображения с помощью специальных строк форматирования. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами. Предположим, например, что в ячейке A1 содержится число 23,5. Чтобы отформатировать число как сумму в рублях, можно использовать указанную ниже формулу.

=ТЕКСТ(A1;"0,00 р.")

 

 

60. Текстовые значения

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

Например:=ТЕКСТ(ТДАТА();"Д МММ ГГГГ")

В этой формуле второй аргумент функции ТЕКСТ является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(NOW), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.

Функция СЦЕПИТЬ

Синтаксис:

СЦЕПИТЬ(текст1,текст2,...)

Результат: Объединяет несколько текстовых элементов в один.

Аргументы:

текст 1, текст2,... - от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

Функция ТЕКСТ

Синтаксис:

ТЕКСТ(значение, строка_формат)

Результат: Преобразует значение в текст в заданном числовом формате.

Аргументы:

значение - либо числовое значение, либо формула, вычисление которой дает числовое значение, либо ссылка на ячейку, содержащую числовое значение;

строка_формат - числовой формат в текстовой форме (вкладка Число диалогового окна Формат ячеек). Запрещено использование символа (*) и формата Общий.

61. Диаграммы - это графический способ представления числовых данных, находящихся на листе, удобный для анализа и сравнения.

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

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

При вводе данных:

1. не оставляйте незаполненных рядов и колонок. Если вы оставляете какую-либо область незаполненной, это затрудняет использование Мастер Диаграмм Excel. Предпочтительно вносить данные в диаграмму вместе.

2. введите данные в колонки, где это возможно. Расположите данные в рабочей таблице таким образом, чтобы имена, описывающие данные были в одной колонке, а справа от нее сами данные. Если у вас более одной группы данных, расположите их группа за группой в колонках с названием каждой группы сверху.

Две опции для выбора данных диаграммы.

1. выделите данные и зажмите курсор в правом нижнем углу выделенных данных.

2. убедитесь, что вы выделили все данные, которые необходимо включить в диаграмму.

Мастер Диаграмм в Excel 2003

1. Кликните значок Мастер Диаграмм на стандартной панели инструментов.

2. Выберите из меню Insert > Chart.

3. В появившемся окне выберите тип диаграммы слева.

4. Выберите подтип диаграммы справа. Если вы хотите создать необычную диаграмму, выберите вкладку Custom Types в верхней части окна диаграмм.

ТИПЫ ДИАГРАММ

Диаграммы с областями

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

 

Линейчатая диаграмма

Линейчатые диаграммы состоят из серий горизонтальных маркеров. Сравнивая длину этих маркеров, можно судить о том, насколько одна величина от другой в определенный период времени. Линейчатая диаграмма, представленная на рис., позволяет сравнить население городов в течении пяти периодов. Линейчатая диаграмма бывает нескольких видов: с отдельными значениями, составная, 100%-ная составная и объемная

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

 

Графики

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

Круговые диаграммы

Круговые диаграммы лучше всего использовать для того, чтобы показать соразмерность, или соотношение, частей и целого. Примеры круговых диаграмм приведены на рис. 2.20-2.22.

Кольцевые диаграммы

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

Точечные диаграммы

Точечные, или XY-точечные, диаграммы позволяют проследить зависимости между парами чисел. Одно число этой пары этой пары наносится на ось X, другое на - ось Y. Из засечек, соответствующих этим числам проводятся прямые, линии параллельные осям. В том месте, где эти линии пересекаются, ставится точка. Пример диаграммы приведен на рис. 2.24. Этот тип диаграммы может быть использован для построения графика функции с переменным шагом по оси ОХ.

Настройка диаграмм

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

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

Для того, чтобы добавить или удалить ряд данных на диаграмму необходимо: выделить на диаграмме данные, щелкнуть правой кнопкой мышки и выбрать команду Исходные данные, появиться окно мастера диаграмм (см. рис. 2.28). В этом окне можно добавлять новые ряды данных (кнопка Добавить) и удалять уже существующие (кнопка Удалить).

62. Прогнозирование с исп.диаграмм

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

Рассмотрим конкр.пример соз-дания диаграммы линии тренда:
1. Выделите данные своей базовой линии.

2. Щелкните на кнопке Мастер диаграмм, расположенной на стандартной панели инструментов, либо выберите команду Вставка→Диаграмма.

3. На первом шаге выберите тип диаграммы График и щелкните на кнопке Далее.

4. На втором шаге работы средства Мастер диаграмм проверьте правильность ссылок на ячейки базовой линии и щелкните на кнопке Далее.

5. На третьем шаге выберите параметры графика, включающего как линии, так и маркеры. Щелкните на кнопке Далее.

6. На последнем, четвертом, шаге работы мастера определите местоположение диаграммы: на отдельном или имеющемся листе. Щелкните на кнопке Готово.

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

8. Щелкните правой кнопкой мыши на ряде данных диаграммы и из появившегося контекстного меню выберите команду Добавить линию тренда.

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

10. Щелкните на кнопке ОК.

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

Формула массива создается по тем же правилам, что и обычная формула, только помещается в фигурные скобки { } и закрепляется сочетанием клавиш Ctrl+Shift+Enter.

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

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

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

 

63.

Списки

 

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

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

Создание списка

1. Выделите диапазон данных, из которых необходимо создать список.

ПРИМЕЧАНИЕ. Можно также выбрать диапазон ячеек, которые будут заданы в качестве списка, используя диалоговое окно Создание списка.

2. В меню Данные выберите пункт Список, а затем команду Создать список.

3. Если у выбранных данных есть заголовки, установите флажок Список с заголовками и нажмите кнопку OK.

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

Правила формирования списка:

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

· нельзя отделять строку заголовков от записей пустой строкой, разделять столбцы с заголовками пустыми столбцами, вставлять перед данными в полях пробелы;

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

· имя поля (заголовок столбца) может состоять из нескольких слов, но обязательно размещенных в одной ячейке;

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

Сортировка – это расположение записей списка по возрастанию или убыванию значений какого-либо столбца.

Строку с итоговыми данными следует отделить от сортируемого диапазона пустой строкой.

Сортировку можно выполнить:

 

а) кнопками панели инструментов (этот способ удобно использовать для сортировки списка по данным одного столбца);

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

Сортировка списка по данным одного столбца

Активизировать любую ячейку в столбце, значения которого требуется отсортировать.

В панели инструментов щелкнуть нужную кнопку:

- сортировать по возрастанию;

– сортировать по убыванию;

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

Варианты действий:

автоматически расширить выделенный диапазон – будут переставлены все строки списка;

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

Сортировка по данным нескольких столбцов

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

Выполнение вложенной сортировки

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

Выполнить команду меню Данные => Сортировка.

Откроется диалоговое окно Сортировка диапазона.

Указать столбцы сортировки в полях:

Сортировать по

Затем по

В последнюю очередь по

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

Если установлен переключатель Идентифицировать поля по подписям, то в полях окна отображаются подписи столбцов. При выборе второго переключателя (или при отсутствии подписей столбцов) поля идентифицируются по заголовкам столбцов Excel, т.е. Столбец A, Столбец B, Столбец C.

65. В Excel для работы со списком существуют средства, которые предназначены для обработки и анализа данных в списке. При применении команд (сортировка, фильтр, форма, итоги, проверка) для обработки списков, редактор автоматически распознает список Excel как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.

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

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

Excel содержит два варианта фильтрации: автофильтр и усиленный фильтр

66.. Автофильтр осуществляет быструю фильтрацию списка в соответствии с содержимым ячеек или в соответствии с простым критерием поиска. Активизация автофильтра осуществляется командой Фильтр — Автофильтр меню Данные (указатель должен быть установлен внутри области списка). Заглавная строка списка в режиме автофильтра содержит в каждом столбце кнопку со стрелкой. Щелчок раскрывает списки, элементы которого участвуют в формировании критерия. Каждое поле (столбец) может использоваться в качестве критерия. Список содержит следующие элементы.

1. Все — будут выбраны все записи.

2. Первые 10 — предназначены для создания нового списка, состоящего из 10 исходных или наибольших значений предыдущего списка. Число 10 устанавливается по умолчанию. При выборе этого элемента раскрывается окно Наложение условия по списку, в котором можно указать число элементов в создаваемом списке; определить, какие по значению элементы следует выбирать (наибольшие или наименьшие); установить численное или процентное ограничение на количество выводимых элементов. Процентное — задается доля выводимых элементов, имеющих наибольшие или наименьшие значения из всего списка.

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

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

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

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

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

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

Для поиска записей по двум условиям применяется "Пользовательский автофильтр":

 Выберите ячейку в списке

 Выполните команду "Данные" - "Фильтр" - "Автофильтр"

 Щелкните на кнопке автофильтра в том столбце, который используете для фильтрации данных

 Щелкните на команде "Условие", откроется окно диалога "Пользовательский автофильтр"

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

 Щелкните на кнопке ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям.Для примера выберем следующие условия отбора: показать в списке строки, содержащие продукты, цены которых больше или равны 69,80 грн и меньше 99,99 грн.

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

· возможность сохранения критериев и их многократного использования;

· возможность оперативного внесения изменений в критерии в соответствии с потребностями;

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

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

Фильтрация списка с помощью усиленного фильтра выполняется командой Фильтр — Расширенный фильтр меню Данные. В окне команды Расширенный фильтр следует указать:

1) в поле ввода Исходный диапазон — диапазон ячеек, содержащих список;

2) в поле ввода Диапазон условий — диапазон ячеек, содержащих критерий отбора;

3) в поле ввода Поместить результат в диапазон — верхнюю левую ячейку области, начиная с которой будет выведен результат фильтрации;

4) с помощью переключателя Обработка определить расположение результатов фильтрации на рабочем листе:

o Фильтровать список на месте — означает, что список остается на месте, ненужные строки скрываются;

o Скопировать результаты в другое место — позволяет расположить результаты фильтрации в другой области рабочего листа.

5) Параметр Только уникальные записи — означает, что выборка должна содержать только уникальные записи в соответствии с критериями отбора.

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

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

2. Критерий отбора содержит несколько условий, накладываемых на несколько столбцов (полей) одновременно. Здесь возможны следующие варианты:

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

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

o в диапазоне критерия нельзя указывать имена полей. Следует ввести новое имя заголовка или оставить ячейку пустой;

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

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

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

o вычисляемые критерии можно сочетать с невычисляемыми;

o не следует обращать внимание на результат, выдаваемый формулой в области критерия (обычно ИСТИНА или ЛОЖЬ).

Алгоритм фильтрации с помощью команды "Расширенный фильтр"

o Выделите ячейку в списке

o В меню "Данные" выберите команду "Фильтр" - "Расширенный фильтр"

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

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

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

o Щелкните на кнопке ОК для выполнения фильтрации. В списке будут отображены записи, удовлетворяющие заданным критериям

На рисунке представлен образец "Расширенного фильтра" выбранными параметрами фильтрации.

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

Выделите любую ячейку в списке и выберите в меню Данные - Форма (Data - Form). На экране появится форма:

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

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

Кнопка Критерии - выведет пустую форму, с помощью которой можно искать записи по введенным критериям.

Кнопка Удалить - удалит текущую запись (строку).

69. Работа со списками. подведение итогов

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

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

Команда Итоги может выполняться для одного и того же списка записей многократно. При каждом выполнении команды подводятся итоги по одному уровню группировки (по факультетам или группам) для одной операции. Ранее созданные итоги могут заменяться новыми или оставаться неизменными. Т.о. имеется возможность подведения итогов различных уровней вложенности.

Для правильного подведения итогов необходимо:

· данные должны быть созданы в виде списка, в котором каждое поле (столбец) имеет заголовок, расположенный в одной ячейке;

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

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

Создание промежуточных итогов:

· выделить любую ячейку списка;

· выполнить команду Данные – Итоги; появится ДО;

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

· выбрать операцию для каждой группы данных;

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

· указать поля, к которых будет выполнена выбранная операция (их может быть несколько), для этого установить флажки в списке Добавить итоги по;

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

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

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

· нажать ОК.

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

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

Удаление промежуточных итогов:

Выполнить команду: Данные – Итоги - Убрать все.

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

 

70. Сводные таблицы в списках

1. Работа со сводными таблицами

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

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

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

Нельзя добавить на листе новые строки и столбцы, если они пересекают область сводной таблицы, целостность этой области защищена.