Всегда начинайте запись с курсором в A1. Форматирование и изменение диаграмм

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

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

Ячейки и их содержимое

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

Ячейка

Каждый прямоугольник на листе называется ячейкой. Ячейка – это пресечение строки и столбца.

У каждой ячейки есть имя или адрес ячейки , который образуется на основе имен столбца и строки, пресечением которых образована ячейка. Адрес выбранной ячейки показывается в поле Имя. Здесь вы можете увидеть, что выбрана ячейка C5 .

Также вы можете одновременно выделить несколько ячеек. Группа ячеек называется диапазоном ячеек . Если вы ссылаетесь не на одну ячейку, а на диапазон, то используйте запись из адресов первой и последней ячеек, разделенных двоеточием. Например, диапазон ячеек, включающий ячейки A1, A2, A3, A4 и A5 будет записан как A1:A5 .

Чтобы выбрать ячейку:

Вы также можете перемещаться между ячейками и выделять их с помощью клавиш со стрелками на клавиатуре.

Чтобы выбрать несколько ячеек:

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

Текст
Ячейки могут содержать буквы, цифры и даты.

Атрибуты форматирования
Ячейки могут содержать атрибуты форматирования, которые меняют способ отображения букв, цифр и дат. Например, даты могут быть отформатированы как ММ/ДД/ГГГГ или Месяц/Д/ГГГГ.

Комментарии
Ячейки могут содержать комментарии от нескольких рецензентов.

Формулы и функции
Ячейки могут содержать формулы и функции, которые вычисляют значения ячейки. Например, СУММ (ячейка 1, ячейка 2…) – это формула, которая суммирует значения нескольких ячеек.

Чтобы ввести содержимое:

1. Кликните по ячейке, чтобы выделить ее.
2. Введите содержимое в выбранную ячейку с помощью клавиатуры. Оно появится в ячейке и строке формул. Вы также можете вносить и редактировать содержимое в строке формул.

Чтобы удалить содержимое ячейки:

1. Выделите нужную ячейку.
2. Кликните по команде Очистить на ленте. Появится диалоговое окно.
3. Выберите Очистить содержимое.

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

Чтобы удалить ячейки:

1. Выделите ячейки, которые хотите удалить.
2. На ленте выберите команду Удалить.

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

Чтобы скопировать и вставить содержимое ячеек:

Чтобы вырезать и вставить содержимое ячеек:

Для доступа к параметрам вставки:

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

Чтобы получить доступ к командам форматирования:

1. Выделите ячейки, которые нужно отформатировать.
2. Кликните правой кнопкой мыши по выделенным ячейкам. Появится диалоговое окно, где вы сможете легко найти множество команд, которые также есть на ленте.

Чтобы переместить ячейки:

Чтобы использовать маркер заполнения для заполнения ячеек:

Практикуйте!

1. Откройте существующую книгу Excel 2010.
2. Выберите ячейку D3 , и обратите внимание, как выглядит его адрес в поле Имя и его содержимое в строке формул.
3. Попробуйте написать текст и цифры.
4. Используйте маркер заполнения, чтобы заполнить прилегающие ячейки по вертикали и по горизонтали.
5. Вырежьте ячейки и вставьте их в другое место.
6. Удалите ячейку и обратите внимание, как ячейки под ним сместятся вверх.
7. Попробуйте перетащить ячейки.

Эту программу можно использовать в следующих целях:

· Бухгалтерский учет. Программа имеет достаточно мощные функции для вычисления финансовой и бухгалтерской отчетности.

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

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

· Планирование. Программа является отличным средством для создания профессиональных планов.

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

Окно программы состоит из:

· Панели вкладок (лента), которая расположена сверху окна программы. На ленте расположены определенные наборы команд (функции программы), которые собраны в команды и группы (по умолчанию отображена лента,содержащая 7 вкладок, при запуске программы активной является вкладка «Главная»). При переходе к новым объектам работы (диаграммы формулы),появляются новые вкладки.

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

· Ниже ленты вкладок расположены строка имени и строка формул. В строке имени будет отображаться имя активного объекта или ячейки, а в строке формул будет отображаться формула активной ячейки.

Строка имени.

Строка формул.

· Основную часть окна занимает рабочее поле.

· В правом нижнем окне программы находятся команды, которые служат для наиболее выгодного просмотра документа.

Книга в Excel 2010

Книга состоит из:

3 листов - это по стандарту, максимально 255 листов. Каждый лист состоит из ячеек, которые образуются пересечением строк и столбцов. Строки пронумерованы арабскими цифрами (1,2,3), а столбцы большими английскими буквами (А, B, C).

Редактирование таблиц в Excel 2010

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

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

Создания формул в Excel 2010

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

Как построить функцию в Excel 2010 (как сделать расчеты в Excel 2010)

Рабочая книга

Рабочая книга состоит из листов, имена которых (Лист1, Лист2, …) выведены на ярлыках в нижней части окна рабочей книги. Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги.

Рабочий лист представляет собой таблицу, состоящую из 16 385 столбцов и огромного количества строк (1 048 577).

Столбцы именуются латинскими буквами, а строки - цифрами.

Рисунок 1. Расположение строк и столбцов в программе MS Excel

Выделение элементов таблицы

Ячейка - это основной элемент электронной таблицы только в ней может содержаться какая-либо информация (текст, значения, формулы).

Одна из ячеек таблицы всегда является активной. Активная ячейка выделяется рамкой.

Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца.

Например, если ячейка находится в столбце E и строке 8, то она имеет адрес E8.

Рисунок 2 Адрес ячейки

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

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

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

Чтобы выделить целый столбец или строку таблицы, необходимо щелкнуть мышью на его имени.

Для выделения нескольких столбцов или строк следует щелкнуть на имени первого столбца или строки и растянуть выделение на всю область.

Заполнение ячеек

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

Чтобы отредактировать данные в ячейке, необходимо сделать ячейку активной и нажать клавишу F2 или дважды щелкнуть в ячейке мышью .

Операции со строками и столбцами

Перед выполнением этих команд следует выделить соответствующую строку или столбец.

Для добавления выделяется столбец/строка, перед которым вы собираетесь вставить столбец или строку.

Вставка пустой строки или пустого столбца в таблицу, удаление строк или столбцов, осуществляется двумя способами.

1-й способ: вызвать контекстное меню → пункт «вставить /удалить».

2-й способ: Вкладка «Главная» → группа элементов «Ячейки» → команды «вставить /удалить».

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

Нажмите правую кнопку мыши. Появится список команд.

Выберите команду «Добавить». Появится новый столбец.

Редактирование ячеек

Команды редактирования: «Вырезать» «Копировать» и «Вставить», и метод «Перетащить» , совпадают со способами описанными в статье «Фрагмент текста, работа с фрагментом текста »

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

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

Основы работы с листами

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

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

Причисленные действия можно выполнить с помощью «Контекстного меню» перед вызовам меню, навести курсор на имя соответствующего листа.

Команда «переименовать»

Рисунок ниже иллюстрирует вызов команды «переименовать» «Лист1», с помощью контекстного меню. Другим способом является двойной клик на названии листа.

Рисунок 3 Команда «переименовать»

Текст имени листа станет выделен черным. Остаётся ввести новое имя листа и кликнуть вне ярлычка (например: Первый лист).

Создать лист

Чтобы создать новый лист, можно воспользоваться командой «Вставить» из контекстного меню или иконкой «Вставить лист» (Ниже иллюстрация расположения иконки «Вставить лист»).

Рисунок 4 Расположение иконки «Вставить лист»

Используя первый способ добавления листа (команда «Вставить»), надо выбрать пункт «Лист» , в окне «Вставка » (смотри рисунок ниже).

Рисунок 5 Окно «Вставка»

Удалить лист

Чтобы удалить лист, можно воспользоваться командой «Удалить» из контекстного меню. Листы могут быть удалены из книги, даже если они содержат данные. На рисунке ниже демонстрируется удаление листа с именем «Лист2»

Рисунок 6 Пункт «Удалить» в контекстном меню

Копировать лист

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

Рисунок 7 Диалоговое окно Переместить или скопировать

После нажатия ОК, появится новый лист с именем скопированного файла и цифрой два в скобках (например : Первый лист(2) ).

→ Ctrl + нажать на левую клавишу мыши → переместить в нужное место.

Переместить

Например Лист2 перемещу в начало списка, перед Первый лист (2).

Первый способ. Контекстное меню → пункт Переместить или скопировать → в окне выбрать имя листа, перед который необходимо переместить лист и нажать ОК (рисунок ниже).

Рисунок 8 Диалоговое окно Переместить или скопировать

Второй способ. Навести курсор на нужный лист → зажать левую клавишу мыши → переместить в нужное место.

Оформление ярлыка листа

Удобный способ навигации среди листов - Перекрасит ярлык листа. Выполняется просто: контекстное меню → Цвет ярлычка → среди палитры выбрать подходящий цвет. (Смотри рисунок 9)

Рисунок 9 Оформление ярлыка листа

Операции с рабочей книгой

Большинство операций с рабочей книгой похожи на те, что применяются при работе с документами Word.

Для завершения работы с Excel необходимо закрыть окно программы
(щелкнуть кнопку X справа вверху окна или нажать комбинацию клавиш Alt+F4).

Заключение

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

Если вы никогда раньше не использовали табличный процессор для создания документов, советуем прочитать наше руководство Эксель (Excel) для чайников.

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

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

Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Кстати, чтобы эффективнее работать с таблицами Exel можете ознакомиться с нашим материалом .

Работа с ячейками. Заполнение и форматирование

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

Файл Эксель состоит из одного или нескольких листов, разграфленных на мелкие ячейки.

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

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

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

Рис. 1 – пример заполнения ячеек

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

При нажатии на ячейку в верхней части окна появится поле с ее адресом, названием и формулой (если клеточка учуствует в каких-либо расчетах).

Выберем ячейку «Доля акций» . Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое.

Формул у этой клетки нет, поэтому они не показываются.

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

Кликните на клеточку правой клавишей манипулятора.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

Сортировка данных

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

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

Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

• Выделить таблицу или блок информации;
• Открыть кладку «Данные» ;
• Кликнуть на иконку «Сортировка» ;

Рис. 3 – вкладка «Данные»

• В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь ).
• Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
• Подтвердите действие, нажав на «ОК» .

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

Математические расчеты

Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы. К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?

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

Затем нажмите на значок функции f(x), как показано на рисунке ниже.

В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее.

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

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ» .

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2» . Выберите первое поле и кликните мышкой на ячейку с цифрой «2».

Её адрес запишется в строку аргумента.

Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно.

Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3» , «Число 4» и так далее.

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

Рис. 7 – результат выполнения подсчетов

Создание таблиц

В экселевских таблицах можно хранить любые данные.

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

Имеют преимущество перед аналогичной опцией в и других офисных программах.

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

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

Чтобы создать таблицу, следуйте инструкции:

• Откройте вкладку «Вставка» . В левой части панели опций выберите пункт «Таблица» . Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
• С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
• Нажмите ОК , чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

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

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

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

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

Вступление

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

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

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

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

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

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

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

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

• ##### - результатом выполнения формулы, использующей значения даты и времени стало отрицательное число или результат обработки не умещается в ячейке;
• #ЗНАЧ! - используется недопустимый тип оператора или аргумента формулы. Одна из самых распространенных ошибок;
• #ДЕЛ/0! - в формуле осуществляется попытка деления на ноль;
• #ИМЯ? - используемое в формуле имя некорректно и Excel не может его распознать;
• #Н/Д - неопределенные данные. Чаще всего эта ошибка возникает при неправильном определении аргумента функции;
• #ССЫЛКА! - формула содержит недопустимую ссылку на ячейку, например, на ячейку, которая была удалена.
• #ЧИСЛО! - результатом вычисления является число, которое слишком мало или слишком велико, что бы его можно было использовать в MS Excel. Диапазон отображаемых чисел лежит в промежутке от -10 307 до 10 307 .
• #ПУСТО! - в формуле задано пересечение областей, которые на самом деле не имеют общих ячеек.

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

Иногда, когда данных в таблице много, а формулы содержат большое количество ссылок на различные ячейки, то при проверке выражения на правильность или поиска источника ошибки могут возникнуть серьезные трудности. Что бы облегчить работу пользователя в таких ситуациях, в 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.

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

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

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

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

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

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

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

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

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

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

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

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

Диаграммы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Спарклайны или инфокривые

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

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

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

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

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

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

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

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

Теперь попробуйте самостоятельно достроить спарклайны по расходным статьям, только в виде гистограмм, а для общего баланса, как нельзя кстати подойдет тип выигрыш/проигрыш.

Теперь, после добавления спарклайнов, наша сводная таблица приняла вот такой интересный вид:

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

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

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

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

Заключение

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