Абсолютные, относительные и смешанные ссылки.

Перейдите на Новый лист.

Введите в ячейки А1, А2, В1, В2 числа 1, 2, 10, 15. В ячейке D1 запишите формулу =А1+В1, нажмите ввод. Скопируем D1 в D2. Как видите, копирования результата не происходит. Вместо =А1+В1 мы получили в D2 формулу =А2+В2. В исходной формуле использовались относительные ссылки на ячейки А1 и В1 (говорят, что использована относительная адресация). Это значит, что при копировании эти ссылки автоматически изменяются в соответствии с новым положением формулы. Как это происходит?

При копировании мы перемещаемся на несколько столбцов влево или вправо и несколько строк вверх или вниз. Эти числа запоминаются, и точно на столько же строк и столбцов изменяются все адреса в исходной формуле. В нашем примере при копировании мы перешли из D1 в D2 на одну строку вниз и не перемещались по столбцам. Поэтому А1 заменилось на А2, а В1 - на В2.

Теперь очистите D2, вернитесь в D1 и отредактируйте формулу так: =$А$1+$B$1. Снова проведите копирование из D1 в D2. Видно, что результат и сама формула не изменились. Адресация с использованием знака «$» называется абсолютной, - при копировании ссылки не меняются.

Если ссылка на строку является абсолютной, а на столбец - относительной (или наоборот), то адресация называется смешанной. Замените в D1 формулу на такую: =$A1+B$1. При копировании в D2 ссылка $A1 заменится на $A2, т.к. строка не закреплена (закреплён столбец), а B$1 останется неизменной (хотя столбец В не закреплён, - он не изменится, т.к. при копировании из D1 в D2 мы не совершаем сдвиг по столбцам).

Скопируйте теперь формулу =А1+В1 из D1 в F3. Мы перемещались на 2 столбца вправо и на 2 строки вниз. А1 и В1 изменяются по этому же принципу и переходят в С3 и D3 соответственно.

Если скопировать ту же формулу из ячейки F3 в ячейку В5, то в В5 появится сообщение об ошибке: #ССЫЛКА! Дело в том, что мы пытаемся адресоваться к несуществующему столбцу, т.к. левее столбца А ничего нет, а мы копируем влево на недопустимое в данном случае количество ячеек.

Рассмотрим различные ссылки на примере.

1. Перейдите на лист Оплата счетов.

2. Добавим в таблицу новый столбец. Запишите в F2 Сумма НДС.

3. В ячейку А8 введите НДС=, в В8 0,2; установите в этой ячейке %-й формат.

4. Введите в F3 формулу: =D3*В8/(1+В8). Она основана на следующем: Сумма НДС=Сумма опдаты´ .

5. Проведите автозаполнение до ячейки F7. Получили нули - это следствие относительной адресации при копировании, частным случаем которого является автозаполнение. Посмотрите на формулы в ячейках F4:F7: В8 заменилась на В9…В12.

6. Отредактируйте F3: =D3*$В$8/(1+$B$8).

7. Сделайте автозаполнение. Как видите, в данном случае абсолютная адресация дала нужный результат. Мы могли бы использовать и смешанную, вместо $B$8 записывая В$8, т.к. автозаполнение происходило вдоль столбца. Заметим, что в этой же таблице мы видели пример полезного применения и относительной адресации, когда заполняли столбец Долг.

8. Установите для диапазона C3:F7 денежный формат: Формат ® Ячейки ® Число ® Денежный (### 0,00 р.).

Часть 2. Задания для самостоятельной работы.

Каждое задание выполняется на отдельном листе книги Excel.

Задание 1. Математические функции.

Мы уже познакомились с одной из них - СЛЧИС(), возвращающей случайное число в диапазоне от 0 до 1. Заметим, что у этой функции нет аргументов. Рассмотрим ещё одну такую функцию - ПИ().

Предположим, вам нужно подсчитать площадь круга по формуле S= .

Перейдите на Новый лист. Введите значение радиуса в ячейку А1 (любое число). В ячейке В1 вызовите Мастер функций, категория Математические, функция ПИ, нажмите ОК. В ячейке В1 вы получите результат. Если сделать столбец В шире, то можно увидеть ещё несколько дробных знаков. Теперь нажмите F2 и допишите в ячейку В1: =ПИ()*А1*А1, чтобы получить площадь круга.

Задание: Рассчитать значения на отрезке [a;b] c шагом 0,1. Результаты оформить в виде таблицы. Варианты заданий выбираются из таблицы. Номер варианта выбирается по четвертой цифре номера зачётной книжки. Например, зачётная книжка № 931400 ® Вариант № 4.

№
	– 3
	0,2	1,4
	– 3

 

 

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

Добавьте лист Логические функции.

Пример1.

Вычислить значение функции у=f(x) по формуле:

у=

Введите любое число в ячейку А1, а в В1 - следующую формулу: =ЕСЛИ(А1<0;А1;А1^2).

У функции ЕСЛИ - 3 аргумента. Первый - это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. Второй и третий - выражения, которые вычисляются в активной ячейке в том случае, если 1-й аргумент принимает соответственно истинное или ложное значение.

Проверьте работу функции, задавая в А1 несколько разных значений.

Пример2.

Вычислить значение функции у=f(x) по формуле:

у=

Введите в А1 любое число, в В1 формулу: =ЕСЛИ(И(А1>= -5;А1<= 5);А1*А1-1;А1).

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

Пример3.

Вычислить значение функции у=f(x) по формуле:

 

у=

Задачу решает такая формула:

=ЕСЛИ(И(А1>= -5;A1<=5);А1^3;ЕСЛИ(ИЛИ(И(А1>= -10;A1< -5);И(А1>5;A1<= 10));A1;A1*A1)).

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

Задание: Рассчитать значения на отрезке [a;b] c шагом h. Результат оформить в виде таблицы.

Варианты заданий выбираются из таблицы. Номер варианта выбирается по последней цифре номера зачётной книжки. Например, зачётная книжка № 931416 ® Вариант № 6

 

			h
	– 4		0,4
	– 0.9	0.9	0,15
	– 1		0,2
	4,2	5,85	0,3
			0,4
	– 3
	– 2		0,5

 

Задание 3 (одинаковое для всех вариантов). Статистические функции.

Добавьте лист Статистические функции1. Введите данные столбцов А, В, С.

 

	А	В	С	D
	Студент	Оценка	Оценка	Количество
	Викторов
	Валерьев
	Валерьева
	Ильичёв
	Антонов
	Евгеньев
	Михайлов
	Дмитриев
	Алексанров
	Надеждина
	Татьянова
	Петрова
	Иванов
	Павлова
	Степанов
	Егоров

Для того чтобы подсчитать, какое количество студентов получили ту или иную оценку, воспользуемся функцией СЧЁТЕСЛИ. Сначала выделите диапазон с оценками, нажмите Вставка ® Имя ® Присвоить и назовите этот диапазон: Оценки. Теперь в ячейке D2 вызовите функцию =СЧЁТЕСЛИ(Оценки;C2). После этого сделайте автозаполнение.

 

Задание 4 (одинаковое для всех вариантов). Использование комбинированных формул.

Добавьте лист Комбинированные формулы1.

Введите следующие данные (кроме столбца G и диапазона А13:А17).

 

	A	B	C	D	E	F	G
	Фирма	Сумма оплаты	Оплачено	Назначенная дата оплаты	Реальная дата оплаты	Долг	Задержка оплаты
	Рекорд	105 100р.	100 000р.	15.02.02	13.02.02	5 100р.	-2
	Рубин	207 000р.	220 000р.	16.02.02	16.02.02	-13 000р.
	Горизонт	153 000р.	139 000р.	20.02.02	25.02.02	14 000р.
	Сокол	192 000р.	185 000р.	23.02.02	23.02.02	7 000р.
	Витязь	220 000р.	220 000р.	23.02.02	20.02.02	0р.	-3
	Sony	735 000р.	750 000р.	25.02.02	25.02.02	-15 000р.
	Panasonic	652 000р.	628 000р.	26.02.02	24.02.02	24 000р.	-2
	Samsung	546 000р.	590 000р.	26.02.02	22.02.02	-44 000р.	-4
	3N	465 000р.	400 000р.	27.02.02	27.02.02	65 000р.
	Philips	357 000р.	340 000р.	27.02.02	28.02.02	17 000р.
	Всего	3 632 100р.	3 572 000р.			60 100р.
	-4900

Дату вводите так: 15-2 для 15.фев…Затем выберите формат ячейки Дата и установите образец 14.03.99 Присвойте соответствующим диапазонам названия: Фирма, С_оплаты, Оплачено, Наз_Дата, Реал_Дата, Долг, З_оплаты.

В ячейку G2 введите знак «=», мышью обозначьте диапазон Реал_дата, поставьте «-», обозначьте Наз_дата, нажмите Ctrl+Shift+Enter. Выделите диапазон З_оплаты, нажмите F2, Ctrl+Shift+Enter.

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

Работа с одним критерием.

Суммирование значений одного знака.

Как видите, имеются должники, а также фирмы, заплатившие вперёд. Простым автосуммированием мы узнаем общую сумму долга, но этой информации может быть недостаточно. Чтобы найти сумму для фактических должников, в ячейку А13 введите: =СУММЕСЛИ(Долг;”>0”).

Суммируемые и проверяемые значения принадлежат разным диапазонам.

Предположим, вы хотите узнать, на какую сумму были проведены поставки только по отношению к должникам. Тогда в ячейку А14 введите: =СУММЕСЛИ(Долг;">0";С_оплаты).

Суммирование на основе сравнения дат.

Если вы хотите узнать, какая общая сумма была выплачена на 25 февраля, - воспользуйтесь формулой =СУММЕСЛИ(Реал_дата;"<25.фев";Оплачено) в ячейке А15.

Допустим, мы хотим найти сумму, которой мы рисковали, исходя из того, что имеются фирмы, проведшие оплату с опозданием. В столбце G положительное число говорит, что оплата - с задержкой, отрицательное - что досрочная. Поэтому в ячейку А16 нужно ввести: =СУММЕСЛИ(З_оплаты;">0";С_оплаты).

Суммирование на основе сравнения текста.

Из таблицы видно, что наш основной должник - фирма 3N. Проверим, как выглядит долг без учёта данных по этой фирме. Для этого введём в ячейку А17: =СУММЕСЛИ(Фирма;"<>3N";Долг).

Работа на основе нескольких критериев.

Суммирование при выполнении всех критериев.

Предположим, вы хотите подсчитать сумму, на которую были поставлены детали фирмам-должникам, которые кроме того и оплату провели с задержкой. Тогда воспользуйтесь формулой массива: {=СУММ((Долг>0)*(З_оплаты>0)*(С_оплаты))}. (Не СУММЕСЛИ)

Эта формула использует 3 массива из 10 элементов: массив С_оплаты и 2 виртуальных массива, которые существуют только в оперативной памяти компьютера - логический массив, где значения ИСТИНА будут соответствовать положительным значениям диапазона Долг, и ЛОЖЬ в противоположном случае; логический массив, где значения ИСТИНА будут соответствовать положительным значениям диапазона З_оплаты, и ЛОЖЬ в противоположном случае.

Логические значения умножаются по правилам:

ИСТИНА*ИСТИНА=1*ИСТИНА=1

ИСТИНА*ЛОЖЬ=ЛОЖЬ*ЛОЖЬ=ЛОЖЬ*1=ЛОЖЬ*0=0

Поэтому значения из диапазона С_оплаты умножаются на 1 только тогда, когда оба соответствующих значения в виртуальных массивах равны ИСТИНА. Иначе они умножаются на 0.

Выясните, какая сумма была получена в период от 20 до 25 февраля от фирм, проведших эту оплату вовремя.

Решение таково:

{=СУММ((ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))>ДАТА(2002;2;19))*(ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))<ДАТА(2002;2;26))*(З_оплаты<=0)*С_оплаты)}.

Обратите внимание, что все условия берутся в скобки.

В этой формуле использованы функции категории Дата и время. Сравнивать даты можно только тогда, когда они представлены в числовом формате. Эту функцию и выполняет ДАТА().

В любой ячейке введите =ДАТА(2002;2;19). Чтобы теперь отобразить результат в числовом формате, - нажмите Формат ® Ячейки ® Числовой ® Без дробных знаков. Получим 37 306. Как видите, даты нумеруются по порядку и на этом основании сравниваются. Очевидно, для работы с датами в числовом формате нет необходимости отображать их в этом формате.

Ниже мы рассмотрим ещё несколько функций этой категории.

Суммирование при выполнении хотя бы одного критерия.

Предположим, вы хотите подсчитать сумму, на которую были поставлены детали фирмам, которые провели оплату либо в срок, либо без долга или кредита. Тогда введите формулу:

{=СУММ(Если((Долг=0)+(З_оплаты=0);1;0)*(С_оплаты))}.

Здесь знак «+» выполняет роль функции ИЛИ. Комбинирование «+» и «*» без функции ЕСЛИ приведёт к ошибке, т.к. ИСТИНА+ИСТИНА=2, а не 1.

Соединение критериев.

Допустим, нужно выяснить, какая сумма была получена в период от 20 до 25 февраля от тех из фирм Рубин, Рекорд, Sony, которые провели оплату вовремя. Тогда нужно воспользоваться формулой, соединяющей действие функций И и ИЛИ:

{=СУММ((ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))>ДАТА(2002;2;19))*(ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))<ДАТА(2002;2;26))*(З_оплаты<=0)*ЕСЛИ ((Фирма=”Рекорд”)+(Фирма=”Рубин”)+(Фирма=”Sony”);1;0) *(С_оплаты)}.

Дополнительное задание.

Аналогично, учитывая различные критерии, можно работать с функцией СЧЁТЕСЛИ. Эта функция подсчитывает количество ячеек в диапазоне, удовлетворяющих определённому критерию. Обратите внимание на отличие этой функции от функции СЧЁТ (помимо условия).

Ответьте на следующие вопросы.

1. Каково количество должников? =СЧЁТЕСЛИ(Долг;”>0”)

2. Какие фирмы провели оплату точно в срок? =СЧЁТЕСЛИ(З_оплаты;0)

3. С какими из фирм заключены более крупные договора, чем с фирмой Philips? =СЧЁТЕСЛИ(С_оплаты;”>”&B11).

Для подсчёта с учётом нескольких критериев вспомним, что логические значения умножаются и складываются по правилам:

ИСТИНА*ИСТИНА=1*ИСТИНА=1

ИСТИНА*ЛОЖЬ=ЛОЖЬ*ЛОЖЬ=ЛОЖЬ*1=ЛОЖЬ*0=0

ИСТИНА+ИСТИНА=2

4. Есть ли такие фирмы, договор с которыми не превышает 250 000 р. и которые провели оплату лишь в марте? {=СУММ((С_оплаты<250000)*(МЕСЯЦ(Реал_дата)=3))}

5. Есть ли такие фирмы, договор с которыми не превышает 250 000 р. и которые либо имеют долг, либо провели оплату с задержкой?

{=СУММ((С_оплаты<250000)*ЕСЛИ((Долг>0)+(З_оплаты>0);1;0))}

 

Задание 5. Построение диаграмм и графиков.

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

 

 

A	B	C	D	E
Оплата счетов за текущий месяц
№	Фирма	Сумма в счёте	Сумма оплаты	Долг
	Рубин
	Горизонт
	Сокол
	Рекорд
Всего:

 

Выделите диапазон А2:Е6 и вызовите Мастер диаграмм. Постройте гистограмму, посмотрите, как можно с помощью контекстного меню изменять параметры диаграммы.

Затем выделите несмежные диапазоны В2:В6 и D2:D6 (включая заголовки) и постройте круговую диаграмму. В контекстном меню выберите Параметры диаграммы, перейдите на вкладку Подписи данных и укажите Доля.

Теперь используем мастер диаграмм для построения графиков функций.

Пример: Построим график функции у=sin(х) на отрезке [-2 с шагом h= /5. Это число получено так: отрезок разбит на n=20 (можно любое) равных частей. Всего будет n+1 узлов разбиения.

1. В ячейку А1 введите: -2*ПИ().

2. В ячейку В1 введите: =SIN(А1).

3. В ячейку А2 введите: =А1+ПИ()/5.

4. Сделайте автозаполнение из В1 в В2.

5. Выделите диапазон А2:В2 и сделайте автозаполнение до 21-й строки.

6. Вызовите Мастер диаграмм. Выберите График, левый верхний образец, далее.

7. На вкладке Диапазон данных проведите мышью по диапазону В1:В21, перейдите на вкладку Ряд. Проведите по диапазону А1:А21, нажмите Готово.

8. Вызовите контекстное меню щелчком по линии графика, опция Формат рядов данных, вкладка Вид. Поставьте флажок Сглаженная линия.

Задание: Построить график функции на отрезке [a;b] c шагом h.

Варианты заданий выбираются из. Номер варианта выбирается по сумме последних трёх цифр номера зачётной книжки. Например, зачётная книжка № 931417 ® Вариант № 4+1+7=12.

№	n	m	H
	– 2		0,25
	– 0.9	0.9	0,1
	– 1		0,2
			0,2
	– 4		0.4
	4.2	5.85	0.15
	– 3		0,5
	– 2.1		0,3
	– 20
		13.2	0.1
	– 0.1	0.1	0,04
		1,4	0,1
	– 5		1,5
	– 2		0,25
	– 1.4	1.4	0,2
	– 1,05	1,05	0,15
		2,4	0,15
	– 12
			0,25
			0,25

 

Задание 6. Операции с матрицами.

Рассмотрим систему n линейных алгебраических уравнений с n неизвестными.

 

 

Система из n уравнений с n неизвестными, определитель которой не равен 0, обладает решением, притом только одним.

1). Решение можно получить по формулам Крамера:

 

,

 

где d - определитель системы, - определитель, получающийся заменой i-го столбца матрицы системы столбцом .

 

Пример. Решить систему:

 

 

Для нахождения решения пользуемся функцией МОПРЕД.

2). Можно решить систему, исходя из матричного уравнения АХ=В, откуда (А^-1А)Х=А^-1В, Х=А^-1В.

Для обращения матрицы:

1. Ввести элементы матрицы n´n в свободные ячейки (диапазон Х).

2. В свободную ячейку - МОБР(Х).

3. Enter - появится элемент А₁₁^-1.

4. Выделить диапазон n´n с А₁₁^-1 в качестве левой верхней ячейки.

5. Нажать клавишу F2.

6. Нажать Ctrl+Shift+Enter.

 

Для умножения матрицы на столбец подготавливаем столбец вместо матрицы и используем МУМНОЖ. Получим Х=А^-1В.

Затем делаем проверку: АХ=В должно иметь место.

Задание: Найти решение системы 4 линейных алгебраических уравнений с 4 неизвестными.

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

Задание 7. Подбор параметра.

Рассмотрим подбор параметра при решении уравнений.

Пример: Найти все корни уравнения

Введите в ячейку В1 формулу: =A1^4-15*A1^2+10*A1+24. Нажмите Сервис ® Подбор параметра:

Установить в ячейке $В$1

Значение 0

Изменяя значение ячейки $A$1

Результат будет зависеть от того, что записано в А1. Подобранный корень - какой-то из концов отрезка между двумя нулями, содержащего число, записанное в А1.

Получив в А1 число а, делим многочлен столбиком на (х-а). Затем делаем то же самое для нового корня. Все корни: -4, -1, 2, 3.

Задание: Найти все корни уравнения. Варианты заданий выбираются из табл. Номер варианта выбирается по модулю разности последней и четвертой цифры номера зачётной книжки. Например, зачётная книжка № 931423 ®3-4=-1, =1 Вариант № 1.

 

№
	– 5,5	– 4
	– 3
	0,2	1,4
	– 3

Задание 8. Построение трехмерных диаграмм и графиков

Задание: Построить таблицу значений функции z(x,y) и ее отображение в виде поверхности на области с шагом 0,1 по каждому направлению. Номер варианта выбирается по разности между последней и предпоследней цифрой шифра из следующей таблицы:

 

 

Варианты заданий:

N вар.	Функция

 

При построении диаграммы – поверхности:

1. поставить подписи оси x и оси y;

2. проградуировать ось z так, чтобы поверхность разбивалась на 5-7 частей; все части окрасить в серый цвет; убрать цвета стенок и основания;

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