На вкладке Граница окна Формат ячеек (рис.2.1) можно установить такие значения свойств границ ячеек: наличие всех границ или только отдельных, тип и цвет линий границ.
Значения свойств границ можно также установить, используя кнопку со списком Границы (ее название и вид изменяется в зависимости от последнего установленного значения этого свойства) группы Шрифт вкладки Главная на Ленте.
Используя элементы управления вкладки Заливка окна Формат ячеек или кнопку со списком Цвет заливки группы Шрифт вкладки Главная на Ленте,можно установить цвет фона ячейки, способ заливки, узор и его цвет.
На вкладке Защита окна Формат ячеек можно установить или отменить режимы защита ячеек и скрытие формул. Защита ячеек устанавливается для того, чтобы защитить данные от несанкционированного изменения, а скрытие формул – для того, чтобы данные не отображались в Строке формул. Для установки режимов защиты и скрытия нужно поставить метки соответствующих флажков: Защищаемая ячейка и Скрыть формулы, выбрать кнопку ОК, после чего выполнить Рецензирование - Изменения – Защитить лист или Главная - Ячейки - Формат - Защитить лист. В окне Защита листа, которое откроется, можно установить пароль для снятия режимов защиты и скрытия, а также установить разрешения на определенные операции при установленных режимах.
Функции Excel
Excel 2007имеет встроенную библиотеку функций, в которую входять больше чем 300 разнообразных функций. Все они для удобства поиска распределены по группам (категориям): математические, статистические, логические, финансовые, текстовые и др.
Функция имеет имя и результат, есть функции с аргументами и без аргументов. Аргументом функции может быть число, текст (его нужно заключать в двойные кавычки), выражение, ссылка на ячейку или диапазон ячеек, результат другой функции. При использовании функции в формуле сначала указывается ее имя, а затем, если функция имеет аргументы, в скобках указывается список аргументов через точку с запятой. Если функция не имеет аргументов, то в скобках после имени функции ничего не указывается.
Вставить функцию в формулу можно несколькими способами:
- использовать список функций кнопки категории функций в группе Библиотека функций вкладки Формулы на Ленте (рис.2.2);
- выполнить Формулы - Библиотека функций - Вставить функциюили выбрать кнопку Вставить функциюСтроки формул;
- ввести функцию непосредственно в ячейку или в поле Строки формул;
Рис.2.2 – вставка функции
Если выполнить Формулы - Библиотека функций – Вставить функцию или выбрать кнопку Вставить функциюСтроки формул, то откроется окно Мастер функций (рис.2.3).
Рис.2.3 – Мастер функций
В этом окне в списке поля Категория можно выбрать нужную категорию, после чего в списке поля Выберите функцию выбрать нужную функцию. После выбора кнопки ОКоткрывается окно Аргументы функции. Если функция имеет фиксированное количество аргументов, то окно Аргументы функции сразу содержит соответствующее количество полей для их ввода. Если функция имеет нефиксированное количество аргументов, то в окне сначала появляется несколько полей, а затем, в процессе ввода аргументов, появляются следующие поля.
Математические функции
Суммирование =СУММ (числа), числа – список из не более, чем 30-ти аргументов, каждый из которых число, формула или ссылка на ячейку, содержащую числовое значение.
=СУММЕСЛИ (интервал; критерий; суммируемый интервал) - суммирует значения в ячейках, содержимое которых удовлетворяет заданному критерию. 3-й аргумент – суммируемый интервал – необязательный, используется для поиска значений в таблице. В качестве критерия могут использоваться константы, а так же операции отношения: >, <, >=, <=
Округление =ОКРУГЛ(число; десятичные знаки), число – округляемое значение; десятичные знаки – целое число определяет разряд округления. Может быть: >0 – округление происходит в дробной части числа; =0 - число округляется до целого; <0 - округление происходит в целой части числа.
Произведение =ПРОИЗВЕД(число1;число2...) перемножает все числа, задаваемые ее аргументами. Может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.
Остаток от деления =ОСТАТ(число; делитель) Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.
Корень =КОРЕНЬ(число). Аргумент число должен быть положительным числом. Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.
Статистические функции
Используются для обработки данных.
=СРЗНАЧ (числа) - вычисляет среднее арифметическое для последовательности чисел. Игнорирует пустые, логические, текстовые ячейки. Может содержать до 30-ти аргументов.
=МАКС (числа) и =МИН (числа) соответственно возвращает максимальное и минимальное значения для заданной последовательности чисел.
= МОДА (числа) – возвращает наиболее часто встречающееся значение во множестве чисел.
=СЧЕТ (интервал) и =СЧЕТЗ (интервал) вычисляет в заданном интервале количество ячеек, содержащих числа, даты, формулы. СЧЕТЗ вычисляет количество заполненных ячеек.
=СЧЕТЕСЛИ (интервал; критерий) подсчитывает количество ячеек, содержимое которых удовлетворяет критерию поиска.
Логические функции
=И, ИЛИ, НЕ – аргументами функций являются логические выражения; функции возвращают значения логических констант: ИСТИНА и ЛОЖЬ. Функция НЕ имеет один аргумент: =НЕ(истина) возвращает ЛОЖЬ. =НЕ(В2>5) возвращает ИСТИНА, если в ячейке В2 содержится значение, меньшее 5, иначе – ЛОЖЬ. И, ИЛИ могут содержать до 30 аргументов. Аргументами функций И, ИЛИ, НЕ могут быть логические функции. Используются для построения сложных логических выражений.
=ЕСЛИ - используется, когда, в зависимости от значения логического выражения, выполняются те или иные вычисления. =ЕСЛИ (логическое выражение; значение «истина»; значение «ложь»). Вложенные функции ЕСЛИ используются для построения сложных условий.
=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))
Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.
Использование функции ЕСЛИ наделяет формулу способностью “принимать решения”. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения: = Равно, > Больше, < Меньше, >= Больше или равно, <= Меньше или равно, <> Не равно. Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Текстовые функции
=ДЛСТР( текст ) - возвращает количество символов в текстовой строке. Аргумент текст должен быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку.
= ЗАМЕНИТЬ (старый_текст; нач_ном; число_знаков; новый_текст) – заменяет часть текста строки на другой текст. Старый_текст - текст, в котором заменяют некоторые символы; нач_ном - позиция символа в тексте «старый_текст», начиная с которой будет произведена замена; число_знаков - число символов в тексте «старый_текст», которые заменяются новым_текстом; новый_текст - текст, который заменяет символы в тексте «старый_текст»
=ЛЕВСИМВ (текст; кол-во знаков) – возвращает указанное кол-во знаков с начала текста
=ПРАВСИМВ (текст; кол-во знаков) – возвращает указанное кол-во знаков с конца текстовой строки.
=СЦЕПИТЬ (текст1; текст2) – объединяет несколько текстовых строк в одну.
ПОСТРОЕНИЕ ДИАГРАММ
Создание диаграммы
Диаграмма – это графическое изображение, в котором числовые данные представляются в виде геометрических фигур. Диаграммы в Excel 2007 строятся по данным, представленным в электронной таблице. В Excel 2007 можно построить диаграммы 14 типов. Наиболее распространенными типами диаграмм являются гистограммы, круговые диаграммы и графики.
Гистограммы целесообразно создавать тогда, когда нужно сравнить значения нескольких наборов данных, графически изобразить отличия значений одних наборов данных по сравнению с другими. Круговые диаграммы рекомендуется использовать в случае, когда нужно отобразить части одного целого, сравнить соотношение частей и частей к целому. График целесообразно использовать, если количество данных в наборе достаточно большое, либо нужно отобразить динамику изменения данных во времени, сравнить изменения нескольких рядов данных.
Для построения диаграммы нужно выделить диапазон ячеек с данными, по которым будет строиться диаграмма. Выделенный диапазон ячеек может быть как связным, так и несвязным. Желательно, чтобы в него вошли подписи строк и столбцов.
После этого можно:
- нажать клавишу F11. В результате будет создан новый лист, расположенный перед листом с выделенными данными, и на нем будет построена диаграмма, тип, вид и значения свойств которой установлены по умолчанию.
- использовать элементы управления на Ленте:
1. Открыть на вкладке Вставка в группе Диаграммы список кнопки нужного типа диаграмм (рис.3.1).
Рис.3.1 - Группа Диаграммы вкладки Вставка
2. Выбрать необходимый вид диаграммы.
- использовать элементы управления окна Вставка диаграммы:
1. Выбрать на вкладке Вставка в группе Диаграммы кнопку открытия диалогового окна 
.
2. В окне Вставка диаграммы выбрать нужный тип и вид диаграммы.
3. Выбрать кнопку ОК.
В результате на листе с электронной таблицей будет построена диаграмма выбранного типа и вида, значения свойств объектов которой устанавливаются по умолчанию.
На ленте появится временный раздел Работа с диаграммами с тремя временными вкладками с элементами управления для работы с диаграммой: Конструктор, Макет и Формат. Вкладка Конструктор автоматически становится текущей. На ней в группе Макеты диаграмм можно выбрать нужный макет диаграммы, который определяет набор объектов диаграммы и их расположение, а в группе Стили диаграмм – стиль оформления диаграммы.
Иногда нужно к диапазону ячеек электронной таблицы, для данных которого уже построена диаграмма, добавить новые столбцы (строки) с данными или удалить существующие.
Чтобы изменить диапазон ячеек, по данным в которых построена диаграмма, нужно:
1. Выбрать диаграмму.
2. Выполнить Конструктор - Данные - Выбрать данные, либо использовать контекстное меню.
3. В окне Выбор источника данных (рис.3.2) можно добавить или удалить ряд, а также задать метки на горизонтальной оси с помощью кнопки Изменить.
Рис.3.2 – Окно «Выбор источника данных»
Можно отдельно форматировать следующие элементы диаграммы: область диаграммы; область построения; ряд данных; оси; названия; подписи данных; легенду (в легенде отображается список имен элементов данных, отображаемых на диаграмме и соответствующие им цвета).
Ряд данных - набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.
Категории – это значения независимой переменной, от которой зависят значения рядов данных, обычно откладываются на горизонтальной оси. Например, на графике значения категорий – это значения аргумента функции.
Для форматирования используется вкладка Макет.
Выбранные параметры макета будут применены к выделенному элементу. Например, если выделена вся диаграмма, метки данных будут применены ко всей последовательности данных. Если выделена единственная точка данных, метки данных будут применены только к выделенной последовательности данных или к выделенной точке данных.
Для форматирования оси необходимо выполнить двойной щелчок и в появившемся диалоговом окне «Формат оси» (рис.3.3) установить необходимые параметры.
Рисунок 3.3 – Формат оси
ОБРАБОТКА СПИСКОВ
Списки
Основным способом представления данных в Microsoft Excel является список.
Список в Excel – снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа. Списки можно рассматривать как внутренние базы данных, записями в которых являются строки, а полями – столбцы.
Первая строка списка список отводится для указания имен полей. При создании списка необходимо выполнять следующие правила:
1. На одном листе не следует помещать более одного списка;
2. Следует отделять список от других данных на листе, хотя бы одной пустой строкой и пустым столбцом;
3. Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат, рамку отличные от тех, которые используются для данных списка.
4. Чтобы отделить имена столбцов от данных, следует поместить рамку по нижнему краю ячеек первой строки.
Сортировка списков
Список можно отсортировать по возрастанию, по убыванию или в заданном пользователем порядке. Чтобы отсортировать весь список необходимо выделить список либо одну ячейку в списке и выбрать в на ленте команду Данные – Сортировка.
Рис.4.1 – Окно сортировки
Сортировка строк. В полях Сортироватьпо и Затем по необходимо указать (выбрать из раскрывающегося меню) имена сортируемых столбцов (ключи сортировки), порядок сортировки (возрастание или убывание) и нажать кнопку OK. Поле Затем по ( второй ключ сортировки ) используют, если в поле первого ключа есть одинаковые значения. Поле Затем по подключается нажатием кнопки Добавить уровень.
Сортировка столбцов. В окне команды Сортировка нажать кнопку Параметры. Установить переключатель Сортировать в положение Столбцы диапазона и нажать кнопку OK. Выбор строк, по которым требуется отсортировать столбцы производится в полях Сортировать по и Затем по.
Сортировка в задаваемом пользователем порядке. Требуемый порядок сортировки задается в окне Сортировка - Параметры. Для этогонеобходимовызвать раскрывающееся меню в поле Сортировка по первому ключу. Пользовательский (настраиваемый) список порядка сортировки необходимо создать заранее с помощью команды Сервис-Параметры-Списки либо из существующих на листе элементов (их необходимо выделить и нажать кнопку Импорт ), либо с помощью непосредственного ввода элементов списка.
Для быстрой сортировки на ленте на вкладке Данные находятся кнопки со стрелками вверх и вниз. Ключевым полем для сортировки в этом случае является столбец с выделенной ячейкой.
Применение фильтров
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.
Для этих целей есть две команды Автофильтр и Расширенный фильтр. Для применения фильтров необходимо выделить список или установить курсор на любую ячейку списка.
Автофильтр активизируется путем выбора команды Данные - Фильтр. В первой строке таблицы появляются кнопки с изображением стрелки. Щелкнув по такой кнопке, можно выбрать поле, для которого осуществляется поиск записей. Если в открывшемся окне задать любую константу, соответствующую значению в поле, в результате на выходе фильтра оказывается только одна запись. Параметр Все восстанавливает в таблице все записи.
С помощью автофильтра можно устанавливать сложные пользовательские критерии. Для этого в раскрывающемся списке необходимо выбрать строку Числовые (текстовые) фильтры, Настраиваемый фильтр, в результате чего появится диалоговое окно.
Левые поля диалогового окна задают операцию отношения, которая выбирается из меню (больше, меньше, равно, … для чисел, «содержит», «не содержит»… для текстов). Правые поля должны содержать сравниваемые значения. Можно использовать символы «?» и «*» вместо любого символа или группы символов соответственно. Критерии типа “И”,“ИЛИ” могут быть заданы для отдельного поля. Если задать условия для нескольких полей, то будут выбраны записи, для которых одновременно выполняются заданные условия (критерий “И”). Пример использования автофильтра приведен на рис.4.2.
Рис.4.2. Выбор строк, для которых значения в поле «Доход» находятся в диапазоне 2000…3000.
При необходимости использовать более сложные критерии отбора записей используется Расширенный фильтр (команда Данные – Дополнительно). В отличие от автофильтра результаты фильтрации можно вынести в другое место рабочего листа.
Чтобы воспользоваться Расширенным фильтром, необходимо предварительно создать таблицу критериев, расположив ее на том же листе. Обычно для этого копируют имена полей списка на свободное пространство. В ячейку, расположенную под именем поля, для которого осуществляется поиск записей, записывается условие отбора. Пример использования расширенного фильтра приведен на рис 4.3.
Рис.4.3 – Выбор строк, для которых значения в поле «Доход» превышают 2000 и название фирмы начинается на «Ю».
При использовании расширенного фильтра необходимо выделить область, содержащую данные (A2:B4). Активизировать фильтр путем выбора пункта Данные - Дополнительно. Подтвердить область исходной таблицы и указать диапазон критерияD2:E3.На выходе фильтра появятся записи, соответствующие заданному критерию.
Результирующую таблицу можно помещать, как на место исходного списка, так и вне его, задав в диалоговом окне левую верхнюю ячейку будущей таблицы результата.
Аналогично в ячейку под соответствующей меткой столбца можно вставить другой критерий. При этом критерию “И” соответствует несколько условий, задаваемых в одной строке (для нескольких полей). Несколько условий в различных строках интерпретируются как критерий “ИЛИ”.
Расширенный фильтр используется и в том случае, если требуется вычисляемый критерий. Для записи такого критерия нужна формула. К формулам в таблице критериев расширенного фильтра предъявляются следующие требования:
1) формула должна выводить логическое значение ЛОЖЬ или ИСТИНА. После выполнения поиска на экран выводятся только те строки, для которых результатом будет ИСТИНА;
2) формула должна ссылаться хотя бы на один столбец (первое значение в столбце) в списке
3) в таблице критериев заголовок над формулой не выполняется, но пустая ячейка над формулой включается в диапазон критериев.
Примерный вид вычисляемого критерия: =Е4>СРЗНАЧ($E$4:$E$18)
где Е4:Е18 данные для вычисления среднего, используется абсолютная адресация ячеек (знак $).