49. Формула в Excel — это настолько мощный инструмент, что если вы с ним еще не работали, то вы наверняка удивитесь, как вы до сих пор жили без него.
Формула должна обязательно начинаться с знака =, в формуле могут использоваться знаки арифметических операций +, -, *, /, числа и имена ячеек, а также встроенные функции редактора.
Имена ячеек в формулах можно набирать вручную. С целью экономии времени и во избежание ошибок при построении формул можно задавать ссылки на ячейки с помощью мыши. Для этого после ввода каждой операции (арифметического действия) вместо того, чтобы набирать вручную имя ячейки, нужно кликнуть мышкой на соответствующей ячейке — и ее имя появится в формуле. При использовании этого метода вам нужно поступить следующим образом: ввести в ячейку знак =, кликнуть мышкой на ячейке B1 (вы увидите, как она выделится), ввести знак *, кликнуть мышкой на С1 и нажать Enter.
Например, вы хотите подсчитать бюджет будущей вечеринки. Для этого вам нужно в столбик Excel ввести табличку с продуктами, которые вы хотите купить и ценами. В третьей колонке указано количество каждого продукта, которое вы планируете закупить.
В четвертой колонке введите формулу =B1*C1 и нажмите Enter.
Если в ячейку ввести формулу и нажать клавишу Enter, то в этой ячейке будет записан числовой результат вычисления по формуле. Если активизировать ячейку с формулой, то выше имен столбиков в строке формул будет записан текст формулы, по которой была вычислена ячейка.
Изменить формулу можно несколькими способами:
— дважды нажать левую клавишу мышки на ячейке, внести изменения в формулу и нажать Enter
— внести изменения в формуле в строке формул и нажать Enter
— активизировать ячейку , нажать клавишу F2, внести необходимые изменения и нажать Enter.
Если изменить содержимое ячейки, которая использована в формуле, то изменится и результат, вычисленный по формуле, соответственно вычисленным числовым данным.
В другую ячейку вы можете ввести формулу, подсчитывающую общую сумму расходов, то есть D1+D2+D3+D4+D5.
А теперь представьте, что ваши планы изменились, и вы хотите купить не 5, а 6 килограмм мяса, но взять меньше чипсов. Внесите все изменения в третью колонку, и вы увидите, как изменятся и формулы, и формулы с суммой по каждой позиции, и общая сумма.
Чудесный инструмент, не правда ли?
Автозаполнение формул.
Еще одна важная возможность использования формул в Excel — их удобное автозаполнение. В нашем примере удалите все формулы в четвертой колонке, кроме первой позиции — пиво. А теперь выполните автозаполнение этой формулы на всю таблицу.
Как видите, формула правильно скопировалась для других строчек, и значения подсчитались абсолютно верно. Обратите внимание: если автозаполнение происходит по столбцам, то изменяется номер столбца. Если автозаполнение происходит по строкам, то в формуле изменяется номер строки.
Таким образом, если в вашей таблице необходимо провести одинаковые вычисления, воспользуйтесь функцией автозаполнения, чтобы повторно не вводить одинаковые формулы.
Адресация ячеек.
При построении формул используется два типа адресов ячеек — относительные и абсолютные. При относительном адресе имя ячейки записывается в обычном виде — А1, В10, С8.
Относительный адрес указывает на ячейку, исходя из ее положения относительно ячейки, в которой находится формула. При автозаполнении формулы с относительным адресом ячейки формула изменяется в процессе автозаполнения.
Именно это свойство относительной адресации используется при копировании формул в другие ячейки.
При записи формул может возникнуть ситуация, когда адрес ячейки, которая используется в формуле, при автозаполнении не должен изменяться. В таком случае нужно использовать абсолютную адресацию ячеек.
Абсолютный адрес указывает на ячейку на основе ее фиксированного положения на рабочем листе. Признаком абсолютного адреса является значок $, который записывается перед номерами столбика и строки (например, $А$10).
При автозаполнении формулы с абсолютным адресом формула не будет изменяться — независимо от того, как было осуществлено автозаполнение. Для более быстрого преобразования относительного адреса в абсолютный при построении формулы нужно после ввода имени ячейки нажать клавишу F4.
А теперь давайте переделаем наш пример. Представьте себе, что вы не знаете, сколько людей планируется на вашей вечеринке, и вы составили табличку из расчета на одного человека.
Сбоку в ячейке F1 укажите предполагаемое количество гостей. Теперь вы решили подсчитать затраты. Если вы будете действовать старым способом создания формул, то уже очень скоро убедитесь, что на чипсы вам нужно … 0 руб.
Дело в том, что при автозаполнении формулы адрес ячейки F1 стал меняться, в ячейке F2 ничего не записано, то есть ноль, поэтому и появились нули. При использовании ячейки F1 нужно применить абсолютную адресацию ячеек, то есть ваша правильная формула должна иметь вид B1*C1*$F$1.
Выполнив автозаполнение, вы убедитесь, что значение $F$1 осталось в таком же виде во всех формулах.
При работе в редакторе можно использовать и смешанные адреса, которые содержат как относительную, так и абсолютную форму записи адреса. Клавиша F4 позволяет превратить абсолютный адрес в смешанный. То есть нажатие клавиши F4 на ячейке изменяет адрес циклически — сначала на абсолютный, потом на смешанный. В смешанном адресе символ $ указывает ту часть адреса, которая не будет изменяться. В адресе А$1 при автозаполнении будет изменяться номер столбика, а номер строки будет оставаться неизменным.
При построении сложных таблиц иногда бывает необходимо использовать в формуле ячейку, расположенную на другом рабочем листе. Для того, чтобы ввести в некоторую ячейку рабочего листа ссылку на ячейку другого рабочего листа, нужно в ячейке, где должна находиться формула, ввести знак =, перейти на нужный рабочий лист, выбрать нужную ячейку, кликнуть на ней мышкой и нажать клавишу Enter. Будет активизирован предшествующий рабочий лист, и в ячейке появится формула
= имя листа! адрес ячейки
Часть формулы, которая содержит ссылку на другой рабочий лист отделена символом ! от ссылки на ячейку.
При необходимости эту формулу можно изменить. Для этого нужно активизировать ячейку, нажать клавишу F2 и внести необходимые изменения.
Ссылка на ячейку другой рабочей книги выполняется аналогично. В этом случае нужно открыть обе рабочие книги и упорядочить их определенным способом в окне Excel, воспользовавшись пунктами меню Окно. Во время ввода формулы в первой рабочей книге нужно активизировать окно второй рабочей книги и выбрать ячейку, которую нужно использовать в формуле. При этом в формуле с использованием такой ссылки имя рабочей книги будет записано в квадратных скобках.
Подытожим.
Основные правила работы с формулами в редакторе Excel состоят в следующем:
— Если нужно выполнить вычисления по одной формуле для некоторого диапазона значений, то нужно построить формулу, применив относительный адрес для первого набора числовых данных, а потом выполнить автозаполнение этой формулы на весь диапазон значений.
— Если нужно построить формулу, которая бы работала с конкретным значением некоторой ячейки, то нужно построить формулу, в которой использовать абсолютный адрес ячейки, и тоже выполнить автозаполнение формулы на весь диапазон значений.
51. сылки в MS Excel бывают трех типов:
1. Относительные [F4]
2. Абсолютные [$F$2]
3. Смешанные [F$2 или $F2]
Значок “$” используется здесь только для обозначения типа ссылки и к знаку “доллара” отношения не имеет.
Разница между ними становится видна при использовании маркера автозаполнения.
Относительные ссылки
Характеризуются смещением, относительно места ввода самой ссылки.
Ставим знак «=» и указываем мышкой или клавиатурой какую-то ячейку на текущем листе – Excel после знака «равно» подставит относительную ссылку. Получится что-нибудь вроде «=F7».
Теперь попробуйте сделать ячейку со ссылкой активной и потяните за черный пряугольник выделения ячейки вниз так, чтобы переместить введенную формулу на нижнюю ячейку. Проверьте, какая формула у Вас сейчас? Она будет уже «=F8». Так произошло потому, что относительные ссылки характеризуются расстоянием, которое всегда остается одинаковым:
Относительная ссылка в формате R1C1 будет выглядеть так: «=R[2]C[1]».
Абсолютные ссылки
А теперь давайте вернем все в исходную позицию и после знака «=» напишем абсолютную ссылку: «=$F$7». Теперь также передвинем содержимое ячейки на одну строку вниз и проверим ссылку.
Она останется прежней: «=$F$7». Так происходит потому, что абсолютные ссылки характеризуются не постоянным расстоянием от ссылки до ячейки, на которую она ссылается, а наоборот: где бы ни стояла ссылка и куда бы ее не двигали, она всегда будет ссылаться на одну и ту же ячейку. Абсолютная ссылка характеризуется ссылкой на конкретную постоянную ячейку. Если же эту ячейку переместить, абсолютная ссылка тоже изменится – теперь она будет постоянно ссылаться на новое местоположение ячейки.
Абсолютная ссылка в формате R1C1 будет выглядеть так: «=R3C2». То есть без квадратных скобок.
Смешанные ссылки
В них нет ничего сложного, если Вы поняли принцип построения «абсолютных» и «относительных» ссылок.
Смешанные ссылки могут быть двух видов: с постоянной по строке [F$7], или постоянной по столбцу [$F7].
Тот параметр формулы, у которого стоит знак «$» будет оставаться постоянным. Второй знак будет изменяться, сообразно маркеру автозаполнения.
52-55.
Сведения о диаграммах
Диаграммы используются для представления рядов числовых данных в графическом формате, который упрощает понимание большого объема информации и отношений между различными рядами данных.
Чтобы создать диаграмму в Excel, сначала введите числовые данные на лист. Затем можно построить на их основе диаграмму, выбрав нужный тип диаграммы на вкладке Вставитьв группе Диаграммы.
Данные листа
Диаграмма, созданная на основе данных листа
Excel поддерживает различные типы диаграмм, что позволяет представлять данные наиболее понятным для той или иной аудитории способом. При создании новой или изменении существующей диаграммы можно выбрать один из разнообразных типов (например, гистограмму или круговую диаграмму) и подтипов (например, гистограмму с накоплением или объемную круговую диаграмму). Совместив в одной диаграмме разные типы, можно создать смешанную диаграмму.
Пример смешанной диаграммы, в которой сочетаются гистограмма и график.
Дополнительные сведения о типах диаграмм, поддерживаемых в Excel, см. в статье Типы диаграмм.
Элементы диаграммы
Диаграмма состоит из различных элементов. Некоторые из них отображаются по умолчанию, другие можно добавлять по мере необходимости. Можно изменить вид элементов диаграммы, переместив их в другое место или изменив их размер либо формат. Также можно удалить элементы диаграммы, которые не требуется отображать.
Область диаграммы.
Область построения диаграммы.
Точки данных для ряда данных, отмеченные на диаграмме.
Оси категорий (горизонтальная) и значений (вертикальная), вдоль которых строится диаграмма.
Легенда диаграммы.
Названия диаграммы и осей, которые можно использовать в диаграмме.
Метки данных, с помощью которых можно обозначать сведения точки данных в ряду данных.
К НАЧАЛУ СТРАНИЦЫ
