LINEBURG


<< Пред. стр.

страница 47
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>


О Получение информации об ячейке: =ЯЧЕЙКА(параметр;ссылка). Типы па­
раметров встроенной функции приведены в табл. 5.13.
Таблица 5.13. Типы параметров функции ЯЧЕЙКА

Параметр Возвращаемое значение

Формат Текстовое значение, соответствующее числовому формату ячейки
Адрес Адрес (ссылка) аргумента в виде текстовой строки
Продолжение &
394 Глава 5. Электронная таблица Microsoft Excel 2000

Таблица 5.13. Продолжение

Параметр Возвращаемое значение
0 — ячейка разблокирована, 1 — ячейка заблокирована
Защита
Имя файла, включая полный путь — логическое имя диска, имена
Имяфайла
папок, имя файла в квадратных скобках, имя листа
Текстовое значение, соответствующее префиксу метки ячейки:
Префикс
' — текст, выровненный влево, " — текст, выровненный вправо, л —
текст, выровненный по центру, \ — текст с заполнением, пустой
текст ("") — любое содержимое ячейки
1, если положительные или все числа отображаются в круглых
Скобки
скобках; во всех остальных случаях — 0 (ноль)
Содержимое левой верхней ячейки в аргументе ссылка
Содержимое
Номер столбца ячейки в аргументе ссылка
Столбец
Номер строки ячейки в аргументе ссылка
Строка
Текстовое значение, соответствующее типу данных в ячейке.
Тип
Пустой ячейке соответствует «Ь», текстовой константе в ячейке
соответствует «1» и «v» — любому другому значению в ячейке
1, если ячейка изменяет цвет при выводе отрицательных значений;
Цвет
во всех остальных случаях — 0 (ноль)
Ширина столбца ячейки, округленная до целого. Единица измерения
Ширина
равна ширине одного символа для шрифта стандартного размера

ПРИМЕЧАНИЕ
Другие категории встроенных функций Microsoft Excel (статистические, работы с базой
данных, логические, информационные и др.) рассматриваются далее в примерах.

Списки и базы данных Microsoft Excel
Microsoft Excel позволяет работать с реляционными структурами, которые но­
сят название списка или базы данных Excel. Список — таблица прямоугольной
конфигурации, столбцы таблицы считаются полями, строки — записями базы
данных реляционного типа. Список должен отвечать ряду требований:
• список целиком помещается на одном рабочем листе, максимальный размер
списка — 65 535 строк и 256 столбцов;
• список должен отделяться от других данных рабочего листа хотя бы одним
незаполненным столбцом и/или строкой;
• имена столбцов списка располагаются в первой строке таблицы, имена могут
быть многострочными с переносами слов1;
• ячейки одного столбца списка содержат однородную информацию.

1
Для искусственного переноса слов в заголовках полей нажимаются клавиши Alt+Enter.
395
Базовые информационные технологии Microsoft Excel


Традиционные операции со списками:
1. Ввод и редактирование данных списка.
2. Сортировка — упорядочивание записей (строк/столбцов таблицы) списка.
3. Фильтрация (отбор) записей списка по условиям.
4. Агрегирование информации списка, вычисление статистических оценок (свод­
ные таблицы, промежуточные итоги).
5. Структурирование данных (формирование новых группировок строк и столб­
цов).
6. Консолидация (объединение данных нескольких списков с формированием
общих итогов).

Форма для работы со списками
Список Microsoft Excel (база данных) может создаваться и редактироваться с
помощью экранной формы, которая содержит имена и значения полей записи
списка. Диалоговое окно формы имеет имя, соответствующее названию листа
списка, с помощью текстовых кнопок форма обеспечивает работу с каждой от­
дельной записью списка (рис. 5.31):
• Добавить — ввод новой записи в список.
Q Назад, Далее — последовательный переход к записям списка.
• Критерии — поиск записей, удовлетворяющих условиям отбора.
• Удалить — удаление записи.
• Закрыть — выход из формы.




Рис. 5 . 3 1 . Форма ввода данных в список


Для вызова формы курсор устанавливается в область списка, выполняется ко­
манда меню Данные • Форма. Список может содержать вычисляемые поля, при­
мер — поле Тариф (см. рис. 5.31).
396 Глава 5. Электронная таблица Microsoft Excel 2000


ПРИМЕЧАНИЕ
Вычисляемые поля списка Microsoft Excel, содержащие формулы, недоступны для редак­
тирования через форму.

Весь список, включая имена полей и записи, может рассматриваться как единый
блок ячеек, для которого создается общее имя. Если блок списка имеет стан­
дартное имя БазаДанных, при вводе новых записей через форму ввода блок автома­
тически расширяется, в вычисляемые поля новых записей копируются формулы.
Основным недостатком применения экранных форм для работы со списками
Microsoft Excel является низкий уровень достоверности вводимых данных в свя­
зи с отсутствием программных методов контроля, невозможностью использова­
ния элемента управления Поле со списком (см. п. «Контроль ввода данных»).

Пример 16
Заполнить список Картотека с помощью экранной формы. Разряд работающего
определяет его тарифную ставку.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Выбрать лист Тарифы (см. пример 2)
3. Снять защиту с ячейки Е2 (ставка 1 разряда) с помощью команды меню
Формат • Ячейки, на вкладке Защита сбросить Защищаемая ячейка.
4. Выделить блок ячеек Ставка.
5. Скрыть формулы в ячейках блока Ставка с помощью команды меню Формат •
Ячейки, на вкладке Защита установить Защищаемая ячейка и Скрыть формулы.
6. Защитить лист Тарифы от изменений с помощью команды меню Сервис • За­
щита • Защитить лист, указать защиту содержимого листа.
7. Выбрать лист Картотека (см. пример 2)
8. Установить курсор в ячейку первой строки столбца Разряд работающего и
ввести формулу тарифа в ячейку Е2: =nP0CM0TP(D2 ; Разряд;Ставка).
9. Скопировать формулу ячейки Е2 в ячейки столбца Тариф (для заполненных
строк списка).
10. Установить курсор в область списка, команда Данные • Форма.
11. Ввести новые записи в список с помощью кнопки Добавить (табл. 5.14).

Т а б л и ц а 5 . 1 4 . Списки работающих

Табельный № Профессия Разряд работающего Тариф Льготы
ФИО

01235
Котов А А Весовщик 5 2
01236
Павлов Ф. Ф. Весовщик 4 2
Соловьев А П. 01237 Упаковщик 3 1
01238 Грузчик
Крылов А. Н 5 1
Упаковщик
Ильин А. Е. 01239 5 1
397
Базовые информационные технологии Microsoft Excel


ВНИМАНИЕ
В поле столбца Тариф новых записей списка формулы переносятся автоматически

12 Выполнить отбор записей по условию профессия Грузчик, разряд работаю­
щего выше 3 Нажать кнопку Критерии и заполнить поля формы
О поле Профессия — Грузчик,
О поле Разряд работающего — 3
13 Нажать кнопку Далее и просмотреть записи, удовлетворяющие запросу
14 Нажать кнопку Очистить
15 Выполнить отбор записей по условию разряд работающего до 4 и льготы
больше 2 Нажать кнопку Критерии и заполнить поля формы
О поле Разряд работающего — <4,
О поле Льготы — 2
16 Нажать кнопку Далее и просмотреть записи, удовлетворяющие запросу
17 Закрыть файл с сохранением с помощью команды меню Файл • Закрыть

Сортировка списков
Технология сортировки для всех таблиц прямоугольной конфигурации, вклю­
чая списки Microsoft Excel, единая Особенностью списков является то, что они
сортируются построчно, другие же таблицы могут сортироваться как по стро­
кам, так и по столбцам Для сортировки записей таблицы выделяется сортируе­
мая область, поэтому допускается сортировка части таблицы, при этом правиль­
ность операции сортировки должна контролироваться пользователем

ВНИМАНИЕ
Если список Microsoft Excel отделяется от прочей информации хотя бы одной пустой
строкой и та пустым столбцом, достаточно установить курсор в любую ячейку списка пе­
ред выполнением команды сортировки


Результат сортировки списка всегда остается на том же месте Для сортировки
можно задать от 1 до 3 ключей с указанием направления сортировки — по воз­
растанию или убыванию для каждого ключа в отдельности При сортировке по
возрастанию упорядочение идет от меньшею к большему, по алфавиту или в
хронологическом порядке дат Сортировка по убыванию использует обратный
порядок, исключение — пустые ячейки, которые располагаются в конце списка
Если данные в ячейках имеют смешанный формат, установлены приоритеты
• числа,
• текст,
• логические значения,
• значения ошибок,
• пустые ячейки
398 Глава 5. Электронная таблица Microsoft Excel 2000


При выполнении команды меню Данные • Сортировка появляется диалоговое окно
для ввода параметров сортировки (рис. 5.32).




Рис. 5.32. Сортировка списка

Блок ячеек списка включает имена полей, поэтому нужно указать Идентифици­
ровать поля по подписям. Выбираются ключи сортировки и направление сорти­
ровки — по возрастанию или убыванию значений этих ключей.
Текстовая кнопка Параметры обеспечивает дополнительную настройку для вы­
полнения операции сортировки списка:
• выбор способа сортировки по первому ключу;
• различие прописных и строчных букв;
• тип сортировки таблицы — по строкам или по столбцам.

Фильтрация списков
Различают два способа фильтрации (отбора) записей списков в команде меню
Данные • Фильтр: Автофильтр и Расширенный фильтр.

Автофильтр

Автофильтр создает для каждого поля список значений, используемый для отбо­
ра записей:
• Все — нет ограничений на значения поля.
• Первые 10 — вывод установленного числа или процент записей с наибольши­
ми/наименьшими значениями поля.
• Условие — формирование условий отбора в Пользовательском автофильтре.
• Конкретное значение — отбор записей с указанным значением поля.
• Пустые — в поле пусто (нет значения).
• Непустые — в поле содержится какое-либо 31 ачение.
399
Базовые информационные технологии Microsoft Excel


Условие для первого поля фильтрует записи списка Microsoft Excel. Условие
для второго поля фильтрует результат предыдущей фильтрации и т. д. Все ус­
ловия отбора связаны логической связкой И.
Если условие задается только по одному полю с помощью пользовательского
автофильтра (рис. 5.33), условия могут быть связаны как связкой И, так и связ­
кой ИЛИ. Для задания условий отбора могут использоваться шаблоны — заме­
нители отдельных символов — ? или всех символов — *.

Пользовательский *


/i>'
^«**%f#M8>?« &$ж№*фтщ$пфф
c
статей ^ i n>
<*
v
| больше или равно

/-vf;t:cMmM
*i'„ -J 7 ' 78
'Ч] меньше или равно


7/>
С^ивор^Г обозначает любой е;тничнвЙсймеая
итеяьиость любы

rytpt ±
JJ6№*
Рис. 5.33. Пользовательский автофильтр


На экране в списке в результате фильтрации остаются видимыми лишь записи,
удовлетворяющие заданным условиям отбора. Отобранные записи можно копи­
ровать, удалять или редактировать. Для сброса всех условий фильтрации слу­
жит команда меню Данные • Фильтр • Показать все. Для отказа от автофильтра
повторно выполняется команда меню Данные • Фильтр • Автофильтр.

Расширенный фильтр
Расширенный фильтр предполагает формирование специального диапазона яче­
ек в произвольном месте. Этот диапазон является критерием для расширенного
фильтра списка. Диапазон условий состоит из имен полей списка и строк усло­
вий.
В области диапазона условий для расширенного фильтра списка Microsoft Excel
можно сформулировать несколько строк условий, связанных между собой логи­
ческой связкой ИЛИ. При этом условия фильтрации внутри одной строки усло­
вия связаны логической связкой И.
Команда меню Данные • Фильтр • Расширенный фильтр выводит диалоговое окно
для указания диапазона ячеек: исходного диапазона — списка Microsoft Excel,
диапазона условий, диапазона ячеек для вывода результата фильтрации (рис. 5.34).
Результат расширенного фильтра можно скопировать в новое место, он может
включать все отобранные или только уникальные записи. Исходный диапазон —
список Microsoft Excel, диапазон условий и копия отфильтрованных записей
могут находиться на одном и том же листе, на разных листах одной книги и
даже в различных открытых книгах.
400 Глава 5 Электронная таблица Microsoft Excel 2000


ВЕЗ
[ Расширенный ФИЛЬТР

Обработка

& фзхщхтть результат ш. дате мест»



11
Двапакжда*»* *]$Е$6 $Е*7

№}«естит& результат йдиагчадн, |&д$13

R бодяга уциемкчые :цк#5и


L»?L-J-
Р и с . 5 . 3 4 . Расширенный фильтр


Существуют различные способы фильтрации записей списка Microsoft Excel:
фильтровать список на месте и копировать результат фильтрации в новое место.
Последовательность действий для фильтрации списка Microsoft Excel на месте.
1. Установить курсор в область списка.
2. Выполнить команду меню Данные • Фильтр • Расширенный фильтр.
3. В диалоговом окне Расширенный фильтр указать:
О Исходный диапазон — область ячеек списка (заполняется автоматически),
включает заголовки столбцов и все строки.
О Диапазон условий — область условий для расширенного фильтра, включа­
ет заголовки столбцов и строки условий.
4. Нажать кнопку ОК.
Последовательность действий при копировании результата фильтрации списка
Microsoft Excel в другое место.
1. Установить курсор на лист, куда выводится результат фильтрации.
2. Выполнить команду меню Данные • Фильтр • Расширенный фильтр.
3. В диалоговом окне Расширенный фильтр:
О Указать Скопировать результат в другое место.
О Исходный диапазон — область ячеек списка (заполняется автоматически),
включает заголовки столбцов и все строки.
О Диапазон условий — область условий для расширенного фильтра, включа­
ет заголовки столбцов и строки условий.
О Поместить результат в диапазон — указать начальную ячейку для резуль­
тата фильтрации (на текущем листе рабочей книги).
О Нажать кнопку ОК.

Пример 17
Отобрать сведения о сотрудниках, для которых число льгот по подоходному на­
логу находится в интервале от 2 до 3 и разряд не ниже 3, а также всех сотрудни­
ков по профессии «Грузчик». Условия отбора поместить на новом листе. Ре­
зультат фильтрации записей списка поместить на новом листе.
401
Базовые информационные технологии Microsoft Excel


1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Выбрать лист Картотека.
3. Создать именованный блок БазаДанных для списка Картотека — команда меню
Вставка • Имя • Присвоить. В блок входят заголовки столбцов и все запол­
ненные записи списка Microsoft Excel.
4. Вставить новый лист с помощью команды меню Вставка • Лист.
5. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Критерии.
6. Подготовить диапазон условий для расширенного фильтра на листе Крите­
рии (рис. 5.35).

Разряд
Льготы Профессия работающего
Льготы
>=2 >=3
<=3
Грузчик
Рис. 5.35. Область критериев

ВНИМАНИЕ
Имена полей списка в область критериев целесообразно перенести путем копирования,
так как при фильтрации требуется полное совпадение имен полей в списке Microsoft
Excel и диапазоне условий.

7. Вставить новый лист с помощью команды меню Вставка • Лист.
8. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Результат.
9. Установить курсор в ячейку А1 листа Результат.
10. Выполнить команду меню Данные • Фильтр • Расширенный фильтр.
11. В диалоговом окне Расширенный фильтр указать:
О Скопировать результат в другое место.
О Исходный диапазон — блок БазаДанных.
О Диапазон условий — Критерии!$А$1:$0$3.
О Поместить результат в диапазон — Результат!$А$1.
О Кнопка ОК.
12. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
Более сложные условия фильтрации записываются с помощью вычисляемых кри­
териев, которые содержат формулы в условиях. Область вычисляемых критери­
ев заполняется следующим образом:
• заголовок столбца вычисляемого критерия не должен совпадать с именами
полей списка;
• в строке условия вводится формула, которая вычисляет логическую констан­
ту ИСТИНА, ЛОЖЬ (по отношению к первой записи списка);
402 Глава 5. Электронная таблица Microsoft Excel 2000


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

Пример 18

<< Пред. стр.

страница 47
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign