АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2003 ГОД

Удобный прайс-лист

• Все журналы \ Номер 5/228 `2003 от 03.02.2003 \ Удобный прайс-лист

Надежда БАЛОВСЯК

Эта статья посвящена средствам Excel, позволяющим работать со списками.

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

Списком может считаться прайс-лист любой средней фирмы, занимающейся оптовой или розничной торговлей. Возьмем в качестве примера прайс-лист компьютерной фирмы, он имеет некоторые особенности. Прайс может состоять из нескольких типовых конфигураций готовых компьютеров, либо из списка наименований комплектующих, из которых продавец вместе c покупателем подберут нужную систему. Мы подробнее остановимся на прайс-листе второго типа, по ходу раскрывая некоторые «маленькие хитрости».

Итак, список — это фрагмент рабочего листа, оформленный в соответствии с определенными требованиями. Выдвигаются следующие требования к оформлению списка:

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

каждый столбик имеет заголовок;

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

иное форматирование заголовка списка по сравнению с остатком списка (например, другой размер или тип шрифта).

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

формирование прайс-листа в удобном виде как для покупателя, так и для продавца или офис-менеджера;

проверка корректности веденных данных;

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

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

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

Прайс-лист для продавца будет более полным. Он должен содержать следующую информацию: название комплектующего, его тип, гарантийный срок. Кроме этого, в отношении некоторых комплектующих понадобится уточнить их тип, например, процессоры могут относиться к стандартам Socket A или Socket 370. Поэтому введем в наш прайс дополнительное поле «тип 1», где будем указывать информацию подобного рода. Кроме этого, офис-менеджеру в полном прайсе могут понадобиться следующие поля:

«Продано» — поле, отображающее количество реализованных комплектующих данного типа за определенный промежуток времени, например за прошлую неделю;

«Поставщик» — название фирмы-поставщика;

«Дата поставки»;

«Покупная цена»;

«Цена продажи».

После формирования структуры списка его необходимо заполнить. При вводе больших списков возможны различные ошибки: от опечатки в названии фирмы-партнера или фамилии ее директора до ошибки в цене. Excel позволяет упростить ввод данных в список и при этом избежать ошибок. Возможности проверки правильности ввода числовых данных в Excel невелики, можно задать лишь диапазон, в котором должны находится данные. Но даже это позволит избежать многих проблем. Все это реализованы в меню Данные > Проверка, в результате выбора которого откроется окно Проверка вводимых значений.

Рассмотрим на примере нашей таблицы, каким образом обезопасить пользователя от ошибок при заполнении списка. В первую очередь обратим внимание читателей на то, что в Excel есть возможность задать отображение подсказки рядом с ячейкой при вводе в нее данных. Для этого в окне Проверка вводимых значений нужно перейти на вкладку Сообщение для ввода и набрать сообщение, которое будет появляться при вводе данных в ячейку. Удобный вариант — использовать переключатель Отображать подсказку, если ячейка является текущей. Например, при заполнении поля «Дата поставки» в качестве примечания зададим «Вводите дату между 01.01.2001 и 01.12.2002» (рис. 1).

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

Далее в окне Проверка вводимых значений в разделе Тип данных нужно выбрать значение Список, в поле Источник указать адрес списка данных, сформированного на рабочем листе, либо имя диапазона, созданного на отдельном рабочем листе. При задании списка значений в окне Проверка вводимых значений, в поле Источник, необходимо ввести строку «=имя диапазона» (рис. 2).

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

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

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

Для поля Цена в качестве условий проверки введенных значений укажем, чтобы они были неотрицательными (цена >0), для поля Гарантийный срок — зададим проверку введенных значений между значениями 3 и 36.

Аналогично проходит работа и с датами. Для проверки правильности заполнения поля Дата поставки укажем временной интервал от 01.01.2000 до 01.01.2003.

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

Если в поле Тип данных выбрать значение Другой, пользователь сможет задать формулу, в соответствии с которой будут проверяться данные. Формула должна начинаться со знака «=». При вводе данных в ячейку некорректными будут считаться те, для которых результатом вычисления является значение Ложь.

Итак, пусть в нашей фирме цена продажи комплектующего должна превышать закупочную стоимость минимум на 30%. В противном случае фирма будет работать себе в убыток. Тогда проверить правильность заполнения поля «Цена продажи» можно, выбрав в окне Проверка вводимых значений в качестве типа данных значение Другой, а в поле Формула указать формулу «Цена продажи»>= «Цена закупки»*1,3.

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

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

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

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

Например, покупатель хочет получить список доступных в продаже процессоров ценой меньше $100. В этом случае необходимо применить автофильтр к полю Тип и ввести в качестве условия отбора значение CPU. Далее применить повторно автофильтр к полю Цена продажи, выбрав пункт Условие и задав тут Меньше 100.

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

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

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

Целесообразно формировать критерии отбора в строках выше от основного списка. Туда нужно скопировать строку с заголовком списка над пустыми строками. Ниже задаются критерии фильтрации. Чтобы объединить критерии фильтрации с помощью условного оператора «И», необходимо указать их в одной строке под заголовками полей. Если вы хотите воспользоваться условным оператором «ИЛИ», критерии требуется записывать в разных строках.

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

После формирования диапазона условий, выберите Данные > Фильтр > Расширенныйфильтр. В появившемся окне укажите параметры фильтрации. В поле Диапазон условий вводятся адреса ячеек, в которых находятся критерии фильтрации. В поле Исходный диапазон — адреса ячеек со списком. В этом же окне задается местоположение результата фильтрации —Фильтровать список на месте или Скопировать результат в другое место. При выборе второго варианта нужно заполнить поля Поместить результат в диапазон (рис. 3).

Задачу отбора конфигурации можно решить, воспользовавшись расширенным фильтром. Для этого с помощью поля Отбор формируем над основной таблицей диапазон условий. Оставить на рабочем листе лишь отобранную конфигурацию можно, если применить к полученному списку расширенный фильтр, указав для поля Отбор условие отбора значение «1». Результат работы расширенного фильтра отображен на рисунке 4. Стоимость отобранной конфигурации вычисляется функцией СУММЕСЛИ.

Еще раз хочу обратить внимание читателей на то, что в этой статье рассмотрена только небольшая часть возможностей Excel. Надеюсь, что «маленькие хитрости», которыми я поделилась с читателями, пригодятся им в повседневной работе.

Идёт загрузка...

Данную страницу никто не комментировал. Вы можете стать первым.

Ваше имя:
Ваша почта:
Комментарий:
Введите символы или вычислите пример: * Обновить