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

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

Примечание
Примечание

Используя символы подстановки, можно применять более одного фильтра или проводить отбор более чем по одному полю. Например, можно отобрать всех сотрудников, чьи фамилии начинаются с буквы Б, а после этого из получившегося списка — всех, чьи имена на-чинаются с буквы А.

Примечание
Примечание

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

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

Как найти нужную запись

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

Активизируйте любую ячейку в списке и выберите из меню Данные команду Форма.

Как осуществлять отбор данных с помощью операторов

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

Совет
Совет

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

Примечание
Примечание

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

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

Как осуществлять простой отбор данных

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

Примечание
Примечание

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

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

Как подводить промежуточные итоги

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

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

Как проводить более сложный отбор

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

Совет
Совет

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

Примечание
Примечание

При вводе в диалоговом окне Пользовательский автофильтр двух критериев порядок их следования не имеет значения. Например, можно указать первым критерий "больше чем 4000", а вторым — "меньше чем 5000", а можно — наоборот. Результат не зависит от порядка упоминания, как в режиме ИЛИ, так и в режиме И.

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

Как сортировать данные

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

Совет
Совет

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

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

Из меню Данные выберите команду

Шаг 1

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

Шаг 1

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

Шаг 1

2. Выбрав пункт Условие, откройте диалоговое окно Пользовательский автофильтр. Введите значение,котороехотите использовать для фильтрации. Поскольку точное значение в данном случае неизвестно, можно ввести Р*, что означает выбор всех фамилий, начинающихся с буквы Р, после которой следует любое количество других букв. "Звездочка" — это символ подстановки, заменяющий любое количество любых символов.

Шаг 1

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

Шаг 1

1. Появится диалоговое окно формы. Имя в строке заголовка соответствует названию листа рабочей книги Excel. Обратите внимание на то, что в левой части диалогового окна перечислены имена всех полей списка, а против них стоят данные первой записи списка.

Шаг 1

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

Шаг 1

Сортировка по возрастанию на панели инструментов.

Щелкните на любом элементе управления

Шаг 3

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

Шаг 3

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

Шаг 3

4. Если потребуется снова вывести на экран все записи, воспользуйтесь пунктом (Все) из раскрывающегося списка автофильтра. Совет
Совет

При поиске фрагмента, содержащего некоторую цифру или букву, которая не обязательно должна оказаться в начале слова или числа, введите знак * ("звездочку"), искомый символ и еще одну "звездочку". Например, если нужно найти человека, в чьей фамилии есть буква д, однако, возможно, она не стоит вначале слова, в диалоговом окне Пользова-тельский автофильтр следует ввести *д*.
Знак вопроса (?) — это символ подстановки, заменяющий любой единичный символ. Например, если выбрать в списке автофильтра поля Имя пункт Условие и в появившемся диалоговом окне ввести И??? (оставив при этом заданный по умолчанию оператор равно), то будут отобраны все имена из четырех букв, начинающиеся с буквы И. Иван войдет в этот список, а Игорь и Ирина—нет. Если же вместо этого критерия отбора ввести И*, то в отфильтрованный список войдут все трое сотрудников.

Шаг 3

4. Обратите внимание, что выбран режим И. Это означает, что Excel будет искать записи, удовлетворяющие обоим критериям одновременно. Если запись будет удовлетворять только одному критерию, то программа уберет ее с экрана.

Шаг 3

4. Щелкните на кнопке Далее, если хотите, чтобы поиск проводился ниже текущей записи, и на кнопке Назад, если поиск должен проводиться перед текущей записью. Чтобы повторить поиск с теми же критериями, необходимо повторно щелкнуть на одной из этих кнопок. Чтобы снова получить возможность просмотра всех записей, щелкните на кнопке Критерии и затем — на кнопке Очистить, в результате чего все критерии будут отменены. После этого щелкните на кнопке Вернуть. Теперь можно, используя кнопки Далее и Назад, просматривать все записи в списке.

Шаг 3

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

Шаг 3

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

Если выбрать из списка некоторый

Шаг 4

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

Шаг 4

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

Шаг 4

6. Символы подстановки можно использовать не только с оператором равно. Например, если нужно найти всех сотрудников, чьи фамилии находятся во второй половине алфавита, можно ввести в диалоговом окне Пользовательский автофильтр букву М, выбрать оператор больше или равно, после чего щелкнуть на кнопке ОК.

Шаг 4

5. Щелкните на кнопке OK, после чего фильтрация будет выполнена. Отфильтрованная таблица будет выводить на экран информацию только о тех сотрудниках, чей заработок составляет 4000-5000.

Шаг 4

6. Можно также найти - набор записей при помощи операторов сравнения (=, <, >, <=, >= и <>). Например, можно, щелкнув на кнопке Критерии, ввести в поле Зарплата 2003 г. критерий >=5000, после чего с помощью кнопок Далее и Назад просмотреть записи всех сотрудников, чья заработная плата превышает 5000.

Шаг 4

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

Шаг 4

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

При фильтрации списка Excel изменяет

Шаг 5

5. Примечание
Примечание

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

Шаг 5

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

Шаг 5

7.

Шаг 5

6. Если нужно отобрать записи, удовлетворяющие хотя бы одному из указанных критериев, выберите режим ИЛИ. Например, если в списке указаны отделы, можно отобрать всех сотрудников, работающих либо в отделе маркетинга, либо в отделе сбыта. Для этого введите в качестве одного критерия слово Сбыт, а в качестве другого — Маркетинг, после чего включите режим ИЛИ. (Здесь бессмысленно использовать оператор И, поскольку вряд ли кто-то работает в двух отделах одновременно).

Шаг 5

7. После того, как просмотр записей окончен, можно закрыть диалоговое окно Форма, щелкнув на кнопке Закрыть или на стандартной кнопке закрытия окна, расположенной в его верхнем правом углу. Примечание
Примечание

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

Шаг 5

5. После установки всех необходимых параметров в диалоговом окне Сортировка диапазона щелкните на кнопке ОК. Сортировка будет выполнена.

Шаг 5

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

После того, как режимы фильтрации

Шаг 6

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

Шаг 6

6.

Шаг 6

7.

Шаг 6

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

Шаг 6

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

Шаг 7

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

Шаг 7

6. Совет
Совет

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

Если список не был правильно

Шаг 8

Сортировка по убыванию. Совет
Совет

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

Шаг 8

7. Совет
Совет

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

На изменения, которые можно внести

Внимание!

На изменения, которые можно внести в запись в диалоговом окне Форма, накладывается ряд ограничений. Новые записи можно добавлять только в конец списка. Кроме того, можно изменять значения только тех ячеек, которые содержат данные. Значения ячеек, содержащих формулы с названия полей выводятся как текст на сером форе диалогового окна. Их изменить нельзя. Например, значение поля Зарплата на 2004 г. (проект) не может быть изменено, поскольку соответству-ющая ячейка содержит формулу. Совет
Совет

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

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

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

Примечание
Примечание

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

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

Как копировать и перемещать данные

Как копировать и перемещать данные из одной рабочей таблицы в другую

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

Совет
Совет

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

Выделите данные, которые нужно перенести или скопировать в другую рабочую таблицу.

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

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

Примечание
Примечание

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

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

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

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

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

Как вводить данные в несколько рабочих таблиц одновременно

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

Примечание
Примечание

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

Совет
Совет

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

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

Для выбора группы смежных рабочих

Шаг 1

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

Шаг 1

1. Совет
Совет

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

Шаг 1

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

Шаг 1

1. Щелкните на кнопке

Шаг 1

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

Если выбрана группа листов можно

Шаг 2

2. Если выбрана группа листов можно вводить данные в несколько таблиц одновременно, вводя их в одну таблицу. Например, при вводе ВСЕГО в ячейку А10 первой из показанных таблиц эти данные появятся во всех ячейках А10 во всех четырех сгруппированных таблицах. Пока все листы сгруппированы, не имеет значения, в какой таблице вводятся данные.

Шаг 2

Вставить на панели инструментов или воспользоваться ко-мандой Вставить из меню Правка. Если данные нужно перенести, щелкните на кнопке

Шаг 2

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

Шаг 2

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

Шаг 2

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

Можно также применять форматирование ко

Шаг 3

3. Можно также применять форматирование ко всем таблицам в группе. Например, если нужно выделить полужирным курсивным шрифтом заголовки столбцов, которые отображаются в строке 4 каждой рабочей таблицы, можно выделить нужные ячейки в любой из таблиц и применить к ним полужирный курсивный шрифт. Этот шрифт будет применен ко всем сгруппированным листам. Точно также могут быть применены и другие параметры форматирования.

Шаг 3

Вырезать панели инструментов, если копировать — на кнопке

Шаг 3

5. После того, как формула сформирована, нажмите на клавишу Enter или щелкните на кнопке

Шаг 3

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

Шаг 3

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

Можно даже вычислить итоговые значения

Шаг 4

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

Шаг 4

Копировать. Можно также воспользоваться командами Вырезать и Копировать из меню Правка или » комбинациями клавиш Ctrl+X и Ctrl+C. Вокруг выделенных данных появится рамка, показывающая, что теперь эти данные готовы к перемещению. В качестве дополнительной подсказки в строке состояния будет значиться: Укажите ячейку и нажмите Enter или выберите "Вставить".

Шаг 4

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

Шаг 4

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

Шаг 4

4. Совет
Совет

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

Можно быстро выделить все рабочие

Шаг 5

5. Совет
Совет

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

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

Шаг 5

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

Шаг 5

6. Примечание
Примечание

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

Шаг 5

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

Шаг 5

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

Шаг 6

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

Шаг 6

Шаг 6

Чтобы переместить данные из одной

Шаг 7

5. Совет
Совет

Чтобы переместить данные из одной рабочей книги в другую при помощи мыши, выведите на экран обе эти книги одновременно. Для этого откройте обе книги, выберите из меню Окно команду Расположить, затем в появившемся диалоговом окне —режим рядом, слева направо или сверху вниз, после чего щелкните на кнопке ОК. Теперь можно выделить нужные данные и перетащить их из одной ра -бочей книги в другую. Чтобы скопировать данные этим способом, удерживайте при их перемещении клавишу Ctrl.

Шаг 8

Копировать и

После окончания копирования для отмены

Шаг 9

Вставить стандартной панели инструментов (или команд Копировать и Вставить из меню Правка,). После окончания копирования для отмены выделения исходных данных используйте клавишу Esc. Если для вставки данных применялась клавиша Enter, то скопировать данные из буфера обмена можно только один раз.

в буфер обмена их можно

Внимание!

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

Как округлять числа и укорачивать их запись

При выполнении расчетов результат часто представляет собой множество чисел с длинным "хвостом" цифр после десятичной запятой. Если возникает необходимость полностью уместить такие числа в ячейках таблицы, можно расширить ее столбцы или отбросить один или несколько десятичных разрядов, подобрав соответствующий числовой формат. Эти способы позволяют решить проблему отображения чисел в рабочей таблице при выводе этой таблицы на экран, но при этом числовые значения, сохраняемые в памяти, не меняются. В вычислениях по-прежнему будут участвовать полные числа. Если необходимо частично или полностью исключить десятичные разряды в представлении чисел, нужно воспользоваться функциями ОКРУГЛ и ЦЕЛОЕ, описанными в этом разделе.

Примечание
Примечание

Округление чисел не следует путать с ограничением количества цифр справа от десятичной точки при отображении чисел на экране, которое задается выбором соответствующего формата. Просто с помощью округления выбирается количество требуемых значащих цифр. Например, если число 123,4567 Округляется до двух десятичных знаков, то оно примет вид 123,46; если выбран числовой формат с четырьмя десятичными разрядами, это число примет вид 123,4600; наконец, если в числовом формате позиции для дробной части вообще не предусмотрены, число примет вид 123.

Щелкните на ячейке, которая будет содержать округленное значение, а затем на кнопке

Как пользоваться функциями ПР

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

Примечание
Примечание

Excel может находить не только точное совпадение с заданной величиной, но и ближайшее к ней значение. Например, при назначении надбавок к зарплате с учетом уровня зарплаты может понадобиться составить список, в котором надбавки будут увязаны с зарплатой. В этом случае следует поступить следующим образом. Расположите величины в порядке возрастания в крайнем левом столбце. После этого в текстовом окне Диапазон_просмотр введите слово "истина" . Допустим, в крайний левый столбец внесены суммы зарплат 5000, 3000. и т.д., надбавка 500 относится к зарплате 2500, а надбавка 350 — к зарплате 3000. Если нужно найти надбавку к сумме зарплаты 2730, Excel определит, что эта сумма меньше 3000 и попытается найти ближайшую надбавку среди меньших зарплат. Такой надбавкой окажется 500, соответствующая зарп-лате 2500.

Примечание
Примечание

Функциям ПР доступны также таблицы из других рабочих книг.

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

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

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

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

Совет
Совет

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

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

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

В главе 2 вы ознакомились с несколькими способами суммирования чисел. Во всех случаях — независимо от того, используется ли формула или кнопка автоматического суммирования, — для ускорения процесса вычислений нужно воспользоваться функцией СУММ программы Excel. Эта функция производит сложение всех величин в заданных ячейках. Если же нужно сложить числа, удовлетворяющие определенным критериям, необходимо воспользоваться функцией СУММЕСЛИ, о которой и пойдет здесь речь.

Как рассчитать выплаты по ссуде

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

Примечание
Примечание

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

Введите в рабочую таблицу текущую величину ссуды, величину процентной ставки и срок ссуды (также называемый количеством периодов). Здесь сравниваются 2 ссуды по 1 000 000 каждая, одна из которых выдана под 8,5% годовых на срок 15 лет (180 мес.), а другая — под 7% годовых на срок 30 лет.

Как выполнять арифметические действия с датами

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

Совет
Совет

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

Чтобы выяснить, сколько проработал в компании тот или иной служащий, нужно из текущей даты вычесть дату поступления его на работу. Если в рабочей таблице содержатся даты приема на работу, в нее целесообразно добавить сегодняшнее число. (Самый простой способ введения текущей даты — с помощью комбинации клавиш Ctrl+;).

Вставка функции на панели инструментов.

Шаг 1

Вставка функции на панели инструментов. На экране появится диалоговое окно Мастер функций. Если хотите, можно набрать имена функций ОКРУГЛ и ЦЕЛОЕ вручную, без использования диалогового окна Мастер функций.

Шаг 1

1. Щелкните на ячейке, в которую будет занесен результат сложения, а затем — на кнопке

Шаг 1

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

Шаг 1

1. Щелкните на кнопке

Шаг 1

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

Шаг 1

1. Активизируйте ячейку, в которую будет заноситься трудовой стаж данного служащего.

Если имя нужной функции обнаружено

Шаг 2

1. Если имя нужной функции обнаружено в окне списка Функция, выделите его, щелкнув на нем кнопкой мыши. Если же нужная функция отсутствует, в списке Категория выберите категорию, в состав которой входит искомая функция. Если не удалось найти функцию ОКРУГЛ, следует выбрать категорию Математические, после чего найти функцию ОКРУГЛ в списке Функция.

Шаг 2

Вставка функции на панели инструментов.

Шаг 2

Вставка функции в строке формул. В списке Категория выберите строку Финансовые, затем в списке Выберите функцию выделите функцию ПЛТ и щелкните на кнопке ОК.

Шаг 2

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

Шаг 2

Вставка функции.

Шаг 2

2. Теперь из текущей даты нужно вычесть дату приема на работу. В рассматриваемом примере сначала можно попробовать воспользоваться формулой =$В$2-Е4. Обратите внимание на результат! Получилось такое большое число, потому что формула вычисляет количество дней (а не лет) работы на предприятии.

в поле Число введите округляемое

Шаг 3

2. Щелкните на кнопке ОК. В появившемся диалоговом окне в поле Число введите округляемое число, а в поле Чис-ло_разрядов введите количество десятичных разрядов, до которого будет выполнено округление. В обоих случаях можно вводить либо числа, либо ссылки на ячейки. Если хотите, щелкните на кнопке сворачивания диалогового окна, чтобы вернуться к рабочей таблице и выделить в ней нужные числа.

Шаг 3

2. Если в окне Функция появится функция СУММЕСЛИ, щелкните на ней. В противном случае, в списке Категория выберите строку Математические, после чего найдите функцию СУММЕСЛИ.

Шаг 3

2. В поле Ставка введите ставку 8,5% или воспользуйтесь кнопкой сворачивания диалогового окна

Шаг 3

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

Шаг 3

2. В диалоговом окне Мастер функций в списке Категория выберите Ссылки и массивы, затем в списке Выберите функцию: выберите ВПР и щелкните на кнопке ОК.

Шаг 3

3. Разделив результаты на 365, получим ответ в годах. В нашем случае формула должна иметь вид =($В$2-Е4)/365. Теперь легко заметить, что первый сотрудник проработал на предприятии более 16 лет.

Щелкните на кнопке ОК. Excel

Шаг 4

3. Щелкните на кнопке ОК. Excel поместит результаты в рабочую таблицу. Функция ОКРУГЛ выполнит округление числа до заданного количества десятичных разрядов. Если было задано 0 разрядов, то, как показано на рисунке, округление будет выполнено до ближайшего целого числа. Если задан 1 разряд, будет виден один разряд после десятичной точки.

Шаг 4

3. Щелкните на кнопке ОК. В появившемся диалоговом окне нужно указать, какие ячейки подвергнуть анализу на предмет выполнения заданного условия (поле Диапазон), какие критерии применить к содержимому просматриваемых ячеек (поле Условие), атакже содержимое какой группы ячеек будет просуммировано при выполнении условия (поле Диапазон суммирования).

Шаг 4

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

Шаг 4

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

Шаг 4

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

Шаг 4

4. С помощью маркера заполнения скопируем формулу во все остальные ячейки столбца Е. Чтобы после копирования формула оставалась корректной, ссылка на ячейку, содержащую текущую дату, должна быть абсолютной ($В$2).

Чтобы воспользоваться функцией ЦЕЛОЕ, отображающей

Шаг 5

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

Шаг 5

4. Щелкните на кнопке сворачивания диалогового окна

Шаг 5

3. В поле Кпер введите число периодов — т.е. срок или длительность ссуды. Этот показатель также выражается количеством месяцев. Можно воспользоваться и кнопкой

Шаг 5

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

Шаг 5

, а затем выделить в рабочей таблице ячейку.

Шаг 5

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

в списке используемых функций, выберите

Шаг 6

Вставка функции на панели инструментов. Если функция ЦЕЛОЕ отсутствует в списке используемых функций, выберите из списка Категория опцию Математические, а затем выберите функцию ЦЕЛОЕ. Щелкните на кнопке ОК.

Шаг 6

справа в поле Диапазон. Протащите указатель мыши по группе ячеек, содержимое которых нужно проанализировать. Здесь выделены ячейки, где отражена прибавка к зарплате (в %); из этих величин будут отобраны выплаты, которые нужно сложить для определения планируемой на 2004 г. зарплаты. Затем нажмите клавишу Enter или снова щелкните на кнопке

Шаг 6

, после чего выделить ячейку рабочей таблицы, содержащую количество периодов.

Шаг 6

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

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

Шаг 6

4. В поле Таблица введите диапазон ячеек, в которых содержится справочная таблица. В данном примере таким диапазоном является А4:С9 — сюда входят порядковые номера, описания и цены. (Заметьте, что заголовки столбцов в этот диапазон не включены).

Шаг 6

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

Во втором диалоговом окне Мастера

Шаг 7

6. Во втором диалоговом окне Мастера функций в поле Число введите число или ссылку на ячейку. Чтобы поместить результаты в рабочую таблицу, щелкните на кнопке ОК. Функция ЦЕЛОЕ отбросит все, что находится правее десятичной запятой, сохранив лишь целую часть числа. В некоторых случаях, таких, как этот, результаты применения функций ЦЕЛОЕ и ОКРУГЛ будут различными.

Шаг 7

.

Шаг 7

4. В поле Пс ( величина ссуды) введите текущую величину ссуды или выделите ячейку рабочей таблицы, содержащую величину ссуды. Если кредит должен быть погашен полностью в поле Бс можно ничего не вводить. В поле Тип вводится 0, если выплата делается в конце периода, или 1, если нужно платить в начале периода.

Шаг 7

7.

Шаг 7

5. В поле Номер_столбца введите номер столбца, в котором будет проводиться поиск нужной величины. В данном примере мы ищем цены в столбце 3. Если задать столбец 2, Excel возвратит описание пленки, порядковый номер которой указан в поле Искомое_значение.

Шаг 7

6. Примечание
Примечание

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

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

Шаг 8

5. Введите критерий в поле Условие. В данном случае 4% означают, что Excel примет во внимание только выплаты, которые планируется увеличить на 4%.

Шаг 8

5. Щелкните на кнопке ОК. В рабочей таблице появится результат в виде суммы ежемесячных платежей по закладной. Заметьте, что подобные результаты представляются отрицательными числами, так как относятся к разряду затрат.

Шаг 8

6. Чтобы программа искала точное совпадение с заданной величиной, а не ближайшее к ней, в поле Диапазон_прос-мотра введите слово ложь.

вания диапазон ячеек не задан,

Шаг 9

6. Примечание
Примечание

Если в текстовом окне Диапазон_суммиро- вания диапазон ячеек не задан, то Excel считает, что нужно сложить содержимое ячеек, указанных в поле Диапазон.
Щелкните на кнопке

Шаг 9

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

Шаг 9

7. Щелкните на кнопке ОК. Excel поместит результаты выполнения функции в рабочую таблицу. Таким образом, поиск цены на фотопленку не составил никаких проблем. Хотя в нашем примере функция ПР и таблица данных расположены на одном листе, но с тем же успехом эту функцию можно было бы поместить и в другом месте.

справа поля Диапазон_суммирования. Протащите указатель

Шаг 10

справа поля Диапазон_суммирования. Протащите указатель мыши по группе ячеек, содержимое которых необходимо сложить. В нашем примере выделены ячейки, куда включены планируемые на 2004 г. выплаты; из этих величин будут просуммированы только соответствующие планируемой 4%-й прибавке. Нажмите клавишу Enter или снова щелкните на кнопке

Шаг 10

8.

Шаг 10

8.

Шаг 11

.

Шаг 12

7. Щелкните на кнопке ОК. Диалоговое окно закроется, и вы увидите результаты суммирования — т.е. сумму выплат, в которых предусмотрена 4%-я прибавка. Теперь можно без труда построить другую функцию СУММЕСЛИ, которая произведет сложение планируемых выплат при 7%-й прибавке. Для этого просто повторите пп. 1—7, задав в текстовом окне Условие величину 7%.