Закрепление строк и столбцов

При просмотре больших таблиц, содержащих много строк и столбцов, имена столбцов и наименования строк «уходят» с экрана. Чтобы шапка[1] таблицы была постоянно видна на экране, нужно закрепить шапку таким образом:

s выделить первую строку данных (следующую после шапки);

s выполнить команду Окно – Закрепить области.

G Примечания.

a) Аналогично можно закрепить и столбцы, расположенные левее выделенного столбца.

b) Можно закрепить строки, расположенные выше, и столбцы, расположенные левее выделенной ячейки.

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

Итоги

Часто бывает необходимо подвести итоги по группе одинаковых значений определённого столбца таблицы. Excel обладает очень полезным средством автоматического подведения основных и промежуточных итогов – команда Данные/Итоги.

Порядок действий для подсчётов итогов:

1) отсортировать таблицу по столбцу, в котором нужно получить итоги;

2) поставить табличный курсор на любую ячейку таблицы (таблица выделяется);

3) выполнить команду Данные - Итоги;

4) задать параметры в диалоговом окне команды:

· в поле «При каждом изменении в» - выбрать имя поля, по которому нужен итоговый результат;

· в поле «Операция» - выбрать нужную функцию из списка для подведения итогов;

· в поле «Добавить итоги по» - указать флажком все поля, по которым нужны итоговые данные по выбранной функции;

· отметить необходимые флажки в диалоговом окне;

- «Заменить текущие итоги» - если возникла необходимость исправить предыдущие итоги;

убрать этот флажок, если нужно добавить итоги с другой функцией к существующим итогам;

- «Конец страницы между группами» следует указать, если таблица занимает несколько страниц;

- «Итоги под данными» - итоговые данные располагаются в том же столбце, что и данные,

· нажать кнопку OK.

Кроме итоговых строк формируется структура, которая согласована с группировкой данных для вычисления промежуточных и общих итогов. Пользуясь кнопками уровней структуры (+, -, 1, 2, и т.д.) можно получить таблицы данных разного уровня.

Пример 5.1. Сколько студентов на каждом факультете? Каков средний балл студентов всего факультета?

Решение.

1) К таблице примера 2.1. добавить столбец со средним баллом по результатам сессии каждого студента.

2) Отсортировать данные по факультетам.

3) Выполнить команду Данные/Итоги (курсор в таблице!).

4) В диалоговом окне задать параметры как на рис.8., ОК

 

Рис.8. Диалог команды Данные/ Итоги

 

Рис. 9. Результаты решения примера 5.1., п. 4)

5) Добавить итоги - подсчитать средний балл по факультету - в диалоговом окне задать операцию -Среднее, Добавить итоги по - средний балл (флажок).

Рис. 10. Результаты решения примера 5.1., п. 5)

G Примечания к решению примера 5.1., п.5):

1) Дополнительная операция по подсчёту итогов ведёт к появлению дополнительного структурного уровня:

1-й уровень – общие итоги по двум операциям;

2-й уровень – промежуточные итоги по одной операции;

3-й уровень – промежуточные итоги по двум операциям;

4-й уровень – полное раскрытие списка.

2) Восстановить таблицу – кнопка «Убрать все» в диалоге команды Данные/Итоги

Задача 5.1. Подвести итоги по группам (данные примера 5.1). Сколько студентов в каждой группе, каков средний балл по группе?

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

В Excel существует ещё одно очень мощное средство получения обоб­щенной информации из таблицы — сводные таблицы. Для построения сводной таблицы необходимо использовать однородные табличные данные.

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

Рис. 11. Данные к примерам главы 6.

Пример 6.1. Вывести суммарную оплату по факультетам.

Решение.

1) Диалог с Мастером сводных таблиц.

· Выделить одну из ячеек таблицы.

· Выполнить команду: "Данные/ Сводные таб­лицы ".

Запускается Мастер сводных таблиц.

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

В Excel 2000 предлагается также выбрать вид создаваемого отчета - "сводная таблица".

Нажать кнопку "Далее>".

· На втором шаге предлагается указать диапазон, содержащий исходные данные. Но Excel сам "догадался", что это наша таблица (мы предвари­тельно выделили в ней ячейку). Нажать кнопку "Далее>".

· Третий шаг - самый ответственный – определение структуры таблицы.

- Внимательно рассмотреть диалоговое окно: (рис. 12).

G В Excel 2000 нужно сначала щелкнуть кнопку "Ма­кет".

 

Рис.12. Диалоговое окно макета сводной таблицы.

Структуру сводной таблицы составляют четыре области: страницы, строка, столбец и данные. В этих областях нужно разместить названия столбцов исходно­й таблицы. Эти столбцы перечислены справа от шаблона структуры.

- Перета­щить мышью кнопку Факультет в область строк, а кнопку Оплата за учёбу в об­ласть данных (в этой области обязательно что-то должно при­сутствовать).

G Примечание. Вместо Оплата за учёбу в поле теперь написано "Сумма по по­лю Оплата за учёбу ". Мастер сам вы­брал итоговую функцию "Сум­ма", потому что Оплата за учёбу явля­ется числовым полем.

· Четвертый шаг Мастера.. Предлагается поместить свод­ную таблицу на новый лист или на существующий лист. Указать расположение таблицы: на существующем листе - для наглядности и проверки результата

G В Excel 2000 возвращаемся к диалого­вому окну третьего шага.

Нажать кнопку Готово.

Результат – сводная таблица:

 

 

Отформатированная таблица:

 

 

· Изменение вида таблицы.

"Схватить мышью" поле Факультет и перетащить его в ячейку расположения столбцов.

G Обратить внимание на вид курсора мыши при перемещении по сводной таблице и за ее пределами.

В результате получится горизонтальная таблица:

 

 

· Обновление сводной таблицы

Изменить одну из сумм в исходной таблице. Сводная таблица не изменится. Чтобы изме­нения в исходных данных отразились на сводной таблице, нужно выделить одну из ячеек таблицы и выбрать в меню команду Данные/Обновить данные.

· Изменение итоговой функции

Мастер сводных таблиц вы­брал по умолчанию суммирование оплат по факультетам. Но мож­но выбрать и другие итоговые функции таким образом:

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

- щёлкнуть кнопку "Поле сводной таблицы"

G в Excel 2000 — "Параметры поля"

- рассмотреть диалоговое окно " Вычисление поля сводной таблицы":

в окне "Опе­рация" перечислены итоговые функции: "Сумма", "Количество значений", "Среднее",...

- Выбрать "Максимум".

Получится таб­лица (заголовок отредактирован):

 

 

Задача 6.1. Вывести средние оплаты за учёбу по всем группам. С помощью кнопки списка (€) вывести только указанные группы.

Задача 6.2. Определить успеваемость по группам (средний балл по группе).

 

Пример 6.2. Сколько студентов заочной и очной формы обучения по группам и факультетам?

Решение.

- Вызвать Мастер сводных таблиц (см. пример 6.1.);

- на третьем шаге сформировать макет:

В область страниц помес­тить кнопку Факультет, в область строк перетащить кнопку Группа, в область столбцов — Форма обучения, в области данных мож­но разместить любое текстовое поле, например Фамилия. Появится строка "Количество по полю Фамилия".

G Примечание. Если поместить числовое поле в область данных, то получится суммирование по этому полю (умолчание!) и придётся менять "сумму" на "количество".

- На четвертом шаге указать размещение сводной таблицы. Полученная таблица:

 

G Пояснения.

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

2) С помощью выпадающего списка в ячейке «Группа» и «Форма обучения» можно вывести информацию по определённой группе и форме обучения.

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

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

Базы данных

Управлять большими массивами данных позволяют специальные программы, предназначенные для работы с базами данных.

База данных - логически взаимосвязанные данные конкретной предметной области.

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

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

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

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

База данных (БД) в Excel (список) - прямоугольная таблица, состоящая из строк и столбцов с однотипными данными.

 

Строки - записи БД,

Столбцы - поля

записей БД.

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

Весь инструмент работы с БД в Excel сосредоточен в пункте меню Данные.

G При вызове команды из меню Данные курсор должен находиться в одной из ячеек БД

Создание базы данных

При создании базы данных в Excel необходимо соблюдать правила:

1) На одном рабочем листе не рекомендуется помещать более одной базы данных. Если это не так, отделять данные хотя бы одной строкой или одним столбцом.

2) Необходимо присвоить столбцам имена - имена полей записи, которые могут состоять из нескольких строк заголовков, размещенных в одной строке таблицы.

3) Имена столбцов должны располагаться в первой строке базы данных (заглавная строка).

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

5) Не рекомендуется:

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

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

- объединять ячейки в заглавной строке.

6) В списках можно использовать формулы.

Порядок созданиябазы данных:

§ сформировать заглавную строку по приведенным выше правилам;

§ ввести данные в соответствующие поля записи одним из двух способов: обычным или с помощью формы.

§ отформатировать таблицу (стили, границы, заливка).

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

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

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

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

Пример 7.1. Создать базу данных движения товара в магазине «Ашамлыклар».

 

 

Рис. 13. Пример создания базы данных «Магазин»

Порядок созданиябазы данных:

1) оформить заголовок таблицы, учесть при этом:

- несколько строк в имени поля, размещенных в одной строке таблицы Excel (правило 2);

- перенос слов в строке;

- вертикальное выравнивание по центру в ячейках;

2) задать обрамление всей таблицы;

3) ввести нумерацию строк при помощи автозаполнения;

4) вставить формулы для количества остатка («Кол-во прихода» - «Кол-во расхода») и суммы остатка («Кол-во остатка» * «Цену расхода») в первую строку таблицы;

5) далее заполнить таблицу двумя способами:

· обычным - две строки, с копированием формул и заданием форматов;

· с помощью формы базы данных:

- выделить таблицу без первой строки шапки, но захватив последнюю, незаполненную строку данных таблицы;

- выполнить команду Данные - Форма;

- заполнить все поля записи БД, нажать Enter, затем следующую и т.д.

- завершить работу – кнопка Закрыть.

 

 

Рис. 14. Диалоговое окно формы (пример 7.1)

 

G Примечание к рис.14:

Командные кнопки диалогового окна Форма:

Добавить – добавить запись;

Удалить – удалить выбранную запись;

Вернуть – отменить любое изменение, сделанное в выбранной записи;

Назад – возврат к предыдущей записи;

Далее – переход к следующей записи;

Критерии – поиск записи в соответствии с заданными критериями.