консолидация внешних данных

Примечание: Если стоит флажок, то нельзя корректировать ссылки на область источника.

15.Фильтрация (выборка) данных.

Фильтр данных в списке – выбор данных по заданному критерию или условию. Осуществляется эта операция с помощью команды: Данные – Фильтр (в 2003).

Существует две разновидности данной команды:

1. автофильтр (2003)

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

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

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

Чтобы установить фильтрацию, нужно предварительно выделить шапку таблицы.

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

• критерии сравнения

• вычисляемый критерий.

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

1. формирование области критерия поиска

2. фильтрация записей списка

1 этап. Область критерия поиска содержит строки имен столбцов и произведенное число строк для задания поиска.

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

Ненужные имена столбцов из диапазона условно можно удалить.

Критерий сравнения формируется для соблюдения следующих требований:

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

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

3. ниже имен столбцов располагается критерий сравнения типа точного значения, значения, сформированного с помощью операции отношения, шаблона назначение, включая символы *, и(или).

2 этап. Фильтровать записи списка можно на месте, либо копируя в указанную область на текущем листе:

• скопировать результат в другое место, выполнив конкретную команду фильтрации

• только уникальные записи

Фильтрация с помощью формы данных

Данные – Форма

Excel позволяет работать с отдельными записями списка с помощью эффекта списка:

• последовательный просмотр записей

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

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

• добавлять новые записи, строки, таблицы.

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

• Просматривать запись

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

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

Автофильтр (AutoFilter) незаменим при обработке большого массива данных.

Отфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню Данные - Фильтр – Автофильтр.

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

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

18. Фильтрация с помощью формы данных

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

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

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

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

В 2003 данные - сводная таблица

В 2007 вставка - таблицы - сводная таблица.

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

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

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

Значения (данные в 2003) поля, по которым подводятся итоги согласно выбранной функции.

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

Каждое поле размещается только один раз в области страница-строка-столбец.

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

20.Средства условного анализа (Подбор параметра).

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

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

2. Ячейка, которая будет изменяться при подборе, должна наоборот содержать значения, а не формулу.

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

21.Средства условного анализа (Поиск решения).

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

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

2003: сервис - поиск решения

Для выполнения операции поиска решения в диалоговом окне необходимо ввести следующие параметры:

1. Адрес целевой ячейки (формула) с подбираемым значением

2. Критерий оптимизации - подбираемое значение

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

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

5. В excel можно сохранить и в последствии повторно использовать различные модели поиска, решения и полученные результаты. Текущая модель сохраняется вместе с рабочим листом. Чтобы сохранить, а затем повторно применить модель поиска решения, используют кнопки "сохранить модель" и "загрузить модель".

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

7. Отчет создается на отдельном рабочем листе и содержит: сведения об адресах, исходных и конечных целевой и влияющих ячеек и наложенных ограничениях.

22.Средства условного анализа (Сценарий).

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

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

Изменяемые ячейки - имена или адреса ячеек, в которых находятся исходные данные для сценария. Эти ячейки не обязательно должны быть смежными. Если ячейки не смежные, то их адреса будут прописываться через ;. Для одного сценария допускается создавать не более 32 изменяемых ячеек.

Запретить изменения - по умолчанию. Оставить.

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

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

23.Таблица подстановки с 1 переменной.

Таблица подстановки

Таблица подстановки с одной и двумя переменными

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

1. Таблицы подстановки с одной переменной (с одной или несколькими формулами);

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

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

Таблицы подстановки с одной переменной

Предусмотрена следующая последовательность действий:

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

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

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

4. Выбирается команда "таблица подстановки".

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

24. Таблица подстановки с 2 переменными.

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

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

1. Ввести формулу, которая ссылается на две ячейки ввода

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

3. В строку правее формулы ввести значение подстановки для второй переменной.

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

5. В 2003 данные - таблица подстановки

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

25.Поиск зависимых и влияющих ячеек.

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

26.Проверка данных при вводе.

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

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

2. открывается окно проверки вводимых значений. Данные – проверка.

Проверка данных при вводе

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

Критерий правильности ввода для определенного диапазона ячеек задается следующим образом:

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

2. Выполнить:

Вкладка Данные - Группа Инструментов Работа с данными — раскрывающийся список Проверка данных

– кнопка Проверка данных - соответствующее диалоговое окно - вкладка Параметры

27.Поиск ошибок на листе.

1. Деление на ноль - в формуле задано деление на ноль или назначение пустой ячейки

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

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

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

5. Ссылка - в формуле задана неверная ссылка на ячейку. Например, ссылка на ячейку, которая была удалена с рабочего листа.

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

7. Пусто - задано пересечение двух областей, которое в действительности не имеет общих точек

8. # - ширина ячейки недостаточна для отображения результатов вычислений формулы, либо просто какой-то величины.

28. Защита информации в эт Excel.

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

29. Макрокоманды в Excel.

С помощью макрокоманды ПреобразоватьЭлектроннуюТаблицу можно импортировать или экспортировать данные между текущей базой данных Access (MDB или ACCDB) или проектом Access (ADP) и файлом электронной таблицы. Можно также связать данные электронной таблицы Microsoft Office Excel 2007 с текущей базой данных Microsoft Office Access 2007. С помощью связанной электронной таблицы можно просматривать и редактировать данные этой таблицы из Access, одновременно сохраняя полный доступ к этим данным из программы электронных таблиц Excel.

Макрос — это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования — Visual Basic for Application(VBA).

30. Создание и редактирование диаграмм в документе Excel.

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

• С областями

• Линейчатая

• Гистограмма

• График

• Круговая

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