Использование функций для создания объемных формул

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

=СУММ(Лист2:Лист13!В5) складывает все значения, содержащиеся в ячейках В5 на всех листах от Лист2 до Лист 13. Чтобы построить эту объемную формулу, выполните описанные ниже действия.

1. В ячейку В5 листа Лист1 введите =СУММ(.

2. Щелкните на ярлычке Лист2 и затем с помощью кноп­ки прокрутки к следующему листу (расположенной слева от ярлычков листов) выведите на экран ярлычок Лист13.

3. При нажатой клавише Shift щелкните на ярлычке Лист13.

4. Выделите ячейку В5, при нажатой клавише Shift, затем введите «)» и нажмите кла­вишу Enter.

Обратите внимание: после щелчка на ярлычке Лист 13 при нажатой клавише Shift все ярлычки от Лист 2 до Лист13 стали белыми, показывая тем самым, что они выде­лены для включения в создаваемую ссылку.

 

Присвоение имен ячейкам и диапазонам

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

Имена, определенные в текущем листе, могут использо­ваться в любых других листах книги. Имя, определяющее диапазон ячеек, например в листе Лист 6 может использо­ваться в формулах, расположенных в листах Лист1, Лист 2 и т. д. этой же книги. В результате каждая книга содержит свое собственное множество имен. Вы можете также опреде­лить специальные имена "на уровне листа", которые будут доступны только на том листе, где они определены.

Рис.4.6.

Использование имен в формулах

Имя ячейки или диапазона можно использовать в форму­ле. Результат будет таким же, как при вводе ссылки на ячейку или диапазон. Например, предположим, что в ячейку A3 введена формула =А1+А2. Если определено имя Вася для ячейки А1 и Маша для А2, то формула =Вася+Маша возвратит то же самое значение, что и предыдущая формула.

 

Определение имен в поле имени

Самый простой способ определения имени - использова­ниеполя имени в строке формул.

Дня присвоения имени ячейке необходимо:

1. Выделить нужную ячейку.

2. Щёлкнуть на поле имени в строке формул (для ячейки А1 стандартным именем является А1, которое выводится слева от поля формул).

3. Ввести имя и нажать клавишу Enter.

 

 

Рис.4.7.

 

Если выделенной ячейке или диапазону было дано имя, то в поле имени выводится именно оно, а не ссылка на ячей­ку. Обратите внимание, что в имени нельзя использовать пробелы!!!

Если имя определено для диапазона ячеек, оно появится в поле имени только после выделении всего диапазона.

В определение имени обязательно включается имя листа и используются абсолютные ссылки на ячейки. Например, когда вы определяете имя для ячейки С5 листа Лист1, то фактическое определение имени сохраняется как

Лист1$С$5.

 

Переход к именованной ячейке или диапазону

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

 

Применение имен к формулам

Чтобы заменить ссылки в формулах на соответствующие имена, выберите на вкладке Формулы в группе Определенные имена раздел Диспетчер имен команду Присвоить имя ↓ и затем – Применить имена. После выбора этой команды Excel находит в формулах все ссылки на ячейки и диапазоны, для которых были определены имена. Если перед выполнением команды Применить имена была выделена единственная ячейка, Excel применяет имена ко всему активному листу, а при выделении диапазона ячеек - только к выделенным ячейкам.

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

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

 

Рис.4.8. Если вы снимете этот флажок, Excel заменит абсолютные, относительные и смешанные ссылки только на те имена, в определении которых используется соответст­вующий тип ссылок.

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

 

 

Практические задания:

1. Присвоить ячейкам А1 и В1 имена X и Y соответст­венно.

 

а) в А5 введите формулу:

 

б) в В5 введите формулу:

 

в) в С5 введите формулу:

 

г) введите в ячейки А1 и В1 числа 10 и 25 соответствен­но.

д) округлите используя соответствующую функцию до 3-х знаков после запятой все полученные результаты.

2. Имеется выручка от реализации продукции на сумму 45671 рубль. Рассчитать налог на пользование автодорог 3.5% в том числе федеральный 2% и внутримосковский 1.5%, а также подоходный налог 13%. Посчитайте сумму каждого налога в отдельной ячейке. Произведите округле­ние до копеек. В отдельной ячейке высчитать сумму всех налогов.

3. В ячейке D10 написать любое предложение путём объ­единения текстовых значений (т.е. в любые ячейки (не менее 5)) ввести текстовые значения, связанные между со­бой по смыслу и объединить их с помощью нужной функции).

4. Задать произвольный числовой квадратный диапазон 5*5. Высчитать сумму всех значений по диагоналям. Ре­зультат ввести в ячейку А10.

5. Посчитать в отдельной ячейке сумму всех элементов, введённого ранее диапазона, используя кнопку Автосум­мы.

 

 

ЛАБОРАТОРНАЯ РАБОТА №5