Относительные и абсолютные ссылки
Простая таблица, простые расчеты
Рассмотрим следующий пример. Допустим, что у нас есть 100 000 рублей и мы их хотим поместить на банковский депозит под 11% годовых с ежемесячной капитализацией.
Ежемесячная капитализация — это присоединение начисленных за месяц процентов к сумме вклада. Соответственно, в следующем месяце начисление процентов происходит уже к новой сумме и так далее.
Давайте посмотрим на доход, который мы при этом получим. Для этого создадим соответствующую таблицу.
Итак, давайте приступим.
Сделаем активной ячейку А1 и с клавиатуры введем слово «Месяц», затем выберем ячейку B1 и введем в нее «Размер вклада». Нажмем клавишу Enter.
Вы видите, что название в ячейке В1 перекрыло часть соседней ячейки С1, но это не означает, что в ячейку С1 было что-то помещено. У Эксель есть такая особенность - если соседняя ячейка не занята, а в текущую ячейку информация не помещается из-за ее[н1] размеров, то программа выводит весь текст, не ограничиваясь размерами ячейки.
Давайте введем в ячейку С1 текст «День недели». Мы видим (1), что название из ячейки В1 обрезалось границами ячейки С1. Опять же, это
9
не означает, что название было частично стерто. Оно просто не отображается полностью, но если мы обратим внимание на строку формул (2), то обнаружим там содержимое активной ячейки.
В строке формул отображается все содержимое любой выбранной ячейки, и мы видим, что введенный нами текст никак не изменился (не обрезался).
|
|
|
Мы можем изменить ширину столбца, чтобы текст ячейки отображался полностью. Для этого наводим указатель мыши на границу между заголовками столбцов В и С. Указатель мыши изменится и это означает, что теперь можно захватить границу (нажав и удерживая левую кнопку мыши) и переместить
ее в нужную нам сторону, изменив ширину столбца В.
Если же нужно сделать так, чтобы в ячейку помещалась вся введенная нами информация, то также наводим указатель мыши на границу между столбцами
и дважды щелкаем левой кнопкой мыши. Таким образом мы автоматически скорректируем ширину столбцов по ширине размещенного в ячейках текста.
Теперь нам нужно установить табличный курсор в ячейку A2 и ввести название месяца. Например, мы сделали вклад в ноябре и поэтому введем название месяца в ячейку А2 и нажимаем клавишу Enter. Далее можем продолжить ввод названий месяцев - декабрь, январь, февраль и так далее… Но давайте воспользуемся функцией автозаполнения, чтобы автоматизировать этот процесс.
Если присмотреться к табличному курсору, то мы увидим в правом нижнем его углу квадратик, который называется маркером заполнения.
|
|
|
Наведем на него указатель мыши, который сразу же изменится на крестик. Захватим маркер, то естьнажмем и будем удерживать нажатой левую кнопку мыши и начнем ее перемещать вниз, выделяя ячейки от А2 до А13. Отпустим левую кнопку мыши и увидим, что Эксель сам заполнил месяцы по порядку.
Функция автозаполнения работает аналогично и с днями недели, то есть если мы в соответствующем столбце напишем «понедельник» и затем повторим проделанную операцию, то получим список, в котором дни недели будут идти по порядку.
Чтобы удалить введенные данные, мы должны сначала их выделить. Для этого выбираем первую ячейку, информацию из которой нужно удалить, нажимаем
и удерживаем левую кнопку мыши и ведя мышью по таблице останавливаемся на последней ячейке, выделяя весь диапазон. Нажимаем на клавишу Delete, чтобы удалить содержимое всех выделенных ячеек.
Теперь давайте вернемся к нашей таблице и рассчитаем доход от вклада за первый месяц. Для этого в ячейку В2 введем расчетную формулу:
Формула означает, что сумма нашего вклада в размере 100000 будет умножена на 11%, а поскольку процентная ставка годовая, то делим все на 12 месяцев.
Если ввод данных в ячейке начинается со знака равенства, то Эксель автоматически понимает, что сейчас последует формула, которую нужно вычислить.
|
|
|
После нажатия клавиши Enter обнаружим в ячейке В2 вычисленное значение (1, см. рис. ниже), при этом если мы выберем ячейку В2, то в поле формулы увидим не вычисленное значение, а именно формулу (2) и при необходимости сможем ее отредактировать в этом поле, что мы сейчас и сделаем.
Дело в том, что мы получили размер дохода от вклада за месяц, а нам нужно видеть всю сумму целиком, поэтому добавим в формулу размер вклада.
Формула будет выглядеть так: =100000*11%/12+100000
Далее нам нужно определить размер дохода в следующем месяце.
Получаем формулу: =B2*11%/12
То есть мы сумму вклада не вводим вручную, а берем готовое значение из ячейки B2, указав ее адрес. При этом обратите внимание на то, что адрес ячейки В2 в формуле имеет цвет и сама ячейка обрамляется рамкой такого же цвета:
Ну а чтобы увидеть, какова у нас будет сумма вклада во втором месяце, изменим формулу, добавив значение размера вклада за прошлый месяц. То есть к уже введенной формуле необходимо прибавить значение, находящееся
в ячейке В2. Мы можем ввести адрес ячейки вручную с клавиатуры или укажем ячейку щелкнув по ней мышью:
|
|
|
Этот способ ввода формул более удобный, так можно просто указать нужную ячейку, а не искать ее адрес.
Ну а теперь нужно повторить тоже самое для следующего месяца. Формула будет такая: =B3*11%/12+В3
Давайте проанализируем ситуацию.
Сейчас в таблице отображаются автоматически вычисленные программой значения. Для удобства включим отображение формул - на вкладке Формулы (1, см. рис. ниже) выберем инструмент Показать формулы (2). В итоге в таблице будут отображаться формулы, а не значения (3):
Если проанализируем уже введенные формулы, то увидим, что в последующих формулах у нас будет изменяться только адрес ячейки в столбце В. Это означает, что формула отличается только адресом ячейки!
Что нам это дает?
А дело в том, что мы снова можем воспользоваться автозаполнением данных. Да-да, тоже самое автозаполнение, которое мы проделывали с месяцами!
Точно также захватываем за край табличный курсор на ячейке В3 и перемещаем его до ячейки В13.
Если мы теперь посмотрим на вставленные в ячейки формулы (см. рис. выше), то увидим, что они отличаются только значениями адресов ячеек столбца В, при этом в каждой последующей формуле адрес ячейки увеличивается на единицу. Отличается только формула в ячейке В2, в которой находится сумма вклада – 100 000 рублей.
Давайте несколько улучшим нашу таблицу и сделаем так, чтобы сумма вклада задавалась в отдельном поле. Для этого нам потребуется освободить место над уже созданной таблицей.
Сместим все данные на несколько строк ниже - с помощью мыши выделим все ячейки с данными и наведем указатель мыши на границу выделения. Он изменит свой вид, указывая на то, что теперь мы можем захватить область мышью и переместить ее. Переместим выделенную область на пять строк ниже, освободив тем самым пространство для ввода н данных. 
Если мы теперь обратим внимание на формулы, то
обнаружим, что значения адресов ячеек в них были автоматически изменены при переносе. Поэтому расчет по-прежнему отталкивается от первой строки нашей таблицы, а точнее от ячейки, которая теперь имеет адрес B7.
Это свойство Экселя очень
упрощает нам создание всевозможных таблиц.
Более подробно об этом поговорим дальше, пока же давайте создадим отдельную графу для суммы вклада.
В ячейку А1 введем название всей нашей таблицы «Расчет дохода от вклада», а в ячейку А3 - «Вклад».
Далее в ячейку В3 вынесем сумму вклада - 100 000 рублей.
Теперь нужно отредактировать формулу в ячейке В7 - заменим значение
вклада на значение из ячейки В3. Выделяем ячейку B7 и в строке формулы
заменим сумму вклада
(100000) на ячейку В3.
Для этого можно выделить сумму вклада в поле формулы, а затем щелкнуть мышью по ячейке B3. В формулу
автоматически будет подставлен ее адрес.
Теперь можно отключить режим отображения формул (инструмент Показать формулы на вкладке Формулы) и проверить работу таблицы - убедимся, что значения в таблице зависят от суммы вклада, то есть от значения ячейки В3:
Относительные и абсолютные ссылки
Предлагаю расширить функционал нашей таблицы и вынести в отдельную строку процентную ставку, чтобы была возможность ее также изменять. Но для этого нам понадобятся еще кое-какие дополнительные сведения.
Итак, опять немного теории…
В предыдущей главе мы создали целый столбец значений просто воспользовавшись функцией автозаполнения Excel.
Программа автоматически поняла нашу задумку и значения адресов ячеек в формулы были подставлены правильные, но ка это произошло?
Практически во всех формулах используются адреса ячеек, а правильнее сказать - ссылки на ячейки, что позволяет нам использовать в формулах значения, которые находятся или рассчитываются в других ячейках.
Если в формуле сделана ссылка на ячейку В3 (в которой находится какое-то число) и мы поменяем значение в этой ячейке, то автоматически будет пересчитана и сама формула.
Поясню на примере.
Если ввести в ячейку B3 цифру 2, а в ячейку B4 формулу: = B3+3, то какой будет результат вычислений?
Правильно, после нажатия клавиши Enter в ячейке B4 отобразится вычисленное значение - 5.
Что произойдет, если мы изменим значение ячейки B3 с 2 на 4?
Верно! Значение в ячейке B4 автоматически пересчитается и будет равным 7.
Это означает, что формула будет рассчитываться в соответствии с новым значением ячейки В3.
Если бы не было ссылок (в нашем примере это ссылка в формуле на ячейку B3), то нам бы пришлось вручную изменять все формулы при изменении какого-то одного ее компонента.
Итак, в формулах у нас могут участвовать ссылки на адреса ячеек таблицы.
Эти ссылки могут быть относительными или абсолютными.
Как показывает мой опыт, тема эта не очень проста для понимания, поэтому давайте с ней тщательно разберемся.
Мы с вами научились использовать автозаполнение для тиражирования однотипных формул в таблице. По сути, автозаполнение нам позволяет скопировать формулу из одной ячейки и вставить ее в следующую.
То же самое происходит и при фактическом копировании. Чтобы в этом убедиться, давайте скопируем ячейку В8.
ля этого выделим ячейку (1) и щелкнем на ней правой кнопкой мыши для вызова контекстного меню (2). Данное меню называется контекстным, так
как его содержимое изменяется в зависимости от того, на каком объекте оно вызывается, то есть от
контекста конкретной ситуации.
Из контекстного меню выберем пункт Копировать.
Ячейка выделится динамической рамкой, которая нам указывает на то, что содержимое данной ячейки только что было скопировано:
Теперь установим табличный курсор в пустую ячейку В19 и вставим скопированную информацию. Для этого мы также можем воспользоваться контекстным меню, но давайте задействуем панель инструментов Буфер обмена на вкладке Главная - нажимаем на кнопку Вставить и получаем результат:
Если мы посмотрим на формулу в ячейке B19, то увидим в ней ссылку на ячейку В18: =B18*11%/12+B18
Понимаете, что произошло? Это очень важно понять!
копировали ячейку В8, в которой в формуле использовалась ссылка на вышестоящую ячейку В7 (1), то есть использовалась ссылка на ячейку, которая была выше ячейки с формулой.
Именно так она и была скопирована, то есть в ячейке В19 в формуле используется ссылка на ячейку В18, которая находится выше (2).
Это означает, что адрес ячейки в формуле берется относительно положения ячейки с формулой.
Данный момент у многих вызывает непонимание, поэтому для проверки попробуйте ответить на вопрос - если сейчас скопировать формулу из ячейки В7 (см. рис. выше), которая у нас несколько отличается от формул в других ячейках, и вставить ее в ячейку В20, то ссылка на какую ячейку мы увидим в итоге?
Ответили?
Давайте проверим - в формулу войдет ячейка B16, которая находится выше на четыре ячейки, как и ячейка В3, относительно ячейки В7.
Вот такие ссылки называются относительными, так как в формулу подставляется значение из ячейки, расположенной на определенном расстоянии относительно ячейки, с которой мы работаем.
А то делать, если нужно, чтобы в формуле участвовало значение из какой-то конкретной ячейки и ссылка на эту ячейку никак не изменялась ни при копировании, ни при автозаполнении?
Все логично - нужно использовать абсолютные ссылки, то есть ссылки на конкретные ячейки таблицы.
Как мы знаем, адрес ячейки состоит из обозначения столбца и строки, например, ячейка B3 находится на пересечении третьей строки и столбца B.
Чтобы сделать ссылку на ячейку абсолютной, нужно поставить знак $ перед обозначением столбца и строки (знак доллара ставится с помощью сочетаний клавиш Shift + 4). То есть, если я хочу, чтобы в формуле у меня всегда участвовало значение из ячейки В3, то изменю В3 на $B$3.
Давайте так и сделаем - изменим формулу в ячейке В7:
Получается, что одна ссылка в формуле на ячейку B3 у нас абсолютная, а другая остается относительной. Если теперь скопировать значение ячейки B7
и вставить его в любую другую ячейку, например, В21, то мы увидим следующее:
Абсолютная ссылка по-прежнему ведет на ячейку B3, а относительная изменилась и теперь указывает на ячейку B17.
То есть знаком $ мы как бы запрещаем Экселю изменять столбец или строку в адресе ячейки, и он всегда будет оставаться в неизменном виде.
При этом мы можем запрещать изменять в ссылке что-то одно - либо столбец, либо строку. Такая ссылка будет называться смешанной.
Смешанные ссылки выглядят так - B$2 или $B2, что, соответственно, запретит Эксель менять адрес строки или столбца.
Ну а теперь давайте попрактикуемся и применим полученные знания.
В ячейке А4 напишем «Процент», а в ячейку В4
подставим значение процента по вкладу - 11.
Теперь изменим формулы.
Поменяем в ячейке В7 значение процента на абсолютную ссылку $B$4, которая ведет нас к только что введенному значению.
Затем тоже самое проделаем в ячейке В8.
С помощью автозаполнения растиражируем формулу из ячейки B8 на остальные ячейки столбца B.
Что мы получили в итоге? А то, что теперь мы можем менять и сумму вклада, и процент, а
остальные данные будут вычисляться автоматически!
Давайте снизу таблицы в ячейке А19 напишем – «Доход», а в ячейку В19 подставим простую формулу, вычисляющую этот доход: =B18-B3, то есть сумма итогового вклада с процентами минус сумма начального вклада.
С точки зрения функционала таблица готова, но вот значения в ней не очень наглядны. Давайте применим форматирование к
ячейкам таблицы и их содержимому, чтобы сделать таблицу более читаемой.
Форматирование
Приступим к форматированию электронной таблицы.
Во-первых, сделаем данные в таблице более наглядными.
Выделим диапазон ячеек с В7 по В19 (1, см. рис. ниже) и на вкладке Главная (2) обратимся к панели инструментов Число (3). Здесь в информационном поле (4) мы видим, что все выбранные нами ячейки имеют Общий формат, что есть норма для нового документа.
В этом поле отображается текущий формат выбранной ячейки или диапазона ячеек, но если раскрыть выпадающий список (5), то мы увидим другие форматы (6), которые можем применить для выделенного нами ранее диапазона.
Давайте выберем Денежный формат.
Теперь займемся внешним видом самой таблицей.
С помощью мыши выделим диапазон ячеек от А6 до В19 (1, см. рис. ниже) и перейдем на вкладку Вставка (2). На панели инструментов Таблица щелкнем по одноименному инструменту (3). Появится окно (4), в котором будет указан выбранный нами диапазон (5) в виде абсолютных ссылок.
Диапазон ячеек в Экселе обозначается адресами верхней левой и нижней правой ячеек, которые записываются через двоеточие, например, А6:В19.
В нашем случае адреса ячеек представлены в виде абсолютных ссылок, то есть $A$6:$B$19.
Эксель в состоянии самостоятельно определить неразрывный диапазон ячеек с данными, что очень часто значительно упрощает работу.
Например, нам не обязательно
было предварительно выделять диапазон ячеек мышью, а достаточно было установить табличный курсор в любую из ячеек с данными и выбрать инструмент Таблица на вкладке Вставка.
Результат был бы таким же.
Итак, в окне Создание таблицы нажимаем кнопку ОК, чем подтверждаем, что диапазон ячеек выбран верно.
На ленте появляется еще одна вкладка - Конструктор (2, см. рис. ниже). Ярлычок над этой вкладкой имеет название Работа с таблицами (3) и это свидетельствует о том, что данная вкладка является контекстной. Такие вкладки появляются на ленте в том случае, если в программе выделен какой-то объект, например, таблица (1), диаграмма или рисунок.
Контекстные вкладки содержат инструменты, которые могут применяться только для работы с конкретным объектом. В нашем случае это таблица.
С помощью этой вкладки мы можем настроить внешний вид таблицы.
Для этого можно воспользоваться экспресс-стилями (1, см. рис. ниже), выбрав уже готовый стиль из выпадающего списка (2), либо можем создать полностью свой стиль, выбрав соответствующую опцию (4). При наведении на какой-либо стиль из списка он будет временно применен, и вы сможете оценить внешний вид таблицы (3).
Найдем тот стиль, который нам нравится, и щелкнем по нему. Стиль будет применен к таблице.
Обратите внимание на панель инструментов Параметры стилей таблицы (5).
Я ее помощью можно более тонко настраивать внешнее оформление и функциональность таблицы. Например, мы можем включить параметр Строка итогов, который автоматически создаст снизу таблицы строку со суммарным значением всех ячеек столбца с данными. В нашем случае это не актуально, но вполне может пригодиться при создании таблиц другого рода.
Чтобы данные в таблице были более читаемыми сделаем их жирным шрифтом.
Для этого выделим диапазон ячеек B7:B19 (1) и воспользуемся панелью инструментов Шрифт (3) на вкладке Главная (2).
Аналогичным образом изменим форматирование ячеек В3 и В4 - сделаем данные в них более жирными, а также изменим числовое форматирование для суммы вклада на Денежный.
Можем изменить шрифт, размер и начертание для заголовка таблицы в ячейке
A1 и сделаем более
выразительным графу
дохода, изменив цвет заливки ячеек и текста.
Все эти изменения проделываются с помощью
инструментов панели Шрифт.
Поэкспериментируйте с ними самостоятельно и
приведите таблицу к приблизительно такому же виду, как изображено на рисунке.
Теперь данные в таблице читаются намного лучше и не хватает лишь графического представления, то есть графика или диаграммы.
Построение графика
На основании табличных данных можно построить диаграмму или график, что мы сейчас и сделаем. Поместим табличный курсор в любую ячейку созданной нами таблицы (1) и перейдем на вкладку Вставка (2).
Далее на панели инструментов Диаграммы (3) можем выбрать любой из предложенных типов диаграмм, но я предлагаю щелкнуть по небольшому значку, который находится в правой нижней части панели инструментов (4), чтобы открыть окно Вставка диаграммы. В открывшемся окне можем просмотреть перечень всех доступных в программе диаграмм на соответствующей вкладке (5). Сейчас для нас не особо важен вид будущей диаграммы, поэтому предлагаю перейти в разделе График (6) и выбрать один из вариантов (7).
Диаграмма будем вставлена в центральную часть листа.
Рабочий лист Excel имеет невидимый слой, на котором отображаются диаграммы, изображения и другие объекты, напрямую не связанные с электронной таблицей. Именно на этот слой сейчас была помещена диаграмма и она никак не привязана к ячейкам таблицы.
Это означает, что мы можем захватить диаграмму мышью за границу и перенести в любое место на листе, или же можем изменять размеры диаграммы, с помощью специальных маркеров, расположенных на границе.
нешний вид диаграммы можно изменить. Для этого выделим ее и увидим, что появились контекстные вкладки под общим названием Работа с диаграммами:
На вкладке Конструктор можно выбрать макет диаграммы и задать ее стиль. При этом можно воспользоваться готовыми стилями (1), цветовыми схемами
к или экспресс-макетами диаграмм (3):
Если нужна более тонкая настройка отдельных элементов диаграммы, то стоит обратиться к контекстной вкладке Формат, на которой расположены соответствующие инструменты:
При взгляде на график мы видим, что он в конечном итоге "срывается" вниз. Дело в том, что график у нас был построен по всем значениям таблицы, в том числе и по последней строчке - Доход, которая не должна была участвовать при построении диаграммы:
Обратите внимание, что при выборе диаграммы в таблице разноцветными рамками выделяются те области, которые участвуют в построении графика.
Так как итоговое поле Доход не должно использоваться при построении графика, то следует исключить его из диаграммы. Сделать это можно изменив диапазон выбранных в таблице ячеек с помощью мыши, то есть перетянуть за маркер одну из рамок выше на одну строку.
Теперь последнее значение таблицы не учитывается и график роста вклада выглядит так, как должен.
Ну вот, отчет готов и осталось лишь вывести его на печать.
Эксель очень «чувствителен» к выделенным объектам, поэтому если, например, выделена диаграмма или какой-то другой объект на листе, то на печать может попасть только выделенная область.
По этой причине нажмем клавишу Esc, чтобы снять выделение с любых объектов на листе и далее щелкаем на меню Файл, которое находится в верхнем левом углу окна программы. Это не вкладка, а именно меню. С помощью него мы можем открывать ранее созданные или полученные откуда-то документы, сохранять или пересохранять документы под другим именем, а также выводить документы на печать.
Переходим в раздел Печать (1) и выбираем принтер, который подключен к компьютеру (2). В области предварительного просмотра мы увидим, что диаграмма не полностью поместится на листе бумаги после распечатки (3):
Возникает вопрос - как узнать, каким образом нужно изменить объекты и данные в электронной таблице, чтобы они полностью помещались на листе бумаги после вывода на печать?
И тут нам поможет инструментарий программы Эксель.
и нижней части окна программы находится область, которая называется строкой состояния.В эту строку выводится некоторая информация оботкрытом в программе документе. Также в правой части строки состояния находятся инструменты, позволяющие изменять отображение документа в программе. Например, мы можем изменить масштаб отображения документа, но нас сейчас будет интересовать кнопка Разметка страницы:
При щелчке мышью по этой кнопке Excel покажет, как будет размещаться сознанный нами документ на странице формата А4.
Это очень удобно, так как мы сразу можем подправить какие-то неточности или изменить положение объектов, чтобы они лучше выглядели в распечатанном виде.
В нашем случае видно, что диаграмма не поместилась на одном листе и была частично перенесена на вторую страницу. Можно, конечно, уменьшить диаграмму, но лучше изменить ориентацию страницы на альбомную.
Перейдем на вкладку Разметка страницы (1) и из выпадающего списка Ориентация (2) выберем Альбомная (3):
Далее при необходимости подкорректируем размеры и расположение диаграммы на листе
Документ готов к печати. Заходим в соответствующий раздел меню Файл и выберем из списка принтер, который подключен к компьютеру. Убедимся, что
В окне предварительного просмотра документ отображается желаемым образом и нажмем на кнопку Печать.
Теперь стоит сохранить наши труды. Эксель по умолчанию делает автосохранение документа каждые десять минут и если во время работы случится что-то с программой или компьютером, то при последующем запуске Экселя программа предложит открыть автосохраненную копию вашего документа. Но все же лучше не полагаться на программу и не забывать сохраняться, как в самом начале создания документа, так и в процессе работы.
Итак, в меню Файл выберем пункт Сохранить, далее укажем место на диске, куда будет сохранена наша книга и введем ее название:
[н1]
Дата добавления: 2021-04-06; просмотров: 62; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!