Файл: Л.С. Таганов Решение численных задач средствами MS Excel.pdf

43

Примерные варианты списков

1.Страны: название, столица, площадь, население, год образования , возраст.

2.Столицы: название, страна, население, год образования, возраст.

3.Предприятия города: название, тип собственности, дата рождения, возраст, экономическая эффективность.

4.Список трудового коллектива: фамилия, имя, отчество, профессия, дата рождения, возраст.

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

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

7.Ваши преподаватели: фамилия, имя, отчество, дата рождения, возраст, учёная степень, учёное звание.

8.Учебные дисциплины: название, семестр, количество часов в семестре, количество часов в неделю, дата сдачи экзамена.

9.Список учебной группы: фамилия, имя, отчество, пол, номер зачётной книжки, дата рождения, возраст, успеваемость.

10.Домашние животные (кошки): порода, цвет шерсти, цвет глаз, дата рождения, возраст, цена, телефон хозяина.

11.Домашние животные (собаки): порода, цвет шерсти, дата рождения, возраст, цена, телефон хозяина.

12.Квартиры: планировка, площадь, число комнат, площадь кухни этаж, район города, цена.

13.Продукты питания: наименование, производитель, дата выпуска, срок реализации, время до срока реализации, цена.

3.ОБРАБОТКА СПИСКА

Косновным действиям по обработке списков относятся:

добавление, удаление, редактирование, просмотр, сортировка и поиск записей.

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

44

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

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

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

Реализация сортировки возможна двумя способами:

-с помощью кнопок <Сортировка по возрастанию> и <Сортировка по убыванию> панели инструментов <Стандартная>;

-через команду меню <Данные/Сортировка>.

Применение команды меню <Данные/Сортировка> позволяет отсортировать список за один приём максимум по трём полям (первый ключ, второй, третий ключ). Если необходимо произвести сортировку более чем по трём полям, то сортировка должна производиться последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно её выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки не соответствуют ожидаемым, действие по сортировке необходимо незамедлительно отменить с помощью кнопки

<Отменить> панели инструментов <Стандартная>.

3.2. Формирование запросов

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

Запросы в Excel реализованы с помощью фильтров. Фильтрация списка – это процесс, в результате которого в списке скрываются все строки, не удовлетворяющие критериям фильтрации, а видимыми остаются только те, которые соответствуют условиям запроса (остаётся

45

Расширенный (усиленный) фильтр позволяет выполнять запросы практически любой сложности.

3.2.1. Формирование запросов с помощью автофильтра

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

-щёлкнуть мышкой по кнопке меню <Данные>;

-в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>;

-в появившемся справа подменю установить флажок на строке <Автофильтр>, щёлкнув мышкой по строке.

Для установки автофильтра на одно поле необходимо выполнить:

-активизировать заголовок нужного поля, щёлкнув мышкой по нему;

-нажать комбинацию клавиш Sift+Ctrl+↓.

После установки автофильтра в ячейках с названиями всех полей или одного поля появляется кнопка в виде треугольника вершиной вниз.

Отмена установки автофильтра осуществляется той же последовательностью команд, что и при установке, за исключением последней команды. Вместо установки флажка на строке <Автофильтр> надо снять флажок щелчком мышки по нему.

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

-(Все) – для выбора содержимого всего списка или возврата всего списка после фильтрации;

-(Первые 10 …) – для формирования запросов только по числовым полям. После щелчка мышкой по этой строке появляется диалоговое окно <Наложение условия по списку>. В данном окне устанавливаются условия выборки: количество записей, наибольших или наименьших, элементов списка или % от количества элементов;

-(Условие …) - для установки критериев выборки. После щелчка мышкой по этой строке появляется диалоговое окно

<Пользовательский автофильтр>, который позволяет создать критерий выборки. Критерий может состоять не более чем из двух условий, соединённых операциями <И>, <ИЛИ>. Каждое из условий

46

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

-“*” – для обозначения последовательности произвольной длины, состоящей из любых символов;

-“?” – для обозначения символа, стоящего на определённом

месте.

При включении символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду “~”.

3.2.2. Формирование запросов с помощью расширенного фильтра

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

-позволяет создавать критерии с условиями по нескольким полям;

-позволяет создавать критерии с тремя и более условиями;

-позволяет создавать вычисляемые критерии;

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

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

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

-щёлкнуть мышкой по кнопке меню <Данные>;

-в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>;

-в появившемся справа подменю установить флажок на

строке <Расширенный фильтр>, щёлкнув мышкой по строке. При работе с расширенным фильтром необходимо определить три

области:

-исходный диапазон – вся область базы данных, например, $A$1:$H$26;

-диапазон условий – область, содержащая критерии фильтрации,

47

например, Критерии! $A$28:$C$30;

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

Назначение флажка <Только уникальные записи> в окне диалога <Расширенный фильтр> очевидно. Установка этого флажка при копировании выборки позволяет убрать из неё все повторяющиеся записи. При отсутствии диапазона условий с помощью этого флажка можно избавиться от повторяющихся записей в исходном списке.

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

- диапазон условий должен состоять не менее чем из двух строк: первая строка – строка заголовков, последующие строки – соответствующие критерии;

-интервал критериев должен располагаться вне списка или на другом листе;

-в интервале критериев не должно быть пустых строк;

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

между ними поставлена логическая операция <И>;

-если условия располагаются в разных строках, то требуется выполнение хотя бы одного условия, то есть считается, что они соединены логической операцией <ИЛИ>.

При формировании текстовых критериев необходимо помнить о следующих правилах:

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

-если содержимое ячейки представляет собой текстовую константу вида “>БУКВА” или “<БУКВА”, то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ, или начинающийся с предшествующих ей БУКВ;

-для поиска текста на полное совпадение содержимое ячейки с критерием должно иметь вид =”=ТЕКСТ”;

-в текстовых критериях можно использовать символы шаблона.

48

Вычисляемый критерий представляет собой формулу, в которой обязательно имеется ссылка на соответствующую ячейку первой строки списка. Так как эта формула является логическим выражением, то в ячейке, её содержащей, отображается результат вычисления (ИСТИНА или ЛОЖЬ) для первой записи списка. А после фильтрации в списке будут скрыты те записи, для которых при вычислении формулы получается значение ЛОЖЬ.

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

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

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

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

формировании вычисляемых критериев.

а) Текстовые функции:

-ДЛСТР(текст) – возвращает длину строки, то есть количество символов в параметре текст, включая пробелы между словами;

-ТЕКСТ(значение; формат) – преобразует число в текст

-ПРАВСИМВ(текст; колич_симв) – извлекает заданное по заданному формату;

-ЗНАЧЕН(текст) – преобразует число, представленное в текстовом формате, в числовой формат; количество символов из конца строки текст;

-ЛЕВСИМВ(текст; колич_симв) – извлекает заданное

количество символов из начала строки текст;

-ПСТР(текст; нач_позиция; колич_симв) – извлекает из исходной строки текст, начиная с указанной позиции, подстроку заданной длины;

-СЖПРОЕЛЫ(текст) – удаляет все пробелы из начала и конца строки текст, а из внутренней части строки все кроме одиночных;

49

-НАЙТИ(исх_текст; просм_текст; нач_позиц) – ведёт поиск заданной подстроки внутри исходной строки и возвращает порядковый номер символа исходной строки, с которого начинается найденный образец. При подсчёте учитываются все символы, включая пробелы и знаки препинания, а также учитывается регистр;

-ПОИСК(исх_текст; просм_текст; нач_позиц) – ведёт поиск заданной подстроки внутри исходной строки и возвращает порядковый номер символа исходной строки, с которого начинается найденный образец. При подсчёте учитываются все символы, включая пробелы и знаки препинания, однако не учитывается регистр, допускаются символы шаблона.

б) Функции даты и времени:

-ДАТА(год; месяц; день) – возвращает дату в числовом формате (параметры функции также задаются цифрами);

-СЕГОДНЯ() – возвращает числовое значение текущей даты;

-ДЕНЬНЕД(дата; тип) – вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Параметр тип определяет начало отсчёта (воскресенье или понедельник). Если параметр задан равный 1 или отсутствует, то началом отсчёта будет воскресенье, а если равен 2, то начало отсчёта будет понедельник;

-ГОД(дата) – возвращает значение года (от 1900 до 9999) для данной даты;

-МЕСЯЦ(дата) – возвращает номер месяца (от 1 до 12) для данной даты;

-ДЕНЬ(дата) – возвращает номер дня в месяце (от 1 до 31) для данной даты;

-ДАТАЗНАЧ(дата_как_текст) – преобразует дату, заданную в текстовом формате, в числовой формат.

в) Функции для анализа списков:

-СЧЁТЕСЛИ(интервал; критерий) – возвращает количество ячеек в интервале, которые удовлетворяют критерию, например, =СЧЁТЕСЛИ(F2:F26;”Ж”);