Абсолютная и относительная адресация
В Excel имеется три способа адресации ячеек (ссылки на ячейки): абсолютная, относительная и смешанная.
Ссылка - адрес объекта (ячейки, блока ячеек), используемый при записи формулы.
1. Относительные ссылки
Относительная ссылка - ссылка, автоматически изменяющаяся при вводе формулы.
По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные.
Это означает, что адреса ячеек при копировании формул из одной ячейки в другую изменяются автоматически, то есть меняются относительно исходной формулы (сохраняются взаимосвязи между ячейками таблицы.)
При копировании по горизонтали вправо (влево) смещение на одну ячейку увеличивает (уменьшает) каждый номер столбца в формуле на единицу. При копировании по вертикали вниз (вверх) смещение на одну ячейку увеличивает (уменьшает) каждый номер строки в формуле на единицу.
Относительные ссылки имеют следующий вид: А1, В3
2. Абсолютные ссылки
Абсолютная ссылка - ссылка, не изменяющаяся при копировании формулы.
Абсолютный адрес всегда указывает на точный адрес ячейки, вне зависимости от того, где располагается формула, использующая эту ячейку.
Применяется при включении в формулу неизменных величин, которые всегда расположены в одних и тех же ячейках, независимо от положения ссылающихся на них формул.
Внешним признаком абсолютного адреса является наличие знака $ перед значением координаты в адресе ячейки. Знак $ может стоять в адресе, как перед обозначением столбца, так и перед номером строки. Например, адрес $А$1 - абсолютный адрес, при любом копировании и перемещении он останется неизменным и всегда будет указывать на ячейку, находящуюся на пересечении столбца А и строки 1.
Знак $ можно набирать вручную, а можно использовать клавишу F4.
3. Смешанные ссылки
Смешанная ссылка - при копировании формулы может изменяться только какая-то одна часть ссылки (либо буква столбца, либо номер строки).
Символ $ ставится перед той частью ссылки, которая должна остаться неизменной.
Абсолютная ссылка на строку: в этом случае знак $ ставится только перед номером строки. Например, С$4 – это абсолютная ссылка на четвёртую строку.
Абсолютная ссылка на столбец: в этом случае знак $ ставится только перед именем столбца. Например, $С4 – это абсолютная ссылка на столбец С.
Для того, чтобы относительную ссылку преобразовать в абсолютную, достаточно после ввода ссылки нажать клавишу F4 – и знаки $ появятся автоматически.
Встроенные функции
О математических и тригонометрических функциях
Математические и тригонометрические функции используют при выполнении арифметических и тригонометрических вычислений, округлении чисел и в некоторых других случаях. Всего в данной категории имеется 64 функции.
Для добавления функций используется вкладка Формулы:
Суммирование
Простая сумма
Для простейшего суммирования используют функцию СУММ.
Синтаксис функции
СУММ(А),
где А - список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.
Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула =СУММ(В2:В6), указанная в ячейке В7, тождественна формуле =В2+В3+В4+В5+В6. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.
Выборочная сумма
Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ.
Синтаксис функции
СУММЕСЛИ(А;В;С),
где А - диапазон вычисляемых ячеек.
В - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;
С - фактические ячейки для суммирования.
В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать.
Можно суммировать значения, отвечающие заданному условию. Например, в таблице на рис. суммированы только студенты по странам, при условии, что число студентов от страны превышает 200.
Можно суммировать значения, относящиеся к определенным значениям в смежных ячейках. Например, в таблице на рис. суммированы только студенты, изучающие курсы со средней оценкой выше 4,1. Критерий можно ввести с клавиатуры или выбрать нужную ячейку на листе.
ЕСЛИ
Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Функция ЕСЛИ используется при проверке условий для значений и формул.
Синтаксис
ЕСЛИ(лог_выражение;значение_если_истина ;значение_если_ложь)
| значение_если_ложь |
| Логическое выражение |
| значение_если_истина |
ИЛИ
Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис
ИЛИ(логическое_значение1;логическое_значение2; ...)
Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
СЧЁТЕСЛИ
Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
Синтаксис
СЧЁТЕСЛИ(диапазон;критерий)
Диапазон — диапазон, в котором нужно подсчитать ячейки.
Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".
МАКС
Возвращает наибольшее значение из набора значений.
Синтаксис
МАКС(число1;число2; ...)
Число1, число2, ... — от 1 до 30 чисел, среди которых требуется найти наибольшее.
МИН
Возвращает наименьшее значение в списке аргументов.
Синтаксис
МИН(число1;число2; ...)
Число1, число2, ... — от 1 до 30 чисел, среди которых требуется найти наименьшее.
СРЗНАЧ
Возвращает среднее (арифметическое) своих аргументов.
Синтаксис
СРЗНАЧ(число1; число2; ...)
Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.
ABS
Возвращает модуль (абсолютную величину) числа. Абсолютная величина числа - это число без знака.
Синтаксис
ABS(число)
Число — это действительное число, модуль которого требуется найти.
ОКРУГЛ
Округляет число до указанного количества десятичных разрядов.
Синтаксис
ОКРУГЛ(число;число_разрядов)
Число — округляемое число.
Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.
Наконец, для округления до ближайшего четного или нечетного числа можно использовать функции ЧЕТН и НЕЧЕТН, а для ближайшего кратного большего или меньшего числа - функции ОКРВЕРХ и ОКРВНИЗ.
Синтаксис функции ЧЕТН
ЧЕТН(А),
где А - округляемое число.
Функция НЕЧЕТН имеет такой же синтаксис.
Обе функции округляют положительные числа до ближайшего большего четного или нечетного числа, а отрицательные - до ближайшего меньшего четного или нечетного числа.
Синтаксис функции ОКРВВЕРХ
ОКРВВЕРХ(А;В),
где А - округляемое число;
В - кратное, до которого требуется округлить.
Функция ОКРВНИЗ имеет такой же синтаксис.
Следует обратить внимание на различие в округлении и установке отображаемого числа знаков после запятой с использованием средств форматирования. При использовании числовых форматов изменяется только отображаемое число, а в вычислениях используется хранимое значение.
Возведение в степень
Для возведения в степень используют функцию СТЕПЕНЬ.
Синтаксис функции
СТЕПЕНЬ(А;В),
где А - число, возводимое в степень;
В - показатель степени, в которую возводится число.
Отрицательные числа можно возводить только в степень, значение которой является целым числом. В остальном ограничений на возведение в степень нет.
Для извлечения квадратного корня можно использовать функцию КОРЕНЬ.
Синтаксис функции
КОРЕНЬ(А),
где А - число, из которого извлекают квадратный корень.
Нельзя извлекать корень из отрицательных чисел.
Встроенные диаграммы
Общие понятия
В большинстве случаев область представления данных размещается между горизонтальной линией (осью Х) и вертикальной линией (осью Y). Данные представляются точками. Несколько точек образуют ряд. В Excel оси значений и оси категорий отличаются тем, что оси значений отображают числовые интервалы, а оси категорий – произвольный текст. Как правило, ось категорий располагается вдоль оси Ох, ось значений – вдоль оси Оу (вверх), ось Oz (в глубину) является осью категорий.
Excel автоматически определяет, как расположены данные. Если в исходной таблице строк меньше, чем столбцов, то ряды данных расположены по строкам, а категории – по столбцам.
В данном примере два ряда данных: Доходы и Расходы, а ось категорий содержит текстовые значения – названия месяцев.
Вся область диаграммы содержит несколько объектов:
- название диаграммы,
- легенда (описание данных),
- названия осей Х и Y,
- область представления данных.
Диаграммы помещается непосредственно на рабочем листе с табличными данными или на отдельном диаграммном листе.
Типы диаграмм
Различие между двумерными и трехмерными диаграммами чисто эстетическое. Исключение составляет семейство диаграмм, называемых поверхностями.
Нестандартные диаграммы – это вариации стандартных диаграмм с добавлением цветового и специального оформления.
Для построения диаграммы предназначена вкладка Вставка и группа команд Диаграммы.
После того как вы вставили диаграмму, на ленте появились три новые вкладки с работой над диаграммой.