ексель лр 5. Excel ЛР5 Практика. Методические указания по выполнению л абораторной работы 5 относительная и абсолютная адресация. Функция если

• Название: Методические указания по выполнению л абораторной работы 5 относительная и абсолютная адресация. Функция если
• Анкор: ексель лр 5
• Дата: 17.01.2022
• Размер: 239.15 Kb.
• Имя файла: Excel ЛР5 Практика.docx
• Тип: Методические указания; #334023

MSExcel. Методические указания по выполнению лабораторной работы №5

5. ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ. ФУНКЦИЯ ЕСЛИ

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

      1. Различные типы ссылок. Относительная, абсолютная и смешанная адресация

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

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

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

Относительная ссылка записывается в обычной форме, например F3, Е7. Во всех ячейках, куда она будет помещена после копирования, изменяется и буква столбца, и номер строки.

Абсолютная ссылка используется в формуле в том случае, если при ее копировании не должны изменяться обе части: буква столбца и номер строки.

В абсолютной ссылке перед буквой столбца и перед номером строки ставится символ $, например $F$3, $Е$7. При копировании во все ячейки формула будет помещена в неизменном виде.

ПРИМЕЧАНИЕ: Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки.

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

Пример:

смешанные ссылки с неизменяемой буквой столбца: $С8, $F12;

смешанные ссылки с неизменяемым номером строки: А$5, F$9.

• 
  Абсолютная ссылка — это ссылка, не изменяющаяся при копировании формулы.
  
• 
  Относительная ссылка — это ссылка, автоматически изменяющаяся при копировании формулы.
  
• 
  Смешанная ссылка — это ссылка, частично изменяющаяся при копировании.
  

ПРИМЕЧАНИЕ: Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак $ перед буквой столбца и адресом строки, например $E$3. Более быстрый способ — выделить относительную ссылку и нажать клавишу F4, при этом Excel сам проставит знаки $. Повторные нажатия на F4 позволяют переходить от одного типа ссылок к другим. Например, ссылка на E3 будет циклично изменяться на $E$3, E$3, $E3, E3 и так далее.
Пример. Известны цены товаров в долларах, нужно пересчитать их в рубли. Стоимость одного доллара меняется, и поэтому вынесена в отдельную ячейку А2. Нужно, чтобы при изменении курса доллара в ячейке А2, цены в рублях пересчитывались автоматически.

Исходная таблица	Результат

В ячейке C5 нужно написать формулу =B5*A2. При копировании этой формулы вниз она примет вид: B6*A3 и результат будет =0, так как в ячейке А3 ничего нет. То есть нам нужно, чтобы при тиражировании, в формуле адрес ячейки B5 менялся, а адрес ячейки A2 не менялся. Формула в ячейке C5 должна иметь вид =B5*$A$2 или =B5*A$2.

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

2. Логические функции

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

К категории Логические относятся следующие функции (Рис. 6 .1):



Рис. 6.1. Логические функции

1. Функция ЕСЛИ

Функция ЕСЛИ() используется для проверки выполнения некоторого условия и имеет следующий формат:

ЕСЛИ (условие; формула_если_истина; формула_если_ложь) или короче:

ЕСЛИ (условие; формула 1; формула 2)

Вычисления по Формуле 1 происходят тогда, когда условие выполняется, т.е. логическое выражение принимает значение ИСТИНА.

Вычисления по Формуле 2 происходят тогда, когда условие не выполняется, т.е. логическое выражение принимает значение ЛОЖЬ.

Результат работы функции возвращается в ячейку с формулой.

Схематически работа функции ЕСЛИ() представлена на Рис. 6 .2.



Рис. 6.2. Схематическое представление работы функции ЕСЛИ()

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

Ниже представлен список операторов сравнения Excel:

= Равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Пример: Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

=ЕСЛИ(А1>3;10;20)

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;"Зачет сдал"; "Зачет не сдал")

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

Например:

=ЕСЛИ(СУММ(А1:А3)=30;А10;"")

Аргумент Логическое выражение функции ЕСЛИ может содержать текстовое значение. Например:

=ЕСЛИ(А1="Динамо";10;290)

Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

При вставке функции ЕСЛИ при помощи мастера, появляется следующее окно:



В поле «логическое выражение» нужно записать условие, которое проверяется. Оно может содержать адреса ячеек, константы, знаки арифметических операций (+, –, *, /) и обязательно знак сравнения (>,<,>=,<=,=,<>). Если константа содержит текст, то она берется в двойные кавычки (“).

В полях «значение» записывается выражение. Оно также может содержать адреса ячеек, константы, знаки арифметических операций (+, –, *, /). Если константа содержит текст, то она берется в двойные кавычки (“).

Для выбора одного значения из двух вариантов в окне функции ЕСЛИ достаточно заполнить в поле Лог_выражение и в поля Значение_если_истина и Значение если ложь вписать альтернативные варианты.

2. Функции И, ИЛИ, НЕ

Функции И, ИЛИ, НЕ - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

=И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Пример. Пусть Excel возвращает текст "Прошел", если ученик имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид:

=ЕСЛИ(И(А2>4;А3<3);"Прошел"; "Не прошел")

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

Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.

3. Вложенные функции ЕСЛИ

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

=ЕСЛИ(А1=100; "Всегда"; ЕСЛИ(И(А1>=80;А1<100); "Обычно"; ЕСЛИ(И(А1>=60;А1<80); "Иногда"; "Никогда")))

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

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

Например, =ЕСЛИ(B2>C2;"Авангард";ЕСЛИ(B2
Пример. Известны результаты трех игр между двумя командами. Для каждой игры определить, кто победил или была ничья. При решении этой задачи нужно научиться пользоваться мастером функций. Для этого выполните действия:

• 
  Создайте таблицу
  

• 
  Установите курсор в ячейку D2;
  
• 
  Вызовите функцию ЕСЛИ. Появится окно Мастера функций.
  
• 
  Заполните первое и второе поля, как показано на рисунке:
  

• 
  Затем установите курсор в третье поле и вызовите функцию ЕСЛИ еще раз. Обычным способом это сделать не удастся. Воспользуйтесь для вставки полем отмеченным стрелкой. Появится новое пустое окно функции ЕСЛИ.
  
• 
  Заполните его как показано на рисунке ниже и нажмите ОК.
  

• 
  Протащите формулу за маркер автозаполнения. Результат: