Вычислите прибыль

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

1. Выделите ячейку и введите =, затем щелкните на ячейке D13.

3. Введите -, затем щелкните на ячейке D19.

4. Нажмите клавишу Enter, формула введена, и в ячейке D20 появилось значение прибыли за март.

Составление функций с помощью Мастера функций

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

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

Введите текущую дату

В этом упражнении вы введете в ячейку текущую дату с помощью функции СЕГОДНЯ

1. Выделите ячейку С4.

2. Наберите =сегодня().

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

3. Нажмите клавишу Enter. Формула введена, и в ячейке появи­лась текущая дата.

В большинстве формул необходимо ввести не только имена функции но и указать адреса ячеек и/или дополнительные значения, называемые аргументами. Аргументы функции записываются в скоб­ках после имени функции, как в формуле =СУММ(С15: С18). Даже если функ­ция не требует аргументов, как в формуле вычисления текущей даты =СЕГОДНЯ(), скобки все равно необходимы. Некоторые функции, например, многие статистические или финансовые функции, используют несколько ар­гументов. В таких случаях аргументы отделяются друг от друга запятыми.

Определите средние затраты на товары

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

1. Выделите ячейку L10 и наберите Среднее.

2. Выделите ячейку L12, затем в строке формул щелкните на кнопке Вставить функцию . Откро­ется диалоговое окно Мастера функций. Кнопка Вставить функцию также еще располагается на вкладке Формулы в группе Библиотека функций.

3. В списке Категория выделите Статистические. В окне Выберите функцию появится список статисти­ческих функций.

4. Выделите в этом списке строку CP3HАЧ и щелкните на кнопке ОК. Откроется окно Аргументы функции.

5. Щелкните в поле Число1 (если в этом поле уже указан диапазон ячеек, то удалите его если он не правильный).

6. С помощью мыши выделите диапазон ячеек D12: I12. Теперь в поле Число1 по­явились адреса D12: I12.

7. Щелкните на кнопке ОК. В ячейке L12 теперь находится сред­нее значение затрат на товары за март: 19755, 83.

Присвоение имен блокам ячеек

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

Присвоение имен

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

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

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

1. Выделите диапазон ячеек D11: D20. Адрес первой ячейки блока появился в Поле имени.

2. В Строке формул щелкните в Поле имени и наберите Март_2011.

3. Нажмите клавишу Enter. Выделенный диапазон ячеек теперь называется Март_2011.

4. Выделите блок ячеек C10: I20, содержащий все данные вашего бюджета вместе с заголовками.

5. На вкладке Формулы в группе Определенные имена щелкните на кнопке . В появившемся диалоговом окне Создание имен из выделенного диапазона флажки установлены напротив В строке выше и В столбце слева, что означает, что в качестве имен будут взяты заголовки верхней строки и левого столбца.

6. Щелкните на кнопке ОК. Просмотреть список имен блоков мож­но, щелкнув на стрелке «вниз», находящейся рядом с полем Поле имени.

Каждая строка и столбец теперь стали блоками ячеек с именами своих заго­ловков.

Изменение имен

С помощью кнопки можно исправить имя блока ячеек или изме­нить отдельные ячейки этого блока. Предположим, вы хотите изменить блок ячеек Март_2011.

Увеличьте размер блока и измените имя

1. На вкладке Формулы в группе Определенные имена щелкните на кнопке Диспетчер имен. Откроется диалоговое окно Диспетчер имен, где перечислены созданные вами имена блоков.

2. Выделите в этом списке имя Март_2011.

3. В поле Диапазон в записи ='Бюджет 2011'! $D$10: $D$20 вместо $D$11 наберите$D$10. Так вы добавите в блок ячейку D10.

4. Выделите имя Март_2011 и нажмите на кнопке Изменить. Откроется диалоговое окно Изменение имени.

5. В поле Имя в имени Март_2011 удалите фрагмент текста «20».

6. Щелкните на кнопке ОК. Новое имя Март_11 будет добавлено в список имен блоков.

7. Закройте диалоговое окно Диспетчер имен, щелкнув на кнопке Закрыть.

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

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

Найдите максимальное и минимальное значения

В этом упражнении вы найдете максимальное значение затрат на товары с помощью Мастера Функций и затем составите свою формулу для определе­ния минимальной величины затрат по данным бюджета фирмы ЗАПАД.

1. Выделите ячейку M10 и наберите Макс.

2. Выделите ячейку M12 и щелкните на кнопке Вставка функции . Откроется диало­говое окно Мастер Функций.

3. Убедитесь, что в списке Категория выделена стро­ка Статистические. Затем в списке Выберите функцию выделите имя функции МАКС.

4. Щелкните на кнопке ОК. Откроется следующее окно Аргументы функции.

5. Щелкните в поле Число1 и выделите «L12».

6. На вкладке Формулы в группе Определенные имена щелкните на кнопке и в открывшемся списке имен блоков ячеек выделите имя Затраты_на_товары. Это имя появится в поле Число1 диалога Мастер Функций.

8. Щелкните на кнопке ОК. Формула составлена, и результат 20201 появился в ячейке M12.

9. Выделите ячейку Nl 0 и наберите Мин.

10. Выделите ячейку Nl2 и наберите =мин(3атраты_на_товары). Чтобы не набирать имя Затраты_на_товары, можно выделить его в окне списка имен.

11. Нажмите клавишу Enter. Минимальное значение 19316 бу­дет помещено в ячейку N12.

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

Как копировать формулы

Копирование формул в диапазон ячеек осуществляется так же, как и копи­рование данных. Чтобы внести формулу в диапазон, выделите ячейку, со­держащую формулу, и перетащите маркер заполнения вниз, вверх, вправо или влево, на сколько требуется. Формула будет автоматически введена в новую ячейку. Предположим, вам необходимо посчитать ежемесячную пол­ную выручку

1. Выделите ячейку D20.

2. Перетащите маркер заполнения ячейки D20 к ячейке J20.

Формула вычисления полной выручки заполнит ячейки D20: I20.

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

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

Скопируйте данные первого квартала бюджета на другой лист

1. Выделите блок ячеек А10: F20.

2. Скопируйте в буфер.

3. Щелкните на ярлычке 2011 1 квартал.

4. Щелкните правой кнопкой мыши на ячейке А10. В появившемся контекстном меню выберите команду Специальная вставка. Откроется диалог Специальная вставка.

5. В группе Вставить выберите опцию Значения и щелк­ните на кнопке ОК. Диалоговое окно закроется, и в блок ячеек бу­дут скопированы только численные значения.

Как перемещать данные между ячейками

Чтобы переместить содержимое ячейки с помощью мыши, достаточно выделить ячейку и перетащить ее рамку на новое место. Переместите самостоятельно данные ячеек C20: J20 на одну строку вниз (C21: J21) на листе Бюджет 2011.

Составление формул с относительными и абсолютными адресами

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

Запись абсолютных адресов содержит знаки доллара (например, $А$1), а в записи относительных адресов знак доллара отсутствует (например, А1). Таким образом, до сих пор вы использовали в формулах только относитель­ные адреса. Выбор типа адреса зависит от того, что вы хотите вычислить, причем в одной формуле могут быть использованы и абсолютные, и относи­тельные адреса

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

Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки «7». Запись D$7 означает, что при перемещении будет изменяться только символ столбца «D». Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится и в расчетах будут участвовать данные из ячейки D7.

Используйте смешанные адреса

1. Выделите ячейку D7 и после числа 1, 50 введите %.

2. Выделите ячейку Е11, наберите =D11+(D11*$D$7) и нажмите клавишу Enter. В ячейке Е11 появилось точное значение прихо­да с учетом ежемесячного роста объема продаж (1, 50%).

3. Скопируйте формулу ячейки Е11 в диапазон ячеек F11: I11. ре­зультат формулы во всех ячейках будет зависеть от содержимого ячейки $D$7. 1

Скопируйте формулу и измените относительные адреса на абсолютные

1. Выделите ячейку J19. В этой ячейке находится формула с отно­сительными адресами.

2. Скопируйте ячейку J19 в ячейку J20. Теперь в ячейке J20 сум­мирование происходит в диапазоне D20: I20, в котором нет данных, и в результате получается 0.

3. Выделите ячейку J19.

4. В строке формул выделите «D19: I19».

5. Нажмите клавишу F4, а затем клавишу Enter. Теперь отно­сительные адреса заменены на абсолютные.

6. Скопируйте ячейку J19 в ячейку J20. Формула скопирована в новую ячейку и в ячейке J20 находится число 33360, так как отно­сительные адреса были предварительно заменены на абсолютные.