Прокофьева О. Е. / Excel / СМ 4 / excel 2003 Работа со списками

Работа со списками

Чаще всего нужно видеть все данные в таблице. Однако иногда желательно, чтобы на экране отображалась только их часть. Такая необходимость возникает тем чаще, чем больше таблица. Excel позволяет временно "отфильтровать" данные, отобрав для визуализации только те, которые удовлетворяют определенным условиям. В этом разделе вы ознакомитесь с несколькими простейшими способами удаления с экрана лишних данных, для того чтобы иметь возможность сконцентрировать внимание только на необходимой информации. После того, как база данных отфильтрована, оставшиеся записи можно просматривать, редактировать, копировать или печатать, как и целые таблицы Excel. excel-2003.ru/e14/Index01

Примечание При автофильтрации не происходит удаления данных из таблицы. Они лишь становятся вре-менно невидимыми.

1. Если список отделен от других данных в рабочей таблице хотя бы одной пустой строкой и столбцом, выделите в этом списке любую ячейку. В противном случае выделите весь список.

2. Из меню Данные выберите команду Фильтр, а из появившегося вложенного меню — пункт Автофильтр.

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

3. Теперь рядом с именем каждого поля появился элемент управления автофильтром — кнопка с изображением стрелки. С помощью этих кнопок можно фильтровать список по значениям соответствующих полей (столбцов). Например, в данной таблице можно сделать видимыми записи только тех сотрудников, чью заработную плату планируется увеличить не менее чем на 7%. (Критерием является значение 7% в поле % увеличения).

4. Щелкните на любом элементе управления автофильтром. Раскроется список, в который входит содержимое всех ячеек в столбце, а также несколько специальных элементов, таких, как (Все), (Первые 10...) и (Условие...).

5. 

5. Если выбрать из списка некоторый элемент, все записи, не удовлетворяющие этому значению, будут "отфильтрованы" и станут невидимыми. В частности, если в списке % увеличения выбрать пункт 7%, останутся видимыми записи только тех сотрудников, которым должны увеличить заработную плату на 7%, как показано на рисунке.

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

6. Если потребуется снова сделать видимыми все записи, выберите из списка, с помощью которого осуществлялась фильтрация данных, пункт (Все).

7. Чтобы вывести на экран первые 10 значений определенного поля—или первые 8, или первые 3, и т.п. — выберите из соответствующего списка пункт Первые 10. Это может понадобиться, например, чтобы найти 5 сотрудников, получающих самую высокую заработную плату. При этом появляется диалоговое окно Наложение условия по списку, показанное на рисунке. (Первые 10—число весьма приблизительное: в этом диалоговом окне можно выбрать просмотр от 1 до 500 элементов).

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

9. После того, как режимы фильтрации установлены, щелкните на кнопке ОК, и на экране появится отфильтрованная версия списка. Обратите внимание на то, что строки 9—12 и 14,15 не видны, поскольку соответствующие сотрудники не вошли в число пятерых, получающих наибольшую заработную плату.

http://excel-2003.ru/e14/Index01.htm

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

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

Примечание Отбирать текстовые полятакжелегко, какицифровые. В этом случае считается, что буква а меньше, чем б, и тд. Например, если ввести фамилию Иванов и выбрать оператор "больше", то будут отобраны все фамилии, расположенные в алфавитном порядке после Иванова.

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

2. Щелкните на элементе управления автофильтром того из полей, по которому хотите провести отбор. Например, если нужно отфильтровать записи на основании некоторого уровня заработной платы за текущий год, щелкните на кнопке со стрелкой рядом с полем заработной платы.

3. Из раскрывшегося списка выберите пункт Условие. Появится диалоговое окно Пользовательский автофильтр. В верхней правой строке введите число или фрагмент текста, который будет использован в качестве фильтра. Например, если нужно отобрать всех сотрудников, зарабатывающих менее 4000, введите число 4000.

4. Из верхнего левого списка выберите оператор, определяющий способ сравнения с введенным числом или фрагментом текста. В списке приводится полный набор операторов сравнения: равно, не равно, больше, больше или равно, меньше, меньше или равно, и т.п. Чтобы отобрать сотрудников, чья заработная плата не превышает 4000, выберите оператор меньше.

5. Щелкните на кнопке ОК, в результате чего список будет отфильтрован, и записи, не удовлетворяющие указанным условиям (в нашем случае — данные о сотрудниках, зарабатывающих более 4000) временно исчезнут с экрана.

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

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

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

2. Из меню Данные выберите команду Итоги. Появится диалоговое окно Промежуточные итоги.

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

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

5. Теперь необходимо выбрать поле, в котором будут выводиться промежуточные итоги. В данном случае, поскольку они должны выводиться в поле Зарплата, оставьте выбранным это поле. Кроме того, отмените выбор всех прочих полей. Обратите внимание, что ничто не мешает выводить промежуточные суммы в нескольких полях одновременно.

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

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

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

7.Совет Подведя промежуточные итоги, можно вывести на экран различные уровни детализации списка. Если щелкнуть на цифре 1 в верхнем левом углу рабочей таблицы, будет показана только общая сумма. Если щелкнуть на цифре 2, будет показана общая сумма и промежуточные итоги, но не индивидуальные записи. Если щелкнуть на цифре 3, будут показаны общая сумма, промежуточные итоги и все индивиду-альные записи.

http://excel-2003.ru/e14/Index07.htm

Как ссылаться на данные из других рабочих таблиц

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

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

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

1.Щелкните на кнопке   Копировать на панели инструментов или воспользуйтесь командой Копировать из меню Правка.

3. Щелкните на ярлычке рабочей таблицы, где будет находиться ссылка на данные, и выделите ячейку, куда эти данные будут скопированы. При ссылке на несколько ячеек выделите верхний левый угол области, в которую они будут копироваться.

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

4.Активизируйте одну из ячеек с ссылкой на данные. Обратите внимание, что в строке формул появляется формула, содержащая имя таблицы и имя ячейки с данными, на которые осуществляется ссылка.

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

7. Чтобы заполнить столбец Бюджете таблице Сентябрь скопируйте ячейки С4 - С9 таблицы Август. Для этого проделайте действия описанные в п.п. 1-4.

http://excel-2003.ru/e15/Index04.htm

Как управлять рабочими таблицами

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

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

1.

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

2.Если таблицу нужно не переместить, а скопировать, при перемещении удерживайте нажатой клавишу Ctrl. В этом случае в процессе перемещения рядом с указателем мыши появятся пиктограммы в виде листа бумаги с изображенным на нем знаком "+", свидетельствующие о том, что происходит копирование, и, треугольник, указывающий, куда будет вставлена копия листа.

3.После того, как вы отпустите кнопку мыши, копия рабочей таблицы будет помещена в обозначенное место. Имя копии будет совпадать с именем оригинала, после которого в скобках следует номер копии. Указанным методом можно также скопировать несколько листов одновременно, предварительно сгруппировав их.

4.Совет По умолчанию Excel создает рабочие книги, состоящие из трех листов. Если необходимо регулярно создавать книги, содержащие большее их количество, можно настроить Excel на автоматическое создание более объемных (с большим числом листов) рабочих книг. Для этого воспользуйтесь командой Параметры изменю Сервис. В появившемся диалоговом окне откройте вкладкуОбщие и введите большее число в поле Л истов в новой книге. В это поле можно ввести любое число от 1 до 255. Затем щелкните на кнопке ОК.

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

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

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

Как использовать в формулах данные из других таблиц

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

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

1. Активизируйте рабочую таблицу и в ней — ячейку, которая должна содержать формулу. В зависимости от обстоятельств Можно также снабдить таблицу заголовком, названиями строк и столбцов.

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

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

3.Введите оператор. Например, чтобы сложить содержимое текущей ячейки с содержимым какой-либо другой ячейки, введите знак +.

5. Повторите пп. 3 и 4 столько раз, сколько необходимо для ссылок на используемые в формуле данные — как из текущей, так и из других таблиц. Например, в показанной здесь формуле содержимое ячейки Е5 из таблицы I квартал складывается с содержимым одноименных ячеек из таблиц II квартал и III квартал.

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

6.Примечание Можно создавать трехмерные ссылки, указывающие на ячейку или диапазон ячеек в нескольких таблицах, принадлежащих одной рабочей книге. Трехмерные ссылки состоят из имен первой и последней таблиц, разделенных двоеточием, и ссылки на ячейку или диапазон ячеек, отделенной от названий таблиц восклицательным знаком. Например, трехмерная ссылка Лист1Лист4!А1:С5указывает на диапазон ячеек А1:С5 в рабочих таблицах от Лист! до Лист4, Трехмерные ссылки позволяют, в частности, упростить формулу, рассмотренную в пп. 5 и 6, до =СУММ('1 квартал':!!! 'квартал'!Е5) и избавляют пользователя от необходимости ссылаться на каждую ячейку в отдельности. Чтобы ввести эту формулу, наберите =СУММ, выделите листы I квартал—III квартал, щелкните на ячейке Е5 и нажмите клавишу Enter.

Как принимать решения, используя функцию ЕСЛИ

Часто в одной ситуации требуется выполнить одни действия, а в другой — другие. В подобных случаях удобна функция ЕСЛИ. Эта функция проверяет заданное условие и, если оно выполняется, то производит одно действие, если нет — другое. Например, этой функцией можно воспользоваться для расчета одной комиссионной ставки для торговых агентов с объемами продаж свыше 100 тыс. и другой — для агентов с объемами продаж, не превышающими 100 тыс. Как вы увидите в дальнейшем, функция ЕСЛИ требует трех аргументов: проверяемое условие; величину, возвращаемую в случае, если данное условие выполняется, а также величину, возвращаемую при невыполнении данного условия.

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

1. Выделите ячейку, в которой вы собираетесь разместить результат выполнения функции ЕСЛИ.

1.Щелкните на кнопке   Вставка функции в строке формул, из списка Категория выберите Логическая, а из списка Выберите функцию: — ЕСЛИ и щелкните на кнопке ОК.

2.В появившемся диалоговом окне в поле Логическое выражение введите условие. Например, если нужно задать один уровень комиссионных выплат для продавцов, добившихся продаж, превышающих 100 тыс., а другой—для тех, у кого продажи не превышают 100 тыс., в этом поле можно ввести условие Ь4>100000, считая, что объем продаж содержится в ячейке В4.

3.

4. В поле Значение_если_истина введите величину, которая будет возвращена, если окажется, что заданное условие выполняется. Например, если нужно задать 6% комиссионных для тех, у кого объем продаж превышает 100 тыс., в этом поле можно ввести Ь4*0,06считая, что в ячейке В4 содержится объем продаж.

4.

5. В поле Значение_если_ложь введите величину, которая будет возвращена, если окажется, что заданное условие не выполняется. Например, если нужно задать 5% комиссионных для тех, у кого объем продаж не превышают 100 тыс., в это поле можно ввести Ь4*0,05.

5.

6. Щелкните на кнопке ОК. Excel внесет функцию в рабочую таблицу и будет помещать результаты ее выполнения в выделенную ячейку.

Примечание Если оставить пустым поле Значение_если_-ложь, то при невыполнении заданного усло-вия Excel возвратит не число, а слово Ложь.

7. Если данную функцию необходимо скопировать, можно воспользоваться одним из уже известных методов. В данном примере, чтобы рассчитать комиссионные для всех других работников, достаточно перетащить маркер заполнения на новое место, в результате чего функция, созданная в пп. 2—5, будет скопирована.

7

Как пользоваться функциями ПР Функции ВПР и ГПР (две разновидности функции ПР) позволяют находить заданные величины в справочных таблицах данных. Например, с их помощью можно находить цены в каталоге или ставки налога в налоговой таблице. Функция ВПР ищет нужную величину в крайнем левом столбце рабочей таблицы, возвращая значение из заданного столбца в ту же строку. С ее помощью можно найти уровень дохода в крайнем левом столбце и просмотреть соответствующую ставку налога в другом столбце таблицы. ГПР отыскивает величину в верхней строке таблицы и возвращает величину из заданной строки в тот же столбец. Примечание Excel может находить не только точное совпадение с заданной величиной, но и ближайшее к ней значение. Например, при назначении надбавок к зарплате с учетом уровня зарплаты может понадобиться составить список, в котором надбавки будут увязаны с зарплатой. В этом случае следует поступить следующим образом. Расположите величины в порядке возрастания в крайнем левом столбце. После этого в текстовом окне Диапазон_просмотр введите слово "истина" . Допустим, в крайний левый столбец внесены суммы зарплат 5000, 3000. и т.д., надбавка 500 относится к зарплате 2500, а надбавка 350 — к зарплате 3000. Если нужно найти надбавку к сумме зарплаты 2730, Excel определит, что эта сумма меньше 3000 и попытается найти ближайшую надбавку среди меньших зарплат. Такой надбавкой окажется 500, соответствующая зарп-лате 2500. Примечание Функциям ПР доступны также таблицы из других рабочих книг. Ваша первая задача состоит в том, чтобы открыть справочную таблицу, содержащую данные, которые необходимо просмотреть. Приведенная на рисунке таблица содержит номера позиций, а также соответствующие описания и цены на фотопленку из магазина фотопринадлежностей. Поскольку просматриваемые величины (код товара) оказались в крайнем левом столбце, необходимо воспользоваться функцией ВПР. 1. Теперь можно построить функцию поиска, которая будет находить в таблице нужные величины. Например, можно найти цену на данный тип пленки по ее коду. Начните с выделения ячейки, в которую будет занесена формула, после чего щелкните на кнопке   Вставка функции. 2. В диалоговом окне Мастер функций в списке Категория выберите Ссылки и массивы, затем в списке Выберите функцию: выберите ВПР и щелкните на кнопке ОК. 3. В поле Искомое_значение задайте величину, которую хотите найти с помощью Excel в крайнем левом столбце таблицы. В нашем примере нужно найти порядковый номер конкретного типа пленки. Эту величину можно ввести в окне или щелкнуть на кнопке  , а затем выделить в рабочей таблице ячейку. 4. В поле Таблица введите диапазон ячеек, в которых содержится справочная таблица. В данном примере таким диапазоном является А4:С9 — сюда входят порядковые номера, описания и цены. (Заметьте, что заголовки столбцов в этот диапазон не включены). 5. В поле Номер_столбца введите номер столбца, в котором будет проводиться поиск нужной величины. В данном примере мы ищем цены в столбце 3. Если задать столбец 2, Excel возвратит описание пленки, порядковый номер которой указан в поле Искомое_значение. 6. Чтобы программа искала точное совпадение с заданной величиной, а не ближайшее к ней, в поле Диапазон_прос-мотра введите слово ложь. 7. Щелкните на кнопке ОК. Excel поместит результаты выполнения функции в рабочую таблицу. Таким образом, поиск цены на фотопленку не составил никаких проблем. Хотя в нашем примере функция ПР и таблица данных расположены на одном листе, но с тем же успехом эту функцию можно было бы поместить и в другом месте. 8.