Урок 15. Продолжение работы с таблицами

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

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

Записью здесь является строка списка, где представлены сведения об одном виде продукта, начиная от его наименования и кончая датой покупки, например: Масло сливочное; Килограмм; 0,5; 100; 50; 11 мая 2004. Диапазон списка – это группа ячеек списка, где содержатся взаимосвязанные данные, в нашем случае – перечень продуктов для встречи праздника 1 мая. Имя поля – это заголовок поля, находящийся в первой строке. В нашем случае это: Наименование продуктов, Единица измерения, Количество, Цена единицы, Сумма, Дата покупки. Нужно иметь в виду, что максимальная длина имени поля не должна превышать 255 символов. Поле – это определенный элемент данных, в нашем случае – одно из представленных значений из Наименования продуктов, Единицы измерения, Количества, Цены единицы, Суммы, Даты покупки, Общего веса продуктов, Общей суммы покупок.

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

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

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

Добавить – позволяет вводить новые записи;

Удалить – производит удаление текущей записи после появления предупреждающего окна (см. рис.6.4.) и нажатия там на кнопку ОК;

Вернуть – отменяет предыдущую команду;

Назад – совершает переход на предыдущую запись, удовлетворяющую установленным критериям;

Далее – производит переход на следующую после текущей записи и удовлетворяющей критериям поиска;

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

Ключом для поиска является какой-либо символ из имеющихся в списках, а если некоторые из перечисляемых неизвестны, то вместо них указывается (?) для одиночного символа и (*) для некоторого их количества. Например, если указать В?Р, то среди имеющихся слов будут найдены ВАР и ВОР, а ключ В*Р позволит найти ВАР, ВОР, ВЗОР и ВЗДОР. Если ключевые слова или значения введены в соответствующие поля, то поиск производят нажатием на кнопку Далее или Назад.

Закрыть – совершает выход их окна Формы данных и возвращение к текущему рабочему листу.

Тем не менее, для того, чтобы заводить поля таблицы проще воспользоваться обычными средствами программы Excel, как это делалось ранее, тем более, что в программе имеется возможность автозаполнения. Однако, при помощи режима критериев можно легко найти требуемые записи особенно тогда, когда таблица имеет большой размер.

Что такое режим автозаполнения? Допустим, что в текущую ячейку введено значение 01 мая 2004. Когда мы введем в ячейку ниже символы «01 м», то справа сразу появится текст, который берется из предыдущей ячейки, так как начальные символы текста совпадают, как это показано на рисунке. Если такое словосочетание достаточно для использования в ячейке, нажмите на клавишу Enter, иначе продолжите ввод. Поэтому лучше текст набирать по столбцам.

Предположим, что мы набираем единицу измерения и ввели значения в несколько ячеек, как это показано на в левой части рисунке. Щелкнем правой кнопкой мыши по выделенной ячейке и установим режим Выбрать из списка. После этого на экране появится список введенных ранее значений, как это показано в правой части рисунка. Если нужно отказаться от выбранного режима, нажмите на клавишу Esc.

Автофильтр. В некоторых случаях поиск необходимо провести во всех полях списков по некоторому критерию, например, по определенному виду продукта питания, в частности, имеющие вначале названия слово Масло подсолнечное или с определенной розничной ценой.

Для этого вначале выделите диапазон списка. Далее воспользуемся режимом: Данные →Фильтр →Автофильтр. Теперь в полях с их заголовками появятся кнопки с изображением стрелок, направленных вниз - (см. рис. слева). Нажав на нужные стрелки, появится список, предназначенный для поиска. Выберите в нем элемент, соответствующий критерию поиска (например, имеющий в названии текст: килограмм, см. рис. справа). После щелчка по такому элементу на экране появится новый список, содержащий искомые записи.

Возможен поиск по другому критерию – по Условию, т.е. при соблюдении определенного условия на экране появятся только отвечающие таким ограничениям элементы списка.

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

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

Иногда требуется произвести поиск по нескольким условиям. Если условия относятся к разным столбцам, то нужно применить каждое условие последовательно сначала к одному столбцу, затем ко второму и так далее. Например, нам нужно просмотреть список продуктов, которые были приобретены 01 мая 2004 года и сумма стоимости которых больше 50 рублей. В этом случае сначала выберем дату 01 мая 2004 года в поле Дата покупки, как это показано на рисунке справа. После этого в строке Сумма выберем режим условие, в котором в верхних полях появившегося окна (см. рис 11) установим больше и 50. В результате на экран будет выведены две записи: сельдь малосоленая (строка 7 таблицы) и сосиски молочные (строка 11 таблицы).

Иногда нужно установить несколько условий по одному столбцу, например, нужно просмотреть продукты, стоимость которых больше 30 рублей и меньше 50. В этом случае щелкнем по кнопке в поле Сумма и в появившемся панно выберем Условие. Далее в появившемся окне установим два условия, как это показано на рисунке. Как видно, в окне указано поле столбца, где будет выполняться условие (Сумма), далее находится два условия: чтобы сумма была больше 30, которое указано в верхних двух полях. Ниже находится операция И, которая установлена во взаимоисключающем переключателе и еще ниже условие меньше 50. В результате на экране появятся две строчки: Масло подсолнечное (3я строчка) и Сыр Российский (4я строчка).

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

Транспонирование. С операцией Автоформата довольно удобно работать, но работа происходит только по столбцам. Что делать, если нужно те же действия провести по строкам? В этом случае транспонируйте таблицу, то есть строки перенесите на место столбцов, а столбцы на место строк. Для этого выделим таблицу и скопируем ее в буфер. Далее нажмем на стрелку, которая находится слева от значка вставки из буфера () и на экране появится панно с режимами (см. рис. слева), в котором выберем режим транспонировать. В результате вид таблицы на экране будет такой, как показан на рисунке(для того, чтобы таблица была более читаема, был применен режим Формат →Столбец →Автоподбор ширины).

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

Использование панели инструментов Стандартная для сортировки данных. Перед выполнением режима выделим таблицу с данными, после этого нажмем на кнопку - . В результате на экране появится таблица в виде, показанном на рисунке. Как видно из рисунка, наименование продуктов сортировано в алфавитном порядке от А до Я. Если нажать на кнопку , то сортировка будет проведена также по наименованию товаров, но в обратном алфавитном порядке от Я до А.

Однако, при использовании данной сортировки нельзя сортировать по сумме и по другим столбцам. Если выделить только столбец Сумма, и попробовать его сортировать, то этот столбец будет отсортирован, но другие столбцы сортировке не подлежат. Поэтому воспользуемся следующим режимом.

Применение режима Сортировка из строки меню программы Ms Excel. Такой режим позволяет выполнять сортировку по трем критериям, а также не только по столбцам, но и по строчкам.

Выделим данные таблицы (строчки 2-9, столбцы 1-6), после этого войдем в режим Данные →Сортировка. На экране появится окно, содержащее три поля. Укажем в первом поле название столбца, по которому будет проводиться сортировка, для этого нажмем по кнопке, которая расположена справа от поля () и в появившемся панно выберем Сумма, как это показано на рисунке слева. Отметим, что если включить переключатель идентифицировать поля листа по обозначениям столбцов листа, то сортировка будет проводиться по строкам.

После установки значения в первом поле нажмите на кнопку ОК, после этого данные в таблице будет сортированы, как это показано на рисунке ниже.

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

О↙О↘О↙О↘О↙О↘О↙О↘О↙О↘О↙О↘О↙О↘О↙О↘О