Занятие Картотека подержанных автомобилей стр

Вид материала

Содержание

Подобный материал:

Занятие 8. Картотека подержанных автомобилей стр. из

Автозавершение ввода. Пять раз в неделю, кроме субботы и воскресенья, в Москве выходит газета частных объявлений «Из рук в руки», и каждый её выпуск содержит большое количество предложений относительно продажи подержанных автомобилей. В объявлениях приводятся помимо их марки и цены, также разнообразные технические характеристики: объём двигателя в литрах, величина пробега в километрах, год выпуска, цвет, рабочее состояние и т.д. Потенциальному покупателю нелегко держать в голове все эти сведения, и поэтому совершенно понятно его желание создать на компьютере картотеку вариантов, привлекших внимание. Конечно, для выполнения такого рода задач имеется специальный класс программ СУБД (к их числу принадлежит, в частности, Microsoft Access), однако и Excel предоставляет некоторые возможности работы с базами данных.

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

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

Хорошо ещё, что разработчики Excel предусмотрели для облегчения ввода разные приёмы, и самый простой из них - автозавершение. Если в четвёртой по счёту записи опять должен фигурировать автомобиль марки «ВАЗ», то уже при вводе первой буквы сама программа предложит вариант, встречавшийся в том же столбце выше, т.е. ВАЗ-2106. В знак согласия мы можем сразу нажать Enter, правда, если только речь действительно идёт о шестой модели, в противном же случае, увы, ввод придётся продолжить. Такая же ситуация встретится и для поля «Состояние» (по первой же букве «и» или «о») и вообще для всех полей, содержащих текст. Причём в поле «Цвет» вариант заполнения будет предложен не ранее ввода первых трёх символов, считая и тире, - по четвёртой букве «к» или «с».

По умолчанию, приём автозаполнения уже должен быть активизирован сразу после установки программы Excel. Если же по каким-либо причинам он не действует, то просто следует проверить, стоит ли галочка (флажок) против данного пункта в соответствующем диалоговом окне: Сервис  Параметры  Правка  Автозаполнение значений ячеек.

Выбор из списка. И всё же при вводе марки автомобиля приём автозавершения почти не даёт никакого выигрыша, т.к. у всех моделей, ВАЗа ли, Москвича ли, расхождение в названиях наступает лишь на последнем символе и набирать придётся полностью всё их сочетание. Здесь на помощь должен прийти другой способ, и он даже ещё более удобен - это выбор из списка ранее введённых значений. Правда, дело касается опять же только текстовых полей, и, тем не менее, стоит в очередной, пока ещё пустой клетке щёлкнуть правой клавишей и обратиться к самому нижнему пункту контекстного меню, который так и называется «Выбор из списка», как он, этот список появится. В нём будут перечислены в алфавитном порядке все ранее введённые значения этого поля, и нам останется лишь указать на необходимый элемент. Это значит, что после накопления нескольких первых записей, клавиатуру можно будет вообще отставить в сторону и вводить данные одними щелчками мыши!

Приём работает, между прочим, и по отношению к полю «Телефон», доказывая тем самым, что в нём содержится всё же текст, а не число. Хотя применительно к телефону его использование совершенно неоправданно, поскольку вряд ли для нового номера найдутся совпадения с теми, что уже встречались ранее (разве что один и тот же хозяин продаёт две или три машины). А вот для марки автомобиля это как раз то, что нужно: по мере заполнения таблицы список не разрастётся очень уж сильно и будет довольно легко сориентироваться в тех нескольких наименованиях, которые составляют круг нашего интереса.

Но это всё текстовые поля, а как поступить, например, с «Годом выпуска» и с «Объёмом двигателя», а также с «Датой»? Ведь в отличие от номера телефона и в том, и в другом, и в третьем поле, - всего лишь сравнительно небольшой набор значений и, понятное дело, быстро надоест писать помногу раз одно и то же. Ни автозаполнение, ни выбор из списка для числовых полей не подходят. Остаётся одно из двух: либо искать новый приём для облегчения ввода, либо преобразовывать числа в текст, т.е. просто в совокупность символов.

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

Или можно сделать по другому: вставить ещё один дополнительный столбец, и использовать в нём встроенную функцию «ТЕКСТ», указав ей в качестве аргумента собственно сам год. Тогда, перебрав в первом десятке-двух записей исходного столбца все разные числовые значения, мы можем вообще скрыть его из виду, а в оставшемся дополнительном столбце начать уже пользоваться выбором из списка.

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

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

Сделан этот заказ был следующим образом: сначала потребовалось где-то в стороне от основной таблицы, в ячейках, скажем, N4:N18, разместить вспомогательный список допустимых значений для года, с 1985 по 1999. Затем, после установки курсора на нужной ячейке C13, был вызван пункт «Проверка» из меню «Данные». В появившемся диалоговом окне давались все необходимые разъяснения: и относительно типа данных (Список), и по тому, где расположен источник значений для него. Дополнительно, на третьей вкладке окна можно было бы ещё указать, какое предупреждающее об ошибке сообщение следует выводить на экран, если вдруг в данную ячейку кто-то захочет ввести что-либо иное, игнорируя упомянутый треугольник-стрелку. А чтобы даже такого соблазна не было, вторая вкладка позволила также сформулировать другое сообщение, упреждающее, оно сразу же появляется на экране, как только производится по ячейке щелчок мышью (впрочем, это, пожалуй, уже излишне).

Аналогичную проверку не мешало бы организовать и по другим полям: где-то тоже в форме списка (Марка, Объём, Состояние, Цвет – с соответствующими вспомогательными, как для года, наборами), а где-то и в ином плане. Например, значение цены в долларах можно ограничить целым числом от 300 до 2000, а пробег – от нуля до 200. Для поля «Дата», удобно выбрать проверку, естественно, даты, и тоже в каких-то пределах. На значения поля «Телефон» следует установить длину цепочки вводимых символов, равную девяти (семь цифр и два тире). При любом нарушении установленных ограничений, программой будет подаваться сигнал тревоги, или стандартный, или предусмотренный нами самими.

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

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

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

Порядок добавления записей очевиден: щёлкаем соответствующую экранную кнопку в форме, и попадаем в самый низ таблицы. Определяется же «низ» по первой встретившейся пустой строке. Если нумерация в столбце А была поведена нами, скажем, до 19, то форма об этом так прямо и сообщает (запись номер такой-то из 19), и, значит, добавлять новые сведения будет уже в двадцатую. Водить, к сожалению, придётся всё вручную, никакие выборы из списка, никакая проверка или хотя бы автозавершение тут почему-то не работают. Это, конечно, существенный недостаток формы, который сводит на нет смысл её использования для ввода данных. Единственное её преимущество перед другими способами – это автоматическая защита вычисляемых полей, чтобы случайно не ввести что-нибудь туда, где значение должно вычисляться по формуле. Хотя защиту ячеек ведь можно установить и просто через «Сервис».

Важнее в форме другое действие, а именно, поиск нужных записей по образцу. Наряду с «Добавить» имеется ещё и экранная кнопка «Критерии». Если по ней щёлкнуть, то все поля в форме станут пустыми, так чтобы в них могли быть записаны условия отбора. Например, нам хотелось бы просмотреть конкретно только те записи базы данных, в которых говорится о сорок первых «Москвичах» не старее 90-го года. Первое ограничение формулируется просто вводом нужного названия в поле «Марка», а второе – чуть детальнее, с уточняющим знаком «больше или равно» в поле «Год» (т.е. >=1990).

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

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

Работа наша будет простой: нажатие кнопки того или иного поля раскроет список всех встречающихся в нём значений и, самое главное, позволит установить тот критерий отбора, который нам желателен. Пусть, например, нам надо выделить некоторые записи по ценовому признаку, чтобы просмотреть информацию об автомобилях не очень дорогих, но и не самых дешёвых. В списке, развернувшемся после щелчка по кнопке со стрелкой, мы выберем третий пункт, чтобы сформулировать само «Условие». Формулировку позволяет выполнить диалоговое окно «Пользовательский фильтр». Допускаются одно или два ограничения на значения поля, логически связываемые союзами «и» и «или».

В нашем случае речь идёт о машинах дешевле 1200, но дороже 700 долларов. Предельные величины выбираются из правых раскрывающихся списков, а отношение к ним – из левых. Там предлагаются варианты (операторы) «равно», «не равно», «больше», «меньше» и т.д. В результате применения фильтра, по нажатию ОК, мы получим временное сокрытие тех записей, которые не удовлетворяют поставленному требованию, их получается всего пять. А в знак того, что некоторые записи скрыты, сама стрелка, да и номера видимых строк таблицы, становятся не чёрными, а синими.

Фильтрацию можно продолжить и дальше, по какому-нибудь другому полю. Допустим, из этих пяти записей нас интересуют только модели ВАЗ. Тогда аналогичную операцию надо провести над полем «Марка автомобиля». Условиями здесь будут два отношения: «Больше» со значением первой буквы названия т.е. В, и «Меньше» со значением Г (всё, что начинается с В попадает в этот интервал). Количество выводимых на экран строк уменьшится до трёх. Таким образом, два ограничения, на цену и на марку автомобиля также соединятся союзом «и»: это будут ВАЗы от 700 до 1200 долларов. Из них можно отобрать те, что получше, введя третий критерий «Равно» «отл» или «Равно» «идеал» в поле «Состояние». И так далее.

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

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

Но самого по себе нахождения курсорной рамки где-то внутри списка теперь будет недостаточно, для того, чтобы можно было приступать к действию. Если «Автофильтр» мы сначала включаем, а потом уже задаём условия, то для «Расширенного», наоборот, прежде надо описать критерии поиска. Лучше всего сделать это на отдельном листе, скопировав туда все ячейки с заголовками полей из диапазона A3:L3, и, заодно, присвоив обоим листам подходящие имена. Под заголовками на этом втором листе мы и разместим условия.

Порядок здесь такой. Условия, выставляемые на разные поля и расположенные в одной строке, будут сочетаться по логическому закону «и», - в этом смысле отличия от «Автофильтра» нет. Но вот самих строчек может быть несколько, и все они соединятся по закону «или», а это уже позволяет формулировать сколь угодно сложные запросы.

Допустим, мы ещё не решили точно, какой конкретно автомобиль купить, но некоторые требования должны быть выполнены обязательно. Во-первых, если это будет старый Москвич (2140 или 412), то представляется неразумным платить за него более 500 долларов, да и то, только когда он в отличном состоянии. Если же, во-вторых, речь пойдёт о более или менее современном Москвиче-2141, то нас устроит цена до 1000 долларов при его хорошем состоянии, либо до 700 при состоянии среднем. Причём двигатель должен быть не москвичёвский, объёмом 1,5 или 1,7 л, а жигулёвский, объёмом 1,6. И, наконец, в-третьих, ВАЗ после 1995 подойдёт любой модели, в хорошем состоянии не дороже 1200 долларов, а в среднем – до 1000. Но только не белого цвета, и пробег чтобы был меньше сотни. Перечислять подходящие варианты, в принципе, можно бы и дальше, но остановимся пока на этом.

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

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

Расширенный фильтр. Итак, критерии для искомых записей, так или иначе, но сформированы, - пора произвести собственно поиск. Возвращаемся на лист данных и, не забыв поместить курсор куда-нибудь внутрь таблицы, всё равно в какую её клетку, приступаем к делу: Данные (меню)  Фильтр  Расширенный фильтр.

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

По умолчанию программа считает, что поиск надо вести по всей базе данных, начиная с заголовков и до последней, нижней строки, которая не совсем пуста, а хоть чем-то заполнена, даже если бы и в одном только поле «№». В нашем случае, поскольку нумерация в крайнем левом столбце была начата с четвёртой строки и доведена до тридцать второй (19 записей), то исходным диапазоном предложен A3:L22 (с правой стороны тоже по умолчанию берётся самый крайний непустой столбец).

Всё это нам подходит, несмотря на то, что полноценных записей пока что ещё на одну меньше, чем девятнадцать. А вот по второму вопросу изначально ничего не указано, так что нам придётся сначала щёлкнуть по красной кнопочке справа от соответствующего места ввода, затем перейти на лист критериев и выделить мышкой все те строки A3:L15, в которых объяснялось, что же нам нужно (опять же включая и сами названия полей).

По поводу вывода результатов нам предложено «Фильтровать список на месте». Это значит, как в «Автофильтре», с временным скрытием «не прошедших по конкурсу» строк прямо в самой исходной таблице. Если же нам представляется более наглядным, чтобы все отфильтрованные записи были помещены в какое-то отдельное место, то так и надо об этом заявить – щёлкнуть по соответствующему кружочку в переключателе «Обработка», и указать новое местоположение. Правда, оно обязательно почему-то должно быть на том же листе «Данные», например, в нижней его части, начиная, скажем, с ячейки A50.

Можно было бы ещё, для полноты картины, установить флажок «Только уникальные записи», но в нашей таблице и так нет повторяющихся строчек. Хотя для больших баз данных это возможность даёт дополнительное средство устранения ошибок.

В нашем случае итогом фильтрации будут всего пять записей. Мы можем их распечатать, скопировать в новый лист или даже новый файл, быть может, для дальнейших операций. А когда потребуется вернуть все исходные данные в первоначальное состояние, то к нашим услугам ещё один пункт меню: «Данные»  «Фильтр»  «Отобразить всё».

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

Сделать это можно буквально одним касанием – просто щёлкнуть мышью по значку сортировки в стандартной панели инструментов. Вообще-то их там два, таких значка, от А до Я, и от Я до А, - мы возьмём, для определённости, первый. Результат, правда, может оказаться совсем не тем, которого ожидаешь. Всё дело в том, что сортировка произведётся по тому полю, где находится в момент щелчка курсорная рамка. И если перед щелчком не позаботиться о её переводе куда надо, то и отсортировать можно по, скажем, номеру телефона, или, например, по пробегу, а то и по примечанию.

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

Можно, кстати, и к первоначальному положению вернуться, недаром ведь в поле «№» введена была нумерация. Но, пожалуй, не хотелось бы терять так удачно подвернувшийся под руку порядок группировки по номерам АТС. Вызовем, поэтому, службу сортировки не щелчком по панели инструментов, а полноценно, через меню: Данные  Сортировка

Здесь мы всё и объясним программе, в какой очерёдности надо сортировать поля, и как их сортировать: какие по возрастанию значений, какие по убыванию. Более того, мы вправе заглянуть в «Параметры сортировки», хотя менять там ничего и не требуется, - просто так, для ознакомления с тем, что в случае необходимости можно сортировать не только записи, т.е. строки выделенного диапазона таблицы, но и столбцы. Учитывая при этом, какими буквами, большими ли, маленькими написан текст, или нет. К тому же сортировку делая обычную, по алфавиту, или какую-нибудь особую, по значениям некоторого списка, типа Пн, Вт, Ср, и т.д.

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

Проверочные вопросы.

Чувствительно ли автозавершение к регистру вводимых символов?
В процессе формирования условий «Проверки» для числового поля «Год», специально был создан, в стороне от таблицы, вспомогательный перечень всех допустимых значений. Какие противопоказания имеются относительно того, чтобы в качестве источника взять значения прямо из самого поля, выделив достаточно широкий диапазон уже введённых ранее значений?
В чём проявляется различие между тремя видами сообщений об ошибке, которые могут появиться, если при вводе данных были нарушены условия заказанной «Проверки»?
В чём состоит различие между двумя способами удаления ненужной записи: через меню Правка  Удалить (с предварительным выделением строки таблицы), или через экранную кнопку «Удалить» в режиме «Форма»?
Как следует изменить условие выборки в рассмотренном на занятии примере автофильтра, чтобы обратить его действие, т.е. просмотреть, наоборот, скрытые записи? Будет ли это обращение полным?
Как установить дополнительное ограничение в сформированном на занятии наборе критериев для расширенного фильтра, заключающееся в том, что у автомобиля обязательно должна быть отметка о техосмотре (ТО), неважно какого года, 2000 или 2001?
Какое условие надо добавить в рассмотренную на занятии комбинацию, чтобы наиболее привлекательная модель ВАЗ-2106 проходила бы при любых обстоятельствах?
Каков будет результат, если перед выполнением сортировки, например, по признаку «Состояния», мы заботливо выделим, в целях большей определённости своего пожелания, все ячейки этого поля?
Почему при сортировке записей по «Дате» не соблюдается алфавитный порядок и, несмотря на заказанное возрастание, сентябрь пойдёт раньше октября, ноября и декабря?
Если, допустим, первая запись базы данных нам стала больше не нужна, то как надо её исключить из дальнейшего рассмотрения, - путём очистки содержимого (т.е. выделить ячейки и нажать клавишу Del), или же обязательно путём полного удаления всей строки таблицы?

Задание для самостоятельной работы.

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

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

Blog

Занятие Картотека подержанных автомобилей стр

Содержание