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

Применение имен в формулах

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

1) в меню Вставка выбрать команду Имя - Присвоить. Откроется диалоговое окно Присвоить имя, где в окне списка Имя перечислены созданные имена блоков;

2) выделить в этом списке нужное имя;

3) исправить имя, если это необходимо;

4) в поле Формула изменить адреса, добавив или удалив ячейки;

5) щелкнуть на кнопке Добавить; новое имя будет добавлено в список, однако старое имя останется в списке. Чтобы изменить имя бло­ка ячеек, надо добавить в список новое и удалить старое имя.

6) для удаления имени выделить его в списке и щелкнуть на кнопке Удалить;

7) закрыть диалоговое окно.

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

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

Другой способ перемещения по рабочему листу - использование клавиши F5:

1) нажать клавишу F5. Откроется диалоговое окно Переход, содер­жащее список всех имен ячеек в рабочей книге;

2) В окне списка Перейти выделить нужное имя и щелкнуть по кнопке ОК.

Автоматическое присвоение имен по строкам и столбцам

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

1. Выделите диапазон ячеек, которым хотите присвоить имена (в нашем случае В3:G10);

2. Выберите команду Вставка/Имя/Создать, в результате чего раскроется диалоговое окно Создать имена. Программа сама попытается определить, на основе текста каких ячеек следует создать имена (в данном примере в области По тексту установлены флажки в строке выше и в столбце слева, рис. 4);

3. Если вы согласны с выбором Excel, щелкните на кнопке ОК – имена будут созданы.

И мена и пересечение диапазонов

Если нужно отобразить значение ячейки, находящейся на пересечении диапазонов, достаточно ввести в любую свободную ячейку формулу. Предположим, нам нужно узнать, какая оценка Овдотенко по физкультуре, для этого в свободную ячейку вводим следующую формулу (рис. 4): = Овдотенко Физкультура

 

Применение имен в формулах

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

Например, из формулы:

=СРЗНАЧ(Иванов) или =МАКС(Химия)

можно почерпнуть гораздо больше информации, чем из безликой формулы

=СРЗНАЧ(С4:G4) или =МАКС(C4:C10)

выполняются те же самые вычисления (рис. 4).

 

Рис. 4. Присвоение имен по строкам и столбцам

 

Для вставки в формулу имени ячейки или диапазона используется команда Вставка/Имя/Вставить. Если выбрать данную команду (или нажать клавишу <F3>), раскроется диалоговое окно Вставка имени. Выберите имя в списке и щелкните на кнопке ОК, чтобы вставить его в формулу.

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

Завершение работы

Для окончания работы с Microsoft Excel надо в меню Файл выбрать команду Выход. Если на панели появится диалоговое окно с за­просом о сохранении изменений, щелкнуть в нем по кнопкам ДА или НЕТ.

Задание к лабораторной работе №3

1. Откройте рабочую книгу Ведомость_2. Введите изменения: ФИО и названия предметов, перекопируйте как указано на рис. 4.

2. По таблице, изображенной на рис. 4, строкам нужно присвоить имена Иванов, Петров, Сидоров, Харламов и т.д., а столбцам – Химия, Математика, Биология и т. д., используя команду Вставка/Имя/Создать.

3. Используя имена ячеек, определить какая оценка была у Харламова по математике, Сидорова по русскому языку, Иванова по химии, Кузьмина по физкультуре, Овдотенко по биологии, Краморова по химии.

4. Присвойте имя «Оценки» диапазону ячеек С15:С20, используя команду Вставка/Имя/Присвоить.

5. Используя имена ячеек определите среднее значение оценок для каждого студента, округлив при этом, до одного знака после запятой. Используйте формулу: =ОКРУГЛ(СРЗНАЧ(Иванов);1), а затем скопируйте формулу для остальных студентов, изменяя при этом фамилии. Присвойте имя «Среднее_значение» диапазону ячеек Н4:Н10, используя команду Вставка/Имя/Присвоить.

6. Используя имена ячеек, определите максимальное, минимальное значения по предметам.

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

8. Сохраните как Имена_1.

 

Лабораторная работа №4

Копирование данных

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

Абсолютными адресами

Адреса, которые автоматически изменяются прикопировании их в новые ячейки, называются относительными. Однако в формулах мож­но использовать адреса, которые всегда относятся кодним и тем же ячейкам, независимо от того, где находится формула. Такие адреса на­зываются абсолютными. Знак $ указывает на абсолютный адрес. На­пример, формула в клетке С1 использует относительные адреса клеток А1, В1 и абсолютный адрес клетки А4. При копировании формулы из клетки С1 в блок клеток С2:СЗ, адрес клетки А4 не будет меняться и в клетке С2 получим формулу =(А2+В2)*$А$4, а в клетке СЗ=(АЗ+ВЗ)*$А$4.

Изменять типы адресов можно, нажимая клавишу F4. Можно ус­тановить абсолютный адрес строки и столбца ($А$4), или только стро­ки (А$4), или только столбца (SA4), или вернуться к старым адресам. При перемещении, а не копировании формулы адреса будут указывать на прежние ячейки, независимо от того, являются они абсолютными или относительными.

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

 

Задание к лабораторной работе №4

 

1. Откройте рабочую книгу Имена_1.

2. Внесите изменения в данные:

в клетку В12 внесите заголовок Средний балл по предметам;

в клетку С12 - формулу, вычисляющую среднее значение клеток С4:С10;

скопируйте эту формулу в блок клеток D12:G12; таким образом вы найдете среднюю оценку по каждому предмету. Обратите внимание, как изменились адреса клеток в формулах.

3. Переместите формулы из столбца Н в столбец I. Изменились ли ад­реса ячеек в формулах? Почему? Отмените изменения.

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

в клетку А12 введите текст Минимальный размер стипендии, а в ячейку G12 - значение, соответствующее минимуму;

в клетку IЗ введите заголовок столбца - текст Размер стипендии;

в клетку I4 введите формулу для начисления стипендии первому студенту. Для этого воспользуйтесь функцией =ECJIИ(H4>=4;$G$12;0), то е., если содержимое клетки H4>=4 (а это значение среднего балла первого студента), то в клетку I4 занесется значение клетки G12, в про­тивном случае в клетке I4 появится ноль. Для ввода этой формулы можно воспользоваться Мастером функций;

скопируйте содержимое клетки I4 в блок клеток I5:I10. Так как при копировании формулы адрес клетки G12 не должен меняться, то в формуле для нее используется абсолютный адрес.

5. Начислите студентам, средний балл которых выше 4,5 премию в раз­мере 60% минимальной стипендии. Результаты поместите в столбец J (заголовок - в клетку J3, расчетные формулы - в клетки J4:J10).

6. В столбец K введите формулы, вычисляющие итоговую сумму стипен­дии для каждого студента.

7. Используя имена ячеек, в диапазон ячеек L4:L10 начислите стипендию студентам, используя формулу: =ЕСЛИ(СРЗНАЧ(Иванов)>=4;5000;”Стипендии нет”)

8. В ячейку H12 введите 60%, присвойте имя ячейки «Повышенная_ стипендия».

10. Выполните задание пункта 5, используя имена ячеек. Результат поместите в диапазон ячеек M4:М10. Используйте формулу:

=ЕСЛИ(СРЗНАЧ(Иванов)>=4,5;G$12*Повышенная_стипендия;”Нет стипендии”)

11. В ячейку В22 внесите заголовок «Сред_бал_выб». В ячейку С22 средний бал диапазона ячеек С15:С20, используя имя диапазона ячеек «Оценки», округлите результат до двух цифр после запятой.

12. Скопируйте порядковые номера и Ф.И.О. студентов в блок А18:В25, а в блок клеток С18:С25 итоговую сумму стипендии (использовать специ­альную вставку и опцию вставить ссылку).

13. Сохраните рабочую книгу под именем Начисл_Стип.

 

Лабораторная работа №5