Относительная, абсолютная и смешанная адресация в Microsoft Excel
В приложении Microsoft Excel все ориентировано на работу с ячейками. Как только выстраивается взаимосвязь между числовыми характеристиками в виде формул, происходит обращение к ячейкам. Иначе это называется – адресацией.
Существует три вида указания ссылки на ячейку: относительная, абсолютная и смешанная.
Относительная адресация
Она используется по умолчанию. Результатом такого обращения является то, что при копировании формулы, в которой задействованы адреса, из одной ячейки в другую, параметры будут изменяться. Запись адреса осуществляется путем прямого указания на название столбца и номер строки – например, A1. В формулах такие адреса вводятся щелчком мыши по нужным ячейкам, или выделением диапазона ячеек при суммировании, например, нахождения максимального, минимального или среднего значений нескольких чисел и т.д.
Если копирование осуществляется по вертикали вверх или вниз, то параметр, отвечающий за номер строки, уменьшается или увеличивается соответственно на единицу.
Если сдвиг формулы происходит по горизонтали вправо или влево, то параметр с номером столбца так же будет изменяться на единицу в большую или меньшую сторону.
И в том, и другом случае копирование осуществляется путем использования маркера заполнения, либо путем использования сочетания клавиш для копирования содержимого ячейки в буфер обмена (Ctrl-C, Ctrl-X, Ctrl-V), либо вызов команды «Копировать» в контекстном меню.
Например, стоит задача выполнить какое-либо арифметическое действие между двумя столбцами B и C. Результирующий столбец D будет содержать формулы, в которых закреплены адреса столбцов, но будут изменяться адреса строк.
Абсолютная адресация
В ситуации, когда при копировании формулы адрес не должен изменяться, используется абсолютная адресация. Тогда обращение к ячейке записывается с использованием символа «$» перед неизменяемым именем столбца или номером строки, в зависимости от задач.
При такой записи при копировании формулы один из параметров будет иметь неизменное значение.
Например, есть перечень сотрудников с категориями и ставками по окладам. Определенным категориям назначена премия, которая рассчитывается как процент от оклада. При копировании формулы в столбце с результатом суммы по премиям нужно указать абсолютную адресацию на ячейку с процентом.
В примере представлена абсолютная ссылка на ячейку – «$C$10» и «$C$11».
Для указания абсолютной ссылки на столбец символ «$» записывается только перед столбцом, а именно $C10. Аналогично для абсолютной ссылки на строку – только перед номером строки - C$10.
Есть возможность преобразования адреса из относительного в абсолютный. При вводе адреса (например, когда щелкаем мышью по нужной ячейке) нажать функциональную клавишу F4. Система автоматически добавит символы $ перед именем столбца и номером строки.
Смешанная адресация
Не все выделяют такую адресацию в отдельный тип. Суть в том, что если в абсолютном адресе на ячейку закреплен либо столбец, либо строка, то такая ситуация относится к смешанному виду, например: $C10 – используется для указания адреса с неизменяемым именем столбца, для указания адреса с неизменяемым номером строки используется C$10.
Для получения смешанной адресации ячейки после ввода относительного адреса (щелчком мыши по нужной ячейке) нажать функциональную клавишу F4 два или три раза до получения нужного вида смешанного адреса.
Проверка данных в MS Excel
При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В MS Excel существует инструмент для проверки введенных данных сразу после нажатия клавиши Enter – Проверка данных, установить которую можно на вкладке Данные в группе Работа с данными пиктограмма Проверка данных.
В диалоговом окне Проверка вводимых значений условия проверки вводимого значения можно настроить очень гибко.
Можно разрешить ввод значений в ячейку только определенного типа, выбрав необходимое условие из выпадающего (раскрывающегося) списка. Чаще всего проверка используется в случаях:
· Целое число. В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
· Действительное. В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
· Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999.
· Список. В этом случае ввод значений в ячейку можно ограничить ранее определенным списком. Например, если в качестве источника указать через точку с запятой единицы измерения товара шт;кг;кв.м;куб.м, то ничего другого, кроме этих 4-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список.
На вкладке Сообщение для ввода можно установить текст подсказки для вводимых данных:
В этом случае при переходе в ячейку, для которой установлена проверка данных, будет появляться эта подсказка:
После ввода ошибочного значения Проверка данных может отобразить сообщение, которое пользователь установит для предотвращения ошибки.
Кнопка Очистить все позволяет сбросить проверку данных для выделенного диапазона.
Логическая функция ЕСЛИ
Функция ЕСЛИ используется при проверке условий для значений и формул ивозвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если заданное условие имеет значение ЛОЖЬ. Синтаксис функции ЕСЛИ:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
лог_выражение— любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Для записи логического выражения используются операторы сравнения:
= (знак равенства) – равно;
> (знак больше)– больше;
< (знак меньше) – меньше;
>= (знак больше или равно)- больше или равно;
<= (знак меньше или равно)- меньше или равно;
<> (знак не равно)-не равно.
значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Аргумент «значение_если_истина» может быть формулой.
значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ.Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней — закрывающая скобка), то возвращается значение 0 (ноль). Аргумент «значение_если_ложь» может быть формулой.
После вычисления аргументов «значение_если_истина» и «значение_если_ложь», функция ЕСЛИ возвращает полученное значение.
В качестве значений аргументов «значение_если_истина» и «значение_если_ложь» можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ. Но, как правило, при такой степени вложенности целесообразно пересмотреть алгоритм решения задачи с целью уменьшения уровней вложенности функции ЕСЛИ. Кроме того, чтобы проверить больше 64 условий, используются функции категории Ссылки и массивы (в частности функции ПРОСМОТР, ВПР или ГПР).
Функция категории Ссылки и массивы ПРОСМОТР
Функция ПРОСМОТР возвращает значение из строки, столбца или массива, имеет две синтаксических формы: векторную и форму массива. Векторная форма используется для просмотра значений в большом списке или значений, которые могут изменяться. В этом случае осуществляется просмотр диапазона, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возврат значения из той же позиции второго диапазона.
Форма массива используется для просмотра небольшого списка значений, которые остаются постоянными. При этом производится просмотр первой строки или первого столбца массива, нахождение указанного значения и возврат значения из аналогичной позиции последней строки или столбца массива.
Для правильной работы функции ПРОСМОТР просматриваемые данные должны быть отсортированы в возрастающем порядке. Если это невозможно, рекомендуется использовать функции ВПР.
Синтаксис функции ПРОСМОТР в векторной форме:
ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])
Искомое_значение - значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
Просматриваемый_вектор -диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями и должны быть расположены в порядке возрастания.
Вектор_результатов - диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.
Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, функция ПРОСМОТР возвращает значение ошибки #Н/Д.