2. табличный процессор ms excel

• Название: 2. табличный процессор ms excel
• Анкор: excel2007.docx
• Дата: 26.08.2018
• Размер: 0.69 Mb.
• Имя файла: excel2007.docx
• Тип: Документы; #23574
• страница: 7 из 12

2. 11. СВЯЗЫВАНИЕ И КОНСОЛИДАЦИЯ ДАННЫХ

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

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

Консолидацию данных можно выполнить различными методами, в том числе, используя:

• 
  формулы связи, содержащие внешние ссылки;
  
• 
  формулы, содержащие функции связи;
  
• 
  команду Консолидация в меню Данные.
  

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

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

Например, просуммировать значения из ячеек С5 листов1,2 и 3. Результат поместить в ячейку С5 листа 4 одной и той же рабочей книги.

В ячейку Лист4!С5 введена формула = Лист1!С5 + Лист2!С5 + Лист3!С5

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

= СУММ(Лист1! : Лист5!С5)

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

Объединение данных с помощью команды Консолидация во вкладке Данные.При этом консолидация может выполняться двумя способами: без связи с исходными данными и со связью с исходными данными.

Правила выполнения консолидации:

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

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

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

4. При выполнении консолидации со связью с исходными данными, в итоговых ячейках будет автоматически введена формула. То есть при изменении исходных данных автоматически будет изменен результат консолидации. Кроме того, при данном способе слева от заголовков строк, должны появиться символы структуры в виде «+» – плюсов. Нажав на «+», раскрываются исходные данные, которые были консолидированы.

Порядок выполнения консолидации:

• 
  выделите итоговые ячейки;
  
• 
  в меню Данные выберите команду Консолидация;
  
• 
  в диалоговом окне Консолидация задайте необходимые параметры.
  

Первоначально, очистите окно Список диапазонов, используя кнопку Удалить.

В раскрывающемся списке Функция выделите функцию для консолидации (Сумма, Среднее и т.д.).

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

Включите или отключите флажок Создавать связи с исходными данными.

После указания всех параметров в окне Консолидация нажмите на кнопку ОК.

2.12. РАБОТА СО СПИСКОМ

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

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

Данные, организованные в список, часто называют Базами Данных табличного типа.

Столбцы списка называют полями. Поля имеют свои названия (заголовки). Поля бывают простые и вычисляемые. Строки списка называются записями.

К основным операциям, которые можно выполнять со списком относятся:

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

Основные правила создания списка

1. 
   Заголовки (по одному для каждого столбца) следует помещать в первую строку списка. Она называется строкой заголовков. Если заголовки длинные, необходимо использовать опцию Переноса по словам, чтобы не расширять столбец.
   
2. 
   В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
   
3. 
   Нельзя использовать пустые строки в списке. Пустая строка означает конец списка.
   

Желательно помещать список на отдельном листе.

4. 
   Используйте команду Вид - Окно – Закрепить области,
   

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

5. 
   Данные в столбцах должны иметь один формат отображения (формат Дата, Общий, Числовой и т.д.).
   

2.12.1. Ввод, редактирование, поиск данных в списке

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

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

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

При поиске можно применять символы * и ?, а также операторы сравнения: =, < >, >, <, < =, >=.

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

Например: в поле ФИО введено М* –, то есть фамилия начинается на букву М, в поле Год рождения – < 31.12.91.

2.12.2. Использование фильтров

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

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

Фильтрацию данных можно выполнять с помощью Автофильтра и Расширенного фильтра. Автофильтр применяется для простых условий отбора; Расширенный фильтр – для более сложных условий отбора.

Работа с автофильтром. Первоначально следует выделить любую ячейку внутри списка. Затем в меню Данные - Сортировка и фильтр выбрать команду Фильтр. MS Ехсе1 выведет рядом с каждым заголовком столбцов кнопки Автофильтров в виде стрелок. После щелчка по кнопке Автофильтра откроется список команд с помощью, которых можно задать условия отбора данных.

Команда Первые 10 позволяет найти заданное число или процент наибольших или наименьших элементов в списка.

Команда Условия предназначена для фильтрации списка одновременно только по двум критериям отбора, которые задаются в диалоговом окне. Для соединения условий используется логические операции И и ИЛИ. Логическая операции И используется очень часто для задания числового диапазона, например, от 10 до 100.

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

Отменить автофильтр можно двумя способами: выбрать команду Все в раскрывающемся списке Автофильтра, либо с помощью команды Данные – Фильтр – Отобразить все.

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

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

• 
  задать условия, соединенные логическим оператором ИЛИ для одного или нескольких столбцов;
  
• 
  задать вычисляемые условия,
  
• 
  перемещать копии строк, отвечающих определенному критерию, в другое место от списка.
  

Недостатки Расширенного фильтра. Для использования Расширенного фильтра требуется выполнять больше подготовительных действий, а именно: создавать блок ячеек с критериями отбора (блок критериев отбора).

Порядок создания Расширенного фильтра:



создать диапазон критериев;

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

Блок критериев (диапазон условий) – это специально отведенная область листа рабочей книги, отвечающая следующему требованию:

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

В диапазоне критериев можно ввести любое количество условий, MS Ехсеl их понимает в соответствии со следующими правилами:

• 
  условия на одной строке в разных столбцах считаются соединенными логическим оператором И;
  
• 
  условия на разных строках считаются соединенными логическим оператором ИЛИ;
  
• 
  пустая ячейка в диапазоне критериев означает любое значение для соответствующего столбца;
  
• 
  в Расширенный фильтр нельзя включать пустые строки.
  

Типы критериев. Элементы, которые указываются в диапазоне критериев, можно разделить на текстовые или числовые критерии. При фильтрации используется сравнение с числом или строкой с помощью операторов сравнения: =, < >, >, <, >=, <=. Например, <500, > К – слова, начинающиеся на Л до Я, <> С – все слова, кроме слов, начинающихся на С. В критериях допускается использование символов * и ?.В текстовых сравнениях не различаются строчные и прописные буквы. Например, Сильный, сильный.

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

В Расширенном фильтре можно использовать любое количество вычисляемых критериев, а также сочетать вычисляемые и не вычисляемые критерии.

Правила создания блока критериев с простыми (не вычисляемыми) условиями:

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

Правила создания блока критериев с вычисляемыми условиями:

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

Примечания:

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

Пример блока критериев с вычисляемым условием представлен в таблице.

Таблица 2.4.

Пример блока критериев с вычисляемым условием (ячейки А50:А51)

	А	Пояснения
50	Вывести	Заголовок над вычисляемым условием
51	=С2 > СРЗНАЧ($С $2: $C$40)	Условие отбора записей. С2 – первая ячейка столбца отбора.
	Истина или Ложь	Результат вычислений в ячейке А51

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

Копирование отфильтрованных строк на другой лист. Чтобы поместить результат фильтрации в другой лист рабочей книги, необходимо обязательно на этом же листе создать диапазон критериев отбора. Сам список данных может находиться на любом другом из листов рабочей книги. Затем выполнить команду Расширенный фильтр – Фильтр в меню Данные. При выполнении данной команды первоначально должен быть обязательно активным (то есть отображаться на экране) тот лист, где расположен блок критериев.

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

2.12.3. Сортировка списков

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

Сортировка бывает следующих видов: простая сортировка – по одному столбцу; сложная сортировка – по нескольким столбцам (не более трех); сортировка столбцов/полей.

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



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



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

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

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

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

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

Правила сортировки. Список элементов в порядке убывания их значимости при выполнении сортировки по возрастанию.

1. 
   Числа сортируются от наименьшего отрицательного к наибольшему положительному. Даты и время обрабатываются как числа. Во всех случаях при сортировке обрабатывается реальная величина, а не ее внешний вид после форматирования.
   
2. 
   Текст. Сортируется в алфавитном порядке. По умолчанию во время сортировки строчные и прописные буквы не различаются. Однако есть возможность изменить это в окне Параметры сортировки.
   
3. 
   Логические значения. Ложь следует за истиной.
   
4. 
   Ошибочные значения. Появляются в их первоначальном порядке и не сортируются по типу ошибок.
   
5. 
   Пустые ячейки всегда появляются последними (в конце списка) как при сортировке по возрастанию, так и по убыванию.
   

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

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

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

Порядок действий:

• 
  выполните сортировку необходимого столбца;
  
• 
  установите курсор внутри списка. В меню Данные выберите команду Промежуточные итоги;
  
• 
  в диалоговом окне задайте необходимые параметры и нажмите ОК.
  

В диалоговом окне задаются следующие параметры:

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

Операция – данный раскрывающий список включает набор из 11 функций. Функция СУММА установлена первоначально.

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

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

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

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

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

Например, отсортировать список по полю Наименование и для каждой группы товаров по полям Количество и Сумма подсчитать итоги с помощью функции Сумма, а по полю Цена – с помощью функции Среднее. Пример создания промежуточных итогов приведен на рисунке 2.3.







Рис.2.3. Создание промежуточных итогов.

2.12.5. Сводные таблицы

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

Создание Сводных таблиц. Создать сводную таблицу можно: с помощью меню Вставка - Таблицы - Сводная таблица.

При создании сводных таблиц отображается панель инструментов Работа со сводными таблицами (Рис.2.4.)






Рис.2.4. Панель инструментов Сводная таблицы
На панели инструментов Сводная таблица слева направо отображаются следующие кнопки действий: Сводная таблица, Активное поле, Группировать, Сортировка, Данные, Действия, Сервис, Показать или скрыть. А в меню Конструктор Сводных таблиц: Макет, Параметры стилей сводной таблицы, Стили сводной таблицы.

Порядок создания Сводной таблицы:

• 
  установите курсор в любую ячейку Базы данных. Вызовите Мастер Сводных таблиц;
  
• 
  на первом шаге для создания сводной таблицы, включите кнопку «В списке или Базе данных MS Excel». Нажмите кнопку Далее;
  
• 
  на втором шаге Мастера Сводных таблиц, задайте диапазон исходных данных. Если первоначально курсор был установлен в ячейку Базы данных, то исходный диапазон выбирается автоматически. Нажмите кнопку Далее;
  
• 
  на третьем шаге укажите, где будете располагать Сводную таблицу – на новом листе или на текущем листе, начиная с определенной ячейки. Нажмите кнопку Готово.
  
• 
  на четвертом шаге перетащите мышью заголовки полей в требуемое место на макете Сводной таблицы.
  

Редактирование Сводных таблиц. К основным операциям редактирования сводной таблицы можно отнести: изменение расположения полей в сводной таблице, добавление и удаление полей.

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

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

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

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