Excel_lek

III.16. Операции над рабочими листами

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

Каждая создаваемая рабочая книга состоит из определенного числа рабочих лист(пов умолчанию 16). Их количество изменяется в зависимости от установки по командеСервис Þ Параметры Þ Общие.

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

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

·к определенному листу – щелчок мыши по его ярлычку;

·к первому листу – нажмите 1-й кнопки линейки прокрутки;

·к последнему листу – нажмите 4-й кнопки линейки прокрутки;

·к следующему листу – нажмите 3-й кнопки линейки прокрутки;

·к предыдущему листу – нажмите 2-й кнопки линейки прокрутки.

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

Для удаления рабочего листа выполнить щелчокправой кнопкой мыши по ярлычку нужного рабочего листа, задать команду Удалить и нажатием кнопки ОК подтвердить согласие на удаление.

Для вставки нового рабочего листа выполнить щелчок правой кнопкой мыши по ярлычку того листа, перед которым будет вставляться новый и задать команду Добавить и выбрать Лист во вкладке Общие и нажать ОК.

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

отформатировать определенные диапазоны ячеек. При этом эффективным оказываетсяпредварительное выделение этих рабочих листов. Для выделения:

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

·нескольких смежных листов щелкнуть левой кнопкой мыши по ярлычку первого выделяемого листа, а затем, удерживая в нажатом состоянии клавишу<Shift>, щелкнуть левой кнопкой по ярлычку последнего выделяемого листа;

·нескольких несмежных листов выполнять щелчки по ярлыкам соответствующих листов, удерживая нажатой клавишу <Ctrl>.

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

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

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

III.17. Связывание рабочих листов таблицы

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

III.17.1. Прямое связывание

Для ссылки из таблицы, расположенной на Листе 1, на данные таблицы, расположенной на Листе 2, надо в ячейке таблицы Листа 1 указать абсолютный адрес ячеек с данными Листа 2, в виде: Лист2!$A$1.

Пример:

Определить процентное отношение ячеек, содержащих числовые значения, к общему числу ячеек. Выполнить пример 4 п. III.15.4 на рабочем листе 1. перейти на рабочий Лист 2 (щелкнуть левой кнопкой по ярлыку) и в ячейке А1 ввести формулу: Лист2!$С$6/5*100, где 5 – число ячеек.

В ячейке А1 Листа 2 получим 60.

Если нужно сослаться на данные, расположенные в незагруженном файле(другой рабочей книги), то нужно задавать полный путь местонахождения файла: =D:\EXCEL\[PRIMER]ЛИСТ2’!$N$20, где Лист2’ – имя рабочего листа в одинарных кавычках, \[PRIMER] – имя рабочей книги.

III.18. Консолидация данных

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

В Excel существует следующие методы консолидации данных:

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

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

·задать команды Данные Þ Итоги;

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

·в поле Использовать функцию указать Сумма;

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

·нажать кнопку ОК.

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

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

III.20. Работа со списками (базами данных)

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

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

III.20.1. Создание списками (базами данных)

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

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

В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

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

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

2.Щелкните на любой из ячеек заглавной строки и выполнитеДанные Þ Форма.

3.В открывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. ширина поля соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Tab> - для перехода вниз и <Sift><Tab> - для перехода вверх.

4.Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.

5.Для завершения процесса ввода данных нажмите кнопку Закрыть.

III.20.2. Поиск записей в списке

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

1.Установите курсор в любую ячейку списка и выберите командуДанные Þ Форма.

2.Нажмите кнопку Критерий.

3.Введите критерий поиска в одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилий набрать А. Для поиска записей с величиной оклада, больше 200000, в поле оклада следует ввести >200000.

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

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

III.20.3. Редактирование записей с помощью формы

Для исправления значений (но не формул) в любом поле записи:

1.Установите курсор в любую ячейку списка и выберите командуДанные Þ Форма.

2.Найдите требуемую запись с помощью кнопок Назад и Далее.

3.Отредактируйте запись.

4.Нажмите кнопку Закрыть.

III.20.4. Удаление записей с помощью формы

Для удаления записи:

1.Установите курсор в любую ячейку списка и выберите командуДанные Þ Форма.

2.Найдите требуемую запись и нажмите кнопку Удалить.

3.Подтвердите удаление нажав ОК.

4.Нажмите кнопку Закрыть.

III.20.5. Добавление записей

При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка. Для добавления записи внутрь списка:

1.Установите курсор в строку, перед которой будет вставлена новая строка.

2.Выберите команду Вставка Þ Строка и введите в нее соответствующие значения.

III.20.6. Фильтрация списка

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

автофильтра следует выполнить следующую последовательность действий:

1.Установите курсор в любую ячейку списка, задать команду Данные Þ Фильтр Þ Автофильтр.

2.Нажмите на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.

3.Выбрать любой элемент из списка.

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

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

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

III.20.8. Фильтрация списка с использованием сложных критериев

Вэтом случае необходимо выполнить следующее:

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

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

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

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

2. Установить курсор в любую ячейку списка и задать команду Данные Þ Фильтр Þ Расширенный фильтр.

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

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

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

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

III.20.9. Функции для работы со списками

Библиотека Excel содержит тринадцать встроенных функций списков(баз данных), позволяющих получить информацию из списка или произвести в нем необходимые вычисления. При этом некоторые из этих функций соответствуют уже известнымExcelфункциям таблицы (например, БДСУММ(Список; Поле; Критерий поиска),

БСЧЕТ(Список; Поле; Критерий поиска), ДМАХ(Список; Поле; Критерий поиска) и др.), но являются более гибкими,

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

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

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

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

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

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

III.20.10. Таблица автоматической подстановки данных

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

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

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

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

Таблица1

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

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

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

1.Установите курсор в любую ячейку списка, задать команду Данные Þ Сводная таблица.

2.В открывшемся диалоговом окнеМастер сводных таблицотметьте опцию в списке или базе данных

Microsoft Excel.

3.Далее определите диапазон, с которым будет работатьМастер сводных таблиц. Щелкните на кнопке

Далее.

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

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

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

III.20.12. Вставка информации в список Excel из другой программы

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

Такие преобразования существенно облегчает Мастер текста Excel:

1.Используя буфер обмена скопируйте из документа Word нужный текст и вставьте его на рабочий лист Excel.

2.После вставки все данные будут размещены в один столбец. Выделите весь занятый столбец.

3.Выберите команду Данные Þ Текст по столбцам.

4.После выбора формата данных в появившемся окнеМастер текстов (например, <Запятая > и < Пробел >).

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

6.Щелкните на кнопку Готово .

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