Методические материалы с упражнеиями по Microsoft office Excel 2013

ТВОРЧЕСКАЯ РАБОТА

Методические материалы с упражнениями

по MICROSOFT OFFICE EXCEL 2013

 

 

Автор:

Студент группы 4293/42

Умудов Бахтияр Рагимович

специальность «Программирование
в компьютерных системах»

 

Руководитель творческой работы:

Сухорукова Ольга Алексеевна

преподаватель

«Информационных технологий»

 

САНКТ-ПЕТЕРБУРГ

2015 г.

 

Санкт-Петербургский политехнический университет

Петра Великого

Университетский политехнический колледж «Радиополитехникум»

 

Умудов б.р.

 

Методические материалы с упражнеиями по Microsoft OFFICE Excel 2013

Основы работы в MS excel 2013

 

Санкт-Петербург

Оглавление

Интерфейс программы MS Excel 2013. 2

Вкладки. 5

Ввод и редактирование данных в ячейках. 6

Ввод данных. 6

Редактирование данных. 7

Способы ускорения ввода данных. 7

Форматирование ячеек. 9

Ввод формул.. 15

Функции.. 19

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

Построение графиков и диаграмм... 26

Форматирование и изменение диаграмм.. 28

Форматы чисел и данных. 33

Работа с базами данных. 36

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

Автофильтр. 37

Расширенный фильтр. 38

Сводная таблица. 39

Макросы.. 43

Наиболее частые употребляемые функции.. 44

Лабораторные работы.. 45

Лабораторная работа № 1 «Сведения об успеваемости студентов». 46

Лабораторная работа № 2 «Создание и заполнение таблицы постоянными данными и формулами.». 48

Лабораторная работа № 3 «Создание и форматирование диаграмм». 50

Лабораторная работа № 4 «Создание и форматирование диаграмм (Продолжение)». 53

Лабораторная работа №5 «Построение ведомости заработной платы». 55

Лабораторная работа № 6 «Базы данных». 57

Лабораторная работа № 7 «Адрес ячейки, абсолютные и относительные ссылки, формулы». 59

Лабораторная работа № 8 «Формулы, автосумма, диаграммы». 62

Лабораторная работа № 9 «Создание аналитических таблиц. Вычисления с помощью мастера функций.». 66

Лабораторная работа № 10 «Создание составного документа путем слияния». 70

Лабораторная работа № 11 «Списки, сортировка, копирование, вырезание ячеек, строк». 72

Лабораторная работа №12 «Создание и использование макросов». 77

Лабораторная работа № 13. "Список. Сортировка данных". 80

Лабораторная работа № 14 «Фильтрация данных». 81

 

 

Интерфейс программы MS Excel 2013

 

Лента

Вкладки

Панели быстрого доступа

Поле имени текущей ячейки

Группа

Строка формул

Табличный маркер

Текущая ячейка

Заголовки столбцов

Заголовки строк

Полосы прокрутки

Масштаб

Режимы работы с документами

Добавить новый лист

Ярлыки листов

Навигация по листам

Вкладки

 

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

· Вкладка Главная содержит элементы, которые понадобятся для создания, форматирования и редактирования электронных таблиц.

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

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

· Вкладка Формулы нужна для добавления формул и функций в электронную таблицу.

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

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

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

· Вкладка Разработчик предназначена для более опытных пользователей. Именно здесь можно перейти в режим работы с макросами (макрос – это программа, созданная пользователем на языке программирования Visual Basic For Application и предназначенная для расширения имеющейся функциональности приложения), а также вставить в документ те либо иные элементы управления, отредактировать шаблоны и др. Обратите внимание, что данная вкладка отображается на ленте Word 2013 только в том случае, если в настройках программы в разделе Основные установлен флажок Показывать вкладку «Разработчик» на ленте.

?

Какая вкладка предназначена для установки параметров страницы

_________________________________________________________

Какая предназначена для импорта данных из других источников?

_________________________________________________________

Где можно перейти в режим работы с макросами?

_________________________________________________________

 

 

Ввод данных

Для ввода данных в ячейку щелчком сделайте ячейку текущей и начинайте вводить данные (текст, число и т.д.).

Для завершения ввода, нажмите:

1. Enter для перехода к ячейке вниз.

2. Tab для перехода к ячейке вправо.

3. Shift + Enter для перехода к ячейке вверх.

4. Shift + Tab для перехода к ячейке влево.

5. Для отмены ввода нажмите Esc.

 

Редактирование данных

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

 

Чтобы перенести часть строчи текста на другую строчку внутри ячейки, щелкните в месте разрыва строки, а затем Alt + Enter.

 

Выделение данных:

Выделение всех ячеек

Выделение произвольного диапазона ячеек

Выделение строк

Выделение столбцов

 

Для удаление данных выделите ячейку или диапазон ячеек и нажмите Delete.

 

Перемещение данных ячеек.

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

 

Копирование данных ячеек.

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

 

Дублирование данных.

Выделить ячейки, в которые хотите ввести одинаковую информацию. Введите в активную ячейку данные и нажмите Ctrl + Enter.

Автозаполнение.

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

Маркер автозаполнения

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

 

 

?

Как перейти в ячейку слева?

_______________________________________________________

Как удалить данные из ячейки? (одной клавишей)

_______________________________________________________

Как выполнить автозаполнение?

_______________________________________________________

 

 

Форматирование ячеек

Выравнивание по горизонтали:

По центру

По левому краю

По правому краю

По верхнему краю

Выравнивание по вертикали:

По центру

По нижнему краю

 

Ориентация:

Кнопка ориентации

Кнопка переноса по словам

Перенос по словам:

Объединение ячеек:

Кнопка объединения ячеек

Аналогично выравнивания ячеек можно настроить в окне Формат ячеек вкладка Выравнивание (через кнопку открытия группы).

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

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

Применение цвета и узоров

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

 

?

Где можно настроить выравнивания ячеек?

_____________________________________________________________

Как сделать границу ячейки жирной?

_____________________________________________________________

Что позволяет задать фон для выделенных ячеек?(пропишите путь)

___________________________________________________________________

 

Ввод формул

 

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

Функции

После изучения раздела выполните Лаб. Раб. № 1

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

Например, определение среднего значения пяти ячеек можно описать формулой: =(A1+A2+A3+A4+A5)/5, а можно специальной функцией СРЗНАЧ, которая сократит выражение до следующего вида: СРЗНАЧ(А1:А5). Как видите, что вместо ввода в формулу всех адресов ячеек можно использовать определенную функцию, указав ей в качестве аргумента их диапазон.

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

 

Надо отметить, что программа содержит более двухсот функций, способных облегчить выполнение вычислений различной сложности. Поэтому все функции в Excel 2013 разделены на несколько категорий, группирующих их по типу решаемых задач. Какие именно эти задачи, становится ясно из названий категорий: Финансовые, Логические, Текстовые, Математические, Статистические, Аналитические и так далее.

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

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

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

Многие функции могут иметь сразу несколько аргументов. В таком случае, каждый из них отделяется от следующего точкой с запятой. Например, функция =ПРОИЗВЕД(7; A1; 6; B2) считает произведение четырёх разных чисел, указанных в скобках, и соответственно содержит четыре аргумента. При этом в нашем случае одни аргументы указаны явно, а другие, являются значениями определенных ячеек.

Так же в качестве аргумента можно использовать другую функцию, которая в этом случае называется вложенной. Например, функция =СУММ(A1:А5; СРЗНАЧ(В5:В10)) суммирует значения ячеек находящихся в диапазоне от А1 до А5, а так же среднее значение чисел, размещенных в клетках В5, В6, В7, В8, В9 и В10.

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

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

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

 

Так же кнопку Вставить функцию вы найдете на ленте сверху в группе Библиотека функций во вкладке Формулы. Еще одним способом вызова мастера функций является сочетание клавиш Shift+F3.

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

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

Сделав необходимый выбор, щелкните по кнопке ОК, после чего появится окно Аргументы функции.

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

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

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

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

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

 

?

Какой функцией можно заменить формулу

=(K1+K2+K3+K4+K5)/5 ____________________________________

=(A1+B1+C1+D1+E1)/5 ____________________________________

Опишите подробный порядок применения ввода функции.(соблюдя синтаксис)

_________________________________________________________

_________________________________________________________

_________________________________________________________

Может ли аргумент принимать вид времени и даты?

________________________________________________________

Каким знаком делятся аргументы, в случае, если их несколько?

________________________________________________________

Как называется помощник по составлению функций?

______________________________________________________________

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

После изучения данного раздела выполните Лаб. Раб. №2

 

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

= Равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ (IF) имеет следующий синтаксис:

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

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

=ЕСЛИ(А1>3;10;20)

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")

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

Например:

=ЕСЛИ(СУММ(А1:А3)=30;А10;"")

Аргумент логическое выражение функции ЕСЛИ может содержать текстовое значение. Например:

=ЕСЛИ(А1="Динамо";10;290)

Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

 

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)

 

 

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

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

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

Вложенные функции ЕСЛИ

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

=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

Алгоритм работы этой формулы:

 

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

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

=ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:

=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")

В противном случае формула возвратит "Стоп".

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

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

 

?

Что является результатом логического выражения

_______________________________________________________________

Что может быть аргументами функций И, ИЛИ, НЕ?

_______________________________________________________________

Опишите роль Вложенной функции ЕСЛИ

______________________________________________________

______________________________________________________

Для чего нужна функция ЕПУСТО? И чем может являться аргумент?

____________________________________________________________________

____________________________________________________________________

 

Форматы чисел и данных

После изучения данного раздела выполните Лаб. Раб. № 5, 7, 8, 9

Для изменения формата чисел и даты выполните действие: вкладка Главная ® группа Число.

 

 

Название формата	Пример	Что делает
Числовой		ü Количество десятичных знаков ü Разделитель между разрядами
Денежный		ü Количество десятичных знаков ü Обозначение валюты
Финансовый		ü Количество десятичных знаков ü Обозначение валюты
Дата		ü Изменяет отображение даты
Время		ü Изменение языка (местоположения) ü Изменяет формат часов
Процентный		ü Количество десятичных знаков
Дробный		ü Запись дробного числа в обычном виде
Экспоненциальный		ü Запись числа в экспоненциальном виде

Работа с базами данных

После изучения данного раздела выполните Лаб. Раб. № 6, 13, 14

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

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

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

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

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

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

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

Чтобы отсортировать список надо:

1. Установить курсор в ячейку списка.

2. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.

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

 

Автофильтр

Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.

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

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

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

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров, которые позволяют:

· Задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;

· Вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;

· Определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;

· Самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

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

Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.

Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.

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

Знак	Значение
?	один любой знак
*	любое количество символов
~	используют, когда в тексте надо найти подстановочные знаки (символы «?», «*» или «~»)

Расширенный фильтр

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

Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии.

Поскольку при вводе текста или значения в ячейку знак равенства (=) используется для обозначения формулы, то Microsoft Excel вычисляет введенную формулу; однако это может привести к неверным результатам отбора. Чтобы указать оператор сравнения для текста или значения, введите условие в виде строкового выражения в соответствующую ячейку диапазона условий отбора:

=''= ввод '',

 

Где ввод — искомый текст или значение.

В ЯЧЕЙКУ ВВОДИТСЯ	EXCEL ВЫПОЛНЯЕТ И ОТОБРАЖАЕТ
="=Белов"	=Белов
="=3 000"	=3 000

Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных.

Критерии сравнения – это набор условий для поиска, используемый для извлечения данных при запросах по примеру. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700).

Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =F7>СРЗНАЧ($F$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА. При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА.

Порядок выполнения расширенной фильтрации:

1. Скопируйте шапку таблицы в другое место;

2. Укажите критерии для фильтрации;

3. Вкладка Данные ® группа Сортировка и фильтр ® Дополнительно

4. Надо указать исходный диапазон (таблица) и диапазон условий (таблицу с критериями).

 

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

Сводная таблица

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

Сводная таблица в Excel 2013 используется для:

· выявления взаимосвязей в большом наборе данных;

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

· нахождения повторяющихся элементов, детализации и т.п.;

· создания удобных для чтения отчетов, что является самым главным.

Для чего нужен автофильтр?

_____________________________________________________________

Макросы

После изучения данного раздела выполните Лаб. Раб. № 12

 

Макрос – это команда (действие) или набор команд (действий), с помощью которых можно автоматизировать часто выполняемые в MS EXCEL задачи. Команды по выполнению задачи последовательно автоматически!!! записываются по мере их совершения на языке программирования Visual Basic. При создании макроса записываются щелчки мышью и нажатия клавиш. После завершения записи макроса его можно выполнить любое количество раз, например, для обработки различных исходных данных.

Создание макроса

1. Выполните команду вкладка Вид ® Макросы®Запись макроса, дайте имя макросу, нажмите Ок. Будьте внимательно. С этого момента идет запись каждого вашего действия.

2. Создайте Таблицу.

3. Выполните команду Сервис-Макрос-Остановить запись.

 

Выполнение макроса

Выполняется макрос командой: вкладка Вид ® Макросы ® (имя макроса) ® Выполнить. И после должна появится таблица, которую вы сделали.

 

?

Для чего нужен макрос?

___________________________________________________________

___________________________________________________________

Лабораторные работы

 

Лабораторная работа № 1 «Сведения об успеваемости студентов»

Задание.

1. Подготовьте таблицу сведений об успеваемости студентов в MS EXCEL (Таблица 1). Для объединения ячеек использовать кнопку (объединить и поместить в центре). Для ввода в одну ячейку двух строк