ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 18.11.2021
Просмотров: 313
Скачиваний: 3
21
Автор: доцент, к.ф.-м.н., Андаспаева А.А.
Абсолютные ссылки
Как было сказано выше, если потянуть за маркер авто заполнения формулу, содержащую относи-
тельные ссылки, Excel пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их
адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывают на одну и ту же ячей-
ку.
Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой
столбца и адресом строки, например $A$1.
Смешанные ссылки
Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак дол-
лара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания
тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и аб-
солютная по строке 1. Если мы потянем за маркер авто заполнения эту формулу вниз или вверх, то ссылки
во всех скопированных формулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные.
Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет
пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и
тот же номер строки ($1), но изменится номер столбца (A, B, C...).
Именованные ячейки
Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к
ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать
везде, где можно использовать то значение, на которое указывает ссылка.
Примечание!
Именованные ячейки по умолчанию являются абсолютными ссылками.
Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в
текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не
может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диа-
пазон ячеек (выделенный с "Ctrl").
Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу "F3", откроется следующее окно:
22
Автор: доцент, к.ф.-м.н., Андаспаева А.А.
Пример использования:
"=СУММ(tablica_1);"
Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это
имя) - Вставка/имя/присвоить/удалить
Формулы в Microsoft Excel
Общие сведения
Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Фор-
мулой Excel считает все, что начинается со знака "=". Если в ячейке написать просто "1+1", Excel не будет
вычислять это выражение. Однако, если написать "=1+1" и нажать Enter, в ячейке появится результат вы-
числения выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если
сделать двойной щелчок по ячейке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф.
Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После
двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завер-
шения нажать клавишу Enter.
В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки
на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблю-
дается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения
порядка выполнения операций используются круглые скобки.
Использование текста в формулах
Если в формуле используется текст, то он
обязательно
должен быть заключен в двойные кавычки.
Если написать формулу «=мама», Excel выдаст ошибку, а если написать «="мама"» — все ок, корректная
формула.
Использование ссылок в формулах
Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его
вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нуж-
ный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.
Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень
удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелк-
нете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут
прямоугольники соответствующих цветов. Гораздо проще найти, куда указывает ссылка, по цвету прямо-
угольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разно-
цветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при
23
Автор: доцент, к.ф.-м.н., Андаспаева А.А.
этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле,
особенно после копирования маркером авто заполнения.
Операторы
Операторы в Excel бывают бинарные и унарные. Бинарные операторы работают 2 значениями. На-
пример, оператор «*» умножает число слева от себя на число справа от себя. Если число слева или справа
опустить, то Excel выдаст ошибку.
Унарные операторы оперируют одним значением. Пример унарных операторов: унарный «+» (ни-
чего не делает), унарный «-» (меняет знак числа справа на противоположный) или знак «%» (делит число
слева на 100).
Арифметические операторы
«+» — сложение (Пример: «=1+1»);
«-» — вычитание (Пример: «=1-1»);
«*» — умножение (Пример: «=2*3»);
«/» — Деление (Пример: «=1/3»);
«^» — Возведение в степень (Пример: «=2^10»);
«%» — Процент (Пример: «=3 %» — преобразуется в 0,03; «=37*8 %» — нашли 8 % от 37). То есть
если мы дописываем после числа знак «%», то число делится на 100.
Результатом вычисления любого арифметического выражения будет число.
Логические операторы
">" — больше;
"<" — меньше;
">=" — больше, либо равно;
"<=" — меньше, либо равно;
"=" — равно (проверка на равенство);
"<>" — неравно (проверка на неравенство).
Оператор объединения 2-х строк текста в одну
Оператор «&» (амперсанд) служит для «склеивания» между собой двух текстовых строк. Например, в
ячейке A1 текст «мама», в ячейке A2 текст «мыла раму». В A3 пишем формулу «=A1 & A2». В результате в
ячейке A3 появится текст «мамамыла раму». Как видим, пробел между двумя строками автоматически не
ставится. Чтобы вставить этот пробел, нужно изменить формулу вот так: «=A1 & " " & A2». Точно так же
работает оператор "СЦЕПИТЬ", выглядеть формула с его участием будет так: «=Сцепить(A1;" ";A2)».
Операторы ссылок
: (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочета-
ние является ссылкой на диапазон (A1:A15);
; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ(A1:A15;B1:B15));
(пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов
(B7:D7 C6:C8).
Выражения
Выражения в Excel бывают арифметические и логические.
24
Автор: доцент, к.ф.-м.н., Андаспаева А.А.
Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значе-
ние (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», резуль-
тат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА»
(одно число либо больше другого, либо не больше, других вариантов нет).
Функции в Microsoft Excel
В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь использу-
ется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок
(кода), предназначенный для решения каких-то задач.
Все функции в Excel характеризуются:
Названием;
Предназначением (что, собственно, она делает);
Количеством аргументов (параметров);
Типом аргументов (параметров);
Типом возвращаемого значения.
В качестве примера разберем функцию «СТЕПЕНЬ»
Название:
СТЕПЕНЬ;
Предназначение:
возводит указанное число в указанную степень;
Количество аргументов:
РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);
Тип аргументов:
оба аргумента должны быть числами, или тем, что в итоге преобразуется в число.
Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать
логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает
«ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То
есть логические значения в итоге преобразуются в числовые;
Тип возвращаемого значения:
число — результат возведения в степень.
Пример использования:
«=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячейке и нажать Enter, в
ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значе-
ние.
Пример формулы для вычисления длины окружности, содержащую функцию ПИ():
Синтаксис записи функции
Как вы видите, чтобы Excel не выдал ошибку, функция должна соответствовать определенному на-
бору правил. Этот набор правил называется
синтаксис
записи функции.
Общий синтаксис записи любой функции в Excel:
имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])
Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.
Некоторые функции вообще не принимают аргументов. Например, функция
ПИ()
просто возвраща-
ет в ячейку значение константы «3,1415…», а функция
СЕГОДНЯ()
вставляет в ячейку текущую
дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обяза-
тельно, иначе Excel выдаст ошибку!
Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число),
cos(число) и т. п.
25
Автор: доцент, к.ф.-м.н., Андаспаева А.А.
Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяют-
ся между собой точкой с запятой «;».
В общем случае, аргументами функции могут быть константы (числа, введенные вручную), ссылки на
ячейки, ссылки на диапазон ячеек, именованные ссылки и другие функции (вложенные функции).
Ввод функций вручную
Для набора простейших формул, содержащий функции, можно не пользоваться специальными
средствами, а просто писать их вручную (см. рис. выше). Однако, этот способ плохо подходит для набора
длинных формул, таких, как на рис. ниже.
К недостаткам набора формул вручную можно отнести:
Руками ставить «=»;
набирать имя функции;
открывать/закрывать круглые скобки;
расставлять точки с запятой;
следить за порядком вложенности;
заключать текст в двойные кавычки;
не иметь возможность посмотреть промежуточные расчеты;
и т. п.
Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и
на это уходит много времени.
Ввод функции с помощью кнопки
"сигма"
Одно из средств облегчить и ускорить работы с функциями — кнопка
группы команд
"Редак-
тирование" вкладки "Главная"
. В ней разработчики Microsoft «спрятали» пять часто используемых
функций:
СУММ(минимум один, максимум 30 аргументов)
. Суммирует свои аргументы.
Полезный совет:
Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку
состояния — там должна отображаться сумма;
СРЗНАЧ(минимум один, максимум 30 аргументов)
. Находит среднее арифметическое аргумен-
тов;
СЧЁТ(минимум один, максимум 30 аргументов)
. Подсчитывает количество чисел в списке аргу-
ментов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируют-
ся);
МАКС(минимум один, максимум 30 аргументов)
. Возвращает максимальный аргумент;
МИН(минимум один, максимум 30 аргументов)
. Возвращает минимальный аргумент.