Именование отдельных диапазонов ячеек
Часто приходится иметь дело с постоянными величинами. Для ввода их адреса в формулу нужно воспользоваться абсолютным адресом. В Microsoft Excel для таких случаев предусмотрено именование ячеек. Имена ячеек должны состоять из не разрывного и желательно краткого слова, иначе формула функции может не войти в ячейку.
Можно присвоить имя непосредственно ячейке, для чего сначала надо курсором отметить (выделить) эту ячейку, затем нужно
· либо выполнить команду «Формулы» → «Присвоить имя» группы «Определенные имена». Появится диалоговое окно «Создание имени» (рис. 1), которое имеет четыре поля: 1) Имя - вводится присваиваемое ячейке имя; 2) Область – указывается область действия имени: глобальный уровень на всю книгу или локальный уровень на определенный лист книги. Лучше всего пользоваться глобальным уровнем - книга; 3) Примечание – различные примечания, которые не влияют на результаты вычисления (заполнение не обязательно); 4) Диапазон – указывается абсолютный адрес ячейки, который начинается с названия листа на котором находится именуемая ячейка и завершается после восклицательного знака непосредственно абсолютным адресом ячейки (обычно указывается самой программой). После заполнения полей диалогового окна «Создание имени» нужно нажать клавишу ОК или клавишу ввода Enter.
· либо щелкнуть по полю «Имя» в строке формул и вписать присваиваемое имя и нажать клавишу ввода Enter.
Если возникает необходимость присвоить имя множествам ячеек (диапазону ячеек), то надо выделить этот диапазон включая заголовки таблицы и выполнить команды меню вкладыш «Формула» - «Создать из выделенного» из группы «Определенные имена». В появившемся диалоговом окне «Создание имен из выделенного диапазона» (рис. 2) указать какой из заголовков взять за имена ячеек, если именовать по строке (по горизонтали), то нужно щелкнуть мышью по окошку «в столбце слева»(рис. 2)или «в столбце справа», в зависимости от того как записан заголовок слева или справа. Если именовать по столбцу (по вертикали), то нужно щелкнуть мышью по окошку «в строке выше» или «в строке ниже», в зависимости от того как записан заголовок - в верхней строке или нижней.
В именах не разрешается использование резервированных слов, т.е. применяемых в Excel слов.
Вставка имен в формулы.
После именовании ячеек вместо адреса ячейки можно пользоваться именем ячейки. В таких случаях, например на рис 3, формула в строке формул будет выглядеть, например таким образом, =А1*апр (число в ячейке А1 умножить на значение ячейки В5, названной «апр»). Для того, чтобы ввести имя ячейки в формулу, нужно
· либо выполнить команды «Формулы» - «Использовать в формуле» и из появившегося списка указать нужное или щелкнуть по «Вставить имена…» и из списка диалогового окна «Вставка имени» указать нужное имя и нажать кнопку «ОК» этого диалогового окна;
· либо щелкнуть по именованной ячейке, содержащей нужное значение, и новый адрес запишется в формулу.
11. СОДЕРЖАНИЕ ЗАНЯТИЯ:
САМОСТОЯТЕЛЬНАЯ РАБОТА.
1. Получить задание у преподавателя и открыть соответствующий файл с заданием.
2. Открыть лист 5 с таблицей Измерение максимального артериального давления. Считать, что нормальное максимальное артериальное давление 120, низший допустимый предел (НДопПред) этого давления -110, а высший допустимый предел (ВДопПред) -128. Значение давления выше 130 мм. рт. столба считать очень опасным для жизни (ОчОпЖизни).
8. В целях именования ячеек В14 и В15 выделить блок А14:В15 и именовать их вызвав команды «Формулы» – «Создать из выделенного».
9. Именовать текстом содержащимся в самой ячейке поочередно ячейки А28, А29, А30, А31, А32, А33, А34 и А35 вызвав команды «Формулы» - «Присвоить имя»
10. В ячейки В26, В27, В28, В29, В30, В31, В32, В34 , В35 поочередно написать формулу счета, используя статистическую функцию СЧЕТЕСЛИ(диапазон, условие), где диапазон В16:В25, а условие - слово в ячейке слева, которые вводятся как имя ячейки по команде «Формулы» - «Использовать в формуле», а выражения «ИСТИНА» для В26 и «ЛОЖЬ» для В27 вписать с клавиатуры, так как эти выражения являются резервированными словами, поэтому нельзя ими именовать ячейки.
11. Затем выделив блок В26:В35, скопировать в горизонтальном направлении до столбца F.
12. Активизировть ячейку В16, открыть список логических функций , поставить знак равенства и ввести формулу:
А) с логической функцией НЕ(В4=120) аргументом которого является В4=120, где В4 адрес ячейки, который вводится щелчком мыши по этой ячейке. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
Б) с логической функцией И(логическое1;логическое2;…), за первый аргумент принять В4>НДопПред, за второй аргумент В4<ВДопПред, где НДопПред и ВДопПред имена ячеек В14 и В15 и вводятся они командой «Формулы» - «Использовать в формуле» и выбром из списка нужное имя. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
В) с логической функцией ИЛИ(логическое1;логическое2;…), за первый аргумент принять В4<НДопПред, за второй аргумент В4>ВДопПред, где НДопПред и ВДопПред имена ячеек В14 и В15 и вводятся они командой «Формулы» - «Использовать в формуле» и выбром из списка нужное имя. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
Г) с логической функцией ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь),где первый аргумент В4=120, второй аргумент «Норма», третий аргумент «Изменен». Эти аргументы вводятся выполнением команды «Формулы» - «Использовать в формуле» и выбором из списка нужное имя. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
Д) с логической функцией ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь),где первый аргумент является функцией И(В4>НДопПред; В4<ВДопПред), второй аргумент «Норма», третий аргумент «Опасно». Эти аргументы вводятся выполнением команды «Формулы» - «Использовать в формуле» и выбором из списка нужное имя. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
Е) с логической функцией ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь),где первый аргумент В4>130, второй аргумент «ОчОпЖизни», третий аргумент «Допустимо». Эти аргументы вводятся выполнением команды «Формулы» - «Использовать в формуле» и выбором из списка нужное имя. Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
Ж) с логической функцией ЕСЛИ
=ЕСЛИ(B4>120;Увеличен; ЕСЛИ(B4=120;Норма;Уменьшен)).
В формулу вводить
- В4-щелчком мыши по ячейке В4,
Увеличен, Норма, Уменьшен путем вставки имен ячеек, т.е. выполнив команды «Формулы» - «Использовать в формуле» и выбором из списка нужное имя.
Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
З) с логической функцией ЕСЛИ
=ЕСЛИ(И(B4<ВДопПред;B4>НДопПред);Нормально;ЕСЛИ(B4>ВДопПред;Увеличен;Уменьшен)).
В формулу вводить
- В4-щелчком мыши по ячейке В4,
- ВдопПред, НдопПред, Нормально, ВдопПред, Увеличен, Уменьшен путем вставки имен ячеек, т.е. выполнив команды «Формулы» - «Использовать в формуле» и выбором из списка нужное имя.
Затем скопировать это выражение по вертикали в диапазоне В16:В25. Выделить этот диапазон и скопировать по горизонтали. Обратите внимание на заполнение блока В16:F25 и В26:F35. Объяснение написать в рабочую тетрадь. Не забыть написать в тетрадь и введенные формулы, указывая куда и зачем. Выделить блок В16:F25 и очистить его.
ФОРМА ОТЧЕТА К ЗАДАНИЯМ
В ячейку В16 введена логическая функция ... и скопирована в диапозоне В16:F25. В ячейках диапазона В16:F25 получен результат вычисления «...» для значений ... диапазона В4:В13, «...» для значений ... диапазона В4:В13, «...» для значений ... диапазона В4:В13. Поэтому по каждому столбцу эти результаты встречаются
| Результат | B | C | D | E | F |
раз.
10. Сохранить электронную таблицу.
11. Отчет по работе и результат работы, сохраненное в виде электронной таблицы показать преподавателю
11.2. Работа с преподавателем:
- При появлении затруднений или при ошибочных действиях обратиться к преподавателю для исправления ошибок.
- К концу занятия показать преподавателю отчет о выполненной работе и получить зачет по данной работе.
11.3. Контроль исходного уровня и заключительного уровня знаний:
- Тестирование на компьютере.
- Беседа преподавателя со студентом по вопросам самоподготовки.