Работа с формулами в microsoft excel 2010

Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

• %, ^;
• *, /;
• +, -.

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



Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:



2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.



3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9



2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».



3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.



2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5



3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

Чтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки.

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.



3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.



4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.

Формула, она же функция, – одна из основных составляющих электронных таблиц, создаваемых при помощи программы Microsoft Excel. Разработчики добавили огромное количество разных функций, предназначенных для выполнения как простых, так и сложных расчетов. К тому же пользователю разрешено самостоятельно производить математические операции, что тоже можно назвать своеобразной реализацией формул. Именно о работе с этими компонентами и пойдет речь далее.

Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.

Поиск перечня доступных функций в Excel

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

1. Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.

2. Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».

3. В этом окне переключите категорию на «Полный алфавитный перечень», чтобы в списке ниже отобразились все доступные формулы в Excel, расположенные в алфавитном порядке.

4. Выделите любую строку левой кнопкой мыши и прочитайте краткое описание снизу. В скобках показан синтаксис функции, который необходимо соблюдать во время ее написания, чтобы все аргументы и значения совпадали, а вычисления происходило корректно. Нажмите «Справка по этой функции», если хотите открыть страницу о ней в официальной документации Microsoft.

5. В браузере вы увидите большое количество информации по выбранной формуле как в текстовом, так и в формате видео, что позволит самостоятельно разобраться с принципом ее работы.

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

Вставка функции в таблицу

Теперь давайте разберемся с тем, как в Excel задать формулу, то есть добавить ее в таблицу, обеспечив вычисление определенных значений. Вы можете писать функции как самостоятельно, объявляя их название после знака «=», так и использовать графическое меню, переход к которому осуществляется так, как это было показано выше. В Комьюнити уже есть статья «Как вставить формулу в Excel», поэтому я рекомендую нажать по выделенной ссылке и перейти к прочтению полезного материала.

Использование математических операций в Excel 

Если необходимо выполнить математические действия с ячейками или конкретными числами, в Excel тоже создается формула, поскольку все записи, начинающиеся с «=» в ячейке, считаются функциями. Все знаки для математических операций являются стандартными, то есть  «*»– умножить,  «/» – разделить и так далее. Следует отметить, что для возведения в степень используется знак «^». Вкратце рассмотрим объявление подобных функций.

Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.

После заполнения данных нажмите Enter и ознакомьтесь с результатом. Если синтаксис функции соблюден, в выбранной ячейке появится число, а не уведомление об ошибке.

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

Растягивание функций и обозначение константы

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

Для начала остановимся на растягивании функции. Для этого вам необходимо ввести ее в одной ячейке и убедиться в получении корректного результата. Затем зажмите точку в правом нижнем углу ячейки и проведите вниз.

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

• $В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

• B$2 – неизменна строка.

• $B2 – константа касается только столбца.

Построение графиков функций

Графики функций – тема, косвенно связанная с использованием формул в Excel, поскольку подразумевает не добавление их в таблицу, а непосредственное составление таблицы по формуле, чтобы затем сформировать из нее диаграмму либо линейный график. Сейчас детально останавливаться на этой теме не будем, но если она вас интересует, перейдите по ссылке ниже для прочтения другой моей статьи по этой теме.

Читайте также: Как построить график функции в Excel

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

Excel 2010 для начинающих: Функции,
работа с формулами и диаграммами.

Вступление

В
начале этого материала мы еще немного поговорим о формулах — расскажем, как их
редактировать, поговорим о системе оповещения об ошибках и инструментах
отслеживания ошибок, а так же узнаем, с помощью каких алгоритмов в Excel
осуществляется копирование и перемещение формул. Затем мы познакомимся с еще
одним важнейшим понятием в электронных таблицах – функциями. Напоследок, вы
узнаете, как в MS Excel 2010 можно представлять данные и полученные результаты
в наглядном (графическом) виде, используя диаграммы.

Редактирование
формул и система отслеживания ошибок

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

После
окончания редактирования нажмите клавиши Enter или Tab, после чего Excel
выполнить перерасчет с учетом изменений и отобразит результат.

Довольно
часто случается так, что вы ввели формулу неверно или после удаления
(изменения) содержимого одной из ячеек, на которую ссылается формула,
происходит ошибка в вычислениях. В таком случае  Excel непременно известит вас
об этом. Рядом с клеткой, где содержится ошибочное выражение, появится
восклицательный знак, в желтом ромбе.

Во
многих случаях, приложение не только известит вас о наличие ошибки, но и укажет
на то, что именно сделано не так.

Расшифровка ошибок в
Excel:

·        
##### — результатом выполнения формулы,
использующей значения даты и времени стало отрицательное число или результат
обработки не умещается в ячейке;

·        
#ЗНАЧ! – используется недопустимый тип оператора
или аргумента формулы. Одна из самых распространенных ошибок;

·        
#ДЕЛ/0! – в формуле осуществляется попытка деления
на ноль;

·        
#ИМЯ? – используемое в формуле имя некорректно и
Excel не может его распознать;

·        
#Н/Д – неопределенные данные. Чаще всего эта ошибка
возникает при неправильном определении аргумента функции;

·        
#ССЫЛКА! – формула содержит недопустимую ссылку на
ячейку, например, на ячейку, которая была удалена.

·        
#ЧИСЛО! – результатом вычисления является число,
которое слишком мало или слишком велико, что бы его можно было использовать в
MS Excel. Диапазон отображаемых чисел лежит в промежутке от -10307 до 10307.

·        
#ПУСТО! – в формуле задано пересечение областей,
которые на самом деле не имеют общих ячеек.

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

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

Влияющие ячейки–это ячейки, на которые
ссылаются формулы, а зависимые ячейки, наоборот, содержат формулы,
ссылающиеся на адреса клеток электронной таблицы.

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

Например,
давайте посмотрим как в нашей тестовой таблице, составленной в предыдущих двух
частях, формируется итоговый результат накоплений:

Не
смотря на то, что формула в данной ячейке имеет вид «=H5 – H12», программа
Excel, cпомощью стрелок зависимостей, может показать все значения, которые
учувствуют в вычислении конечного результата. Ведь в клетках H5 и H12 так же
содержаться формулы, имеющие ссылки на другие адреса, которые в свою очередь,
могут содержать как формулы, так и числовые константы.

Чтобы
удалить все стрелки с рабочего листа, в группе Зависимости формул на вкладке
Формулы, нажмите кнопку Убрать стрелки.

Относительные и
абсолютные адреса ячеек

Возможность
копирования формул в Excel из одной ячейки в другую с автоматическим изменением
адресов, содержащихся в них, существует благодаря концепции относительной
адресации. Так что же это такое?

Дело
в том, что Excel понимает адреса ячеек введенных в формулу не как ссылку на их
реальное месторасположение, а как ссылку на их месторасположение относительно
ячейки, в которой находится формула. Поясним на примере.

Например,
ячейка A3, содержит формулу: «=A1+A2». Для Excel это
выражение не означает, что нужно взять значение из ячейки A1 и прибавить к нему
число из ячейки A2. Вместо этого он интерпретирует данную формулу, как «взять
число из ячейки расположенной в том же столбце, но на две строки выше и сложить
его со значением ячейки этого же столбца расположенной выше на одну строку».
При копировании данной формулы в другую ячейку, например D3, принцип
определения адресов ячеек входящих в выражение остается тем же: «взять число из
ячейки расположенной в том же столбце, но на две строки выше и сложить его с…».
Таким образом, после копирования в D3, исходная формула автоматически примет
вид «=D1+D2».

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

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

С
помощью абсолютных ссылок можно дать команду Excel при копировании формулы:

·        
сохранять ссылку на столбец постоянно, но при этом
изменять ссылки на столбцы

·        
изменять ссылки на строки, но сохранять ссылку на
столбец

·        
сохранять постоянными ссылки, как на столбец, так и
на строку.

Чтобы
превратить относительную ссылку в абсолютную или смешанную, необходимо ввести
знак доллара ($) перед той ее частью, которая должна стать абсолютной.

·        
$A$1 – ссылка всегда ссылается на ячейку A1
(абсолютная ссылка);

·        
A$1 – ссылка всегда ссылается на строку 1, а путь к
столбцу может изменяться (смешанная ссылка);

·        
$A1 – ссылка всегда ссылается на столбец A, а путь
к строке может изменяться (смешанная ссылка).

Для
ввода абсолютных и смешанных ссылок используется клавиша «F4». Выделите ячейку
для формулы, введите знак равенства (=) и кликните по клетке, на которую надо
установить абсолютную ссылку. Затем нажмите клавишу F4, после чего перед буквой
столбца и номером строки программа установит знаки доллара ($). Повторные
нажатия на F4 позволяют переходить от одного типа ссылок к другим. Например,
ссылка на E3, будет циклично изменяться на $E$3, E$3, $E3, E3 и так далее. При
желании знаки $ можно вводить вручную.

Функции

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

Например,
определение среднего значения пяти ячеек можно описать формулой: =(A1 + A2 + A3
+ A4 + A5)/5, а можно специальной функцией СРЗНАЧ, которая сократит выражение
до следующего вида: СРЗНАЧ(А1:А5). Как видите, что вместо ввода в формулу всех
адресов ячеек можно использовать определенную функцию, указав ей в качестве
аргумента их диапазон.

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

Надо
отметить, что программа содержит более двухсот функций, способных облегчить
выполнение вычислений различной сложности. Поэтому все функции в Excel 2010
разделены на несколько категорий, группирующих их по типу решаемых задач. Какие
именно эти задачи, становится ясно из названий категорий: Финансовые,
Логические, Текстовые, Математические, Статистические, Аналитические и так
далее.

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

Ввод
функций, как и формул, начинается со знака равенства. После идет имя функции,
в виде аббревиатуры из больших букв, указывающей на ее значение. Затем в
скобках указываются аргументы функции – данные, использующиеся для
получения результата.

В
качестве аргумента может выступать конкретное число, самостоятельная ссылка на
ячейку, целая серия ссылок на значения или ячейки, а так же диапазон ячеек. При
этом у одних функций аргументы – это текст или числа, у других – время и даты.

Многие
функции могут иметь сразу несколько аргументов. В таком случае, каждый из них
отделяется от следующего точкой с запятой. Например, функция =ПРОИЗВЕД(7; A1; 6; B2) считает произведение четырёх  разных
чисел, указанных в скобках, и соответственно содержит четыре аргумента. При
этом в нашем случае одни аргументы указаны явно, а другие, являются значениями
определенных ячеек.

Так
же в качестве аргумента можно использовать другую функцию, которая в этом
случае называется вложенной. Например, функция =СУММ(A1:А5; СРЗНАЧ(В5:В10)) суммирует значения ячеек находящихся в диапазоне
от А1 до А5, а так же среднее значение чисел, размещенных в клетках В5, В6, В7,
В8, В9 и В10.

У
некоторых простых функций аргументов может не быть вовсе. Так, с помощью
функции =ТДАТА() можно получить текущие время и дату, не используя никаких
аргументов.

Далеко
не все функции в Excel имеют простое определение, как функция
СУММ, осуществляющая суммирование выбранных значений. Некоторые из них имеют
сложное синтаксическое написание, а так же требуют много аргументов, которые к
тому же должны быть правильных типов. Чем сложнее функция, тем сложнее ее
правильное составление. И разработчики это учли, включив в свои электронные
таблицы помощника по составлению функций для пользователей – Мастер функций.

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

Так
же кнопку Вставить функцию вы найдете на ленте сверху в группе Библиотека
функций во вкладке Формулы. Еще одним способом вызова мастера
функций является сочетание клавиш Shift+F3.

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

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

Сделав
необходимый выбор, щелкните по кнопке ОК, после чего появится окно Аргументы
функции.

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

Ссылки
на ячейки (или их диапазон) в поля для ввода аргументов можно вводить как
вручную, так и используя мышь, что гораздо удобнее. Для этого просто щелкайте
левой кнопкой по нужным клеткам на открытом листе или обведите их необходимый
диапазон. Все значения будут автоматически подставлены в текущее поле ввода.

Если
диалоговое окно Аргументы функции мешает вводу необходимых данных, перекрывая
собой рабочий лист, его можно на время уменьшить, нажав на кнопку в правой
части поля ввода аргументов.

Повторное
нажатие на нее же приведет к восстановлению обычного размера.

После
ввода всех необходимых значений, остается кликнуть по кнопке ОК и в выбранной
ячейке появится результат вычисления.

Диаграммы

Довольно
часто числа в таблице, даже отсортированные должным образом, не позволяют
составить полную картину по итогам вычислений. Что бы получить наглядное
представление результатов, в MS Excel существует
возможность построения диаграмм различных типов. Это может быть как обычная
гистограмма или график, так и лепестковая, круговая или экзотическая
пузырьковая диаграмма. Более того, в программе существует возможность создавать
комбинированные диаграммы из различных типов, сохраняя их в качестве шаблона
для дальнейшего использования.

Диаграмму
в Excel можно разместить либо на том же листе, где уже
находится таблица, и в таком случае она называется «внедренной», либо на
отдельном листе, который станет называться «лист диаграммы».

В
качестве примера, попробуем представить в наглядном виде данные ежемесячных
расходов, указанных в таблице, созданной нами в предыдущих двух частях
материалов «Excel 2010 для начинающих».

Для
создания диаграммы на основе табличных данных сначала выделите те ячейки,
информация из которых должна быть представлена в графическом виде. При этом
внешний вид диаграммы зависит от типа выбранных данных, которые должны
находиться в столбцах или строках. Заголовки столбцов должны находиться над
значениями, а заголовки строк – слева от них. В нашем случае, выделяем клетки
содержащие названия месяцев, статей расходов их значения.

Затем,
на ленте во вкладке Вставка в группе Диаграммы выберите нужный
тип и вид диаграммы. Что бы увидеть краткое описание того или иного типа и вида
диаграмм, необходимо задержать на нем указатель мыши.

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

В
нашем примере давайте выберем объемную цилиндрическую гистограмму с накоплением
и нажмем кнопку ОК, после чего на рабочем листе появится диаграмма.

Так
же обратите внимание, на появление дополнительной закладки на ленте Работа с
диаграммами, содержащая еще три вкладки: Конструктор, Макет и
Формат.

На
вкладке Конструктор можно изменить тип диаграммы, поменять местами
строки и столбцы, добавить или удалить данные, выбрать ее макет и стиль, а так
же переместить диаграмму на другой лист или другую вкладку книги.

На
вкладке Макет располагаются команды, позволяющие добавлять или удалять
различные элементы диаграммы, которые можно легко форматировать с помощью
закладки Формат.

Вкладка
Работа с диаграммами появляется автоматически всякий раз, когда вы
выделяете диаграмму и исчезает, когда происходит работа с другими элементами
документа.

Форматирование и
изменение диаграмм

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

Что
быстро изменить тип диаграммы на вкладке Конструктор в группе Тип,
размещающейся слева, щелкните кнопку Изменить тип диаграммы. В
открывшемся окне слева выберите сначала подходящий тип диаграммы, затем ее
подтип и нажмите кнопку ОК. Диаграмма будет автоматически перестроена.
Старайтесь подбирать такой тип диаграммы, который наиболее точно и наглядно
будет демонстрировать цель ваших вычислений.

Если
данные на диаграмме отображаются не должным образом, попробуйте поменять
местами отображения строк и столбцов, нажав на кнопку, Строка/столбец в
группе Данные на вкладке Конструктор.

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

И
все же не всегда созданная или отформатированная диаграмма с помощью встроенных
макетов и стилей удовлетворяет пользователей целиком и полностью. Слишком
большой размер шрифтов, очень много места занимает легенда, не в том месте
находятся подписи данных или сама диаграмма слишком маленькая. Словом, нет
предела совершенству, и в Excel, все, что вам не нравится,
можно исправить самостоятельно на свой «вкус» и «цвет». Дело в том, что
диаграмма состоит из нескольких основных блоков, которые вы можете форматировать.

·        
Область диаграммы –
основное окно, где размещаются все остальные компоненты диаграммы. Наведя
курсор мыши на эту область (появляется черное перекрестье), и зажав левую
кнопку мыши, можно перетащить диаграмму в любую часть листа. Если же вы хотите
изменить размер диаграммы, то наведите курсор мыши на любой из углов или
середину стороны ее рамки, и когда указатель примет форму двухсторонней
стрелочки, потяните его в нужном направлении.

·        
Область построения диаграммы – включает в себя вертикальную и горизонтальную оси, ряд данных, а так
же основные и дополнительные линии сетки (стены).

·        
Ряд данных – данные,
представленные в графическом виде (диаграмма, гистограмма, график и т.д.).
Могут иметь подписи данных, отображающие точные цифровые показатели строк или
рядов диаграммы.

·        
Ось значений и ось категорий – числовые параметры, расположенные вдоль вертикальной и
горизонтальной линий, ориентируясь на которые можно оценить данные диаграммы.
Могут иметь собственные подписи делений и заголовки.

·        
Линии сетки – наглядно
представляют значения осей и размещаются на боковых панелях, называемых
стенами.

·        
Легенда – расшифровка
значений рядов или строк.

Любому
пользователю Excel предоставляется возможность
самостоятельно изменять стили и художественное представление каждого из
вышеперечисленных компонентов диаграммы. К вашим услугам выбор цвета заливки,
стиля границ, толщины линий, наложение объема, теней, свечения и сглаживания на
выбранные объекты. В любой момент, можно изменить общий размер диаграммы,
увеличить/уменьшить любую ее область, например, увеличить саму диаграмму и
уменьшить легенду, или вообще отменить отображение ненужных элементов. Можно
изменить угол наклона диаграммы, повернуть ее, сделать объемной или плоской.
Одним словом, MS Excel 2010 содержит инструменты,
позволяющие придать диаграмме собственноручно наиболее удобный для восприятия
образ.

Для
изменения компонентов диаграммы воспользуйтесь вкладкой Макет,
расположенной на ленте в области Работа с диаграммами.

Здесь
вы найдете команды с названиями всех частей диаграммы, а щелкнув по
соответствующим кнопкам, можно перейти к их форматированию. Есть и другие,
более простые способы изменения компонентов диаграмм. Например, достаточно
просто навести курсор мыши на нужный объект, и щелкнуть по нему два раза, после
чего сразу откроется окно форматирования выбранного элемента. Так же вы можете
воспользоваться командами контекстного меню, которое вызывается кликом правой
кнопки мыши по нужному компоненту.

Самое
время преобразовать внешний вид нашей тестовой диаграммы, воспользовавшись
разными способами. Сначала несколько увеличим размер диаграммы. Для этого
установите курсор мыши в любой угол области диаграммы и после изменения его
вида на двухстороннюю стрелочку потащите указатель в нужном направлении
(направлениях).

Теперь
отредактируем внешний вид рядов данных. Щелкните два раза левой кнопкой мыши по
любой цветной цилиндрической области диаграммы (каждый ряд отмечен своим
уникальным цветом), после чего откроется одноименное окно с настройками.

Здесь,
слева в столбце, отображается список параметров, которые можно собственноручно
изменять для данного компонента диаграммы, а справа – область редактирования с
текущими значениями.

Здесь
вы можете выбрать различные параметры отображения ряда, включая тип фигуры,
зазоры между ними, заливку области, цвет границ и так далее.  Попробуйте
самостоятельно в каждом из разделов менять параметры и увидите, как это будет
влиять на внешний вид диаграммы.

В
итоге, в окне Формат ряда данных мы убрали фронтальный зазор, а боковой
сделали равным 20%, добавили тень снаружи и немного объема сверху.

Теперь
щелкнем правой кнопкой мыши на любой цветной цилиндрической области и в
открывшемся контекстном меню выберем пункт Добавить подписи данных.
После этого на диаграмме появятся ежемесячные значения по выбранной статье
расходов. Тоже самое проделайте со всеми оставшимися рядами. Кстати, сами
подписи данных впоследствии тоже можно форматировать: изменять размер шрифта,
цвет, его начертание, менять месторасположение значений и так далее. Для этого
так же используйте контекстное меню, кликнув правой кнопкой мыши
непосредственно по самому значению, и выберите команду Формат подписи данных.

Для
форматирования осей давайте воспользуемся вкладкой Макет на ленте
сверху. В группе Оси щелкните по одноименной кнопке, выберите нужную
ось, а затем пункт Дополнительные параметры основной
горизонтальной/вертикальной оси.

Принцип
расположения управляющих элементов в открывшемся окне Формат оси мало
чем отличается от предыдущих — тот же столбец с параметрами слева и зоной
изменяемых значений справа. Здесь мы не стали особо ничего менять, лишь добавив
светло-серые тени к подписям значений, как вертикальной, так и горизонтальной
осей.

Наконец,
давайте добавим заголовок диаграммы, щелкнув на вкладке Макет в группе Подписи
по кнопке Название диаграммы. Далее уменьшим область легенды, увеличим
область построения и посмотрим, что у нас получилось:

Как
видите, встроенные в Excel инструменты форматирования диаграмм действительно
дают широкие возможности пользователям и визуальное представление табличных
данных на этом рисунке разительно отличается от первоначального варианта.