• знак «меньше или равно» – «<=»;

• знак «не равно» – «<>».

Оператор объединения текста

Для этой цели используется специальный символ «&» (амперсанд). При помощи его можно соединить различные фрагменты в одно целое – тот же принцип, что и с функцией «СЦЕПИТЬ». Приведем несколько примеров:

1. Если вы хотите объединить текст в ячейках, то нужно использовать следующий код.

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

1. Объединять можно не только ячейки, но и обычные символы.

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

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

Для определения ссылок можно использовать следующие операторы:

• для того чтобы создать простую ссылку на нужный диапазон ячеек, достаточно указать первую и последнюю клетку этой области, а между ними символ «:»;
• для объединения ссылок используется знак «;»;
• если необходимо определить клетки, которые находятся на пересечении нескольких диапазонов, то между ссылками ставится «пробел». В данном случае выведется значение клетки «C7».

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

Использование ссылок

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

Простые ссылки A1

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

• столбцов – от A до XFD (не больше 16384);
• строк – от 1 до 1048576.

Приведем несколько примеров:

• ячейка на пересечении строки 5 и столбца B – «B5»;
• диапазон ячеек в столбце B начиная с 5 по 25 строку – «B5:B25»;
• диапазон ячеек в строке 5 начиная со столбца B до F – «B5:F5»;
• все ячейки в строке 10 – «10:10»;
• все ячейки в строках с 10 по 15 – «10:15»;
• все клетки в столбце B – «B:B»;
• все клетки в столбцах с B по K – «B:K»;
• диапазон ячеек с B2 по F5 – «B2-F5».

Иногда в формулах используется информация с других листов. Работает это следующим образом.

На втором листе указаны следующие данные.

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

Абсолютные и относительные ссылки

Редактор Эксель работает с тремя видами ссылок:

• абсолютные;
• относительные;
• смешанные.

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

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

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

1. Введём формулу для расчета суммы первой колонки.

1. Нажмите на горячие клавиши Ctrl +C . Для того чтобы перенести формулу на соседнюю клетку, необходимо перейти туда и нажать на Ctrl +V .

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

1. Теперь посмотрите на новые формулы. Изменение индекса столбца произошло автоматически.

Если вы хотите, чтобы при переносе формул все ссылки сохранялись (то есть чтобы они не менялись в автоматическом режиме), нужно использовать абсолютные адреса. Они указываются в виде «$B$2».

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

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

• $D1, $F5, $G3 – для фиксации столбцов;
• D$1, F$5, G$3 – для фиксации строк.

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

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

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

1. Перенесем эту формулу в другую ячейку. Желательно не на следующую и на другой строке. Теперь вы видим, что новое выражение содержит ту же строчку (4), но другую букву, поскольку только она была относительной.

Трёхмерные ссылки

Под понятие «трёхмерные» попадают те адреса, в которых указывается диапазон листов. Пример формулы выглядит следующим образом.

В данном случае результат будет соответствовать сумме всех ячеек «A5» на всех листах, начиная с 1 по 4. При составлении таких выражений необходимо придерживаться следующих условий:

• в массивах нельзя использовать подобные ссылки;
• трехмерные выражения запрещается использовать там, где есть пересечение ячеек (например, оператор «пробел»);
• при создании формул с трехмерными адресами можно использовать следующие функции: СРЗНАЧ, СТАНДОТКЛОНА, СТАНДОТКЛОН.В, СРЗНАЧА, СТАНДОТКЛОНПА, СТАНДОТКЛОН.Г, СУММ, СЧЁТЗ, СЧЁТ, МИН, МАКС, МИНА, МАКСА, ДИСПР, ПРОИЗВЕД, ДИСППА, ДИСП.В и ДИСПА.

Если нарушить эти правила, то вы увидите какую-нибудь ошибку.

Ссылки формата R1C1

Данный тип ссылок от «A1» отличается тем, что номер задается не только строкам, но и столбцам. Разработчики решили заменить обычный вид на этот вариант для удобства в макросах, но их можно использовать где угодно. Приведем несколько примеров таких адресов:

• R10C10 – абсолютная ссылка на клетку, которая расположена на десятой строке десятого столбца;
• R – абсолютная ссылка на текущую (в которой указывается формула) ссылку;
• R[-2] – относительная ссылка на строчку, которая расположена на две позиции выше этой;
• R[-3]C – относительная ссылка на клетку, которая расположена на три позиции выше в текущем столбце (где вы решили прописать формулу);
• RC – относительная ссылка на клетку, которая распложена на пять клеток правее и пять строк ниже текущей.

Использование имён

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

Имена вы можете использовать для умножения, деления, сложения, вычитания, расчета процентов, коэффициентов, отклонения, округления, НДС, ипотеки, кредита, сметы, табелей, различных бланков, скидки, зарплаты, стажа, аннуитетного платежа, работы с формулами «ВПР», «ВСД», «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» и так далее. То есть можете делать, что угодно.

Главным условием можно назвать только одно – вы должны заранее определить это имя. Иначе Эксель о нём ничего знать не будет. Делается это следующим образом.

1. Выделите какой-нибудь столбец.
2. Вызовите контекстное меню.
3. Выберите пункт «Присвоить имя».

1. Укажите желаемое имя этого объекта. При этом нужно придерживаться следующих правил.

1. Для сохранения нажмите на кнопку «OK».

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

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

А если попробовать вместо адреса «D4:D9» вставить наше имя, то вы увидите подсказку. Достаточно написать несколько знаков, и вы увидите, что подходит (из базы имён) больше всего.

В нашем случае всё просто – «столбец_3». А представьте, что у вас таких имён будет большое множество. Все наизусть вы запомнить не сможете.

Использование функций

В редакторе Excel вставить функцию можно несколькими способами:

• вручную;
• при помощи панели инструментов;
• при помощи окна «Вставка функции».

Рассмотрим каждый метод более внимательно.

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

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

В этом случае необходимо:

1. Перейти на вкладку «Формулы».
2. Кликнуть на какую-нибудь библиотеку.
3. Выбрать нужную функцию.

1. Сразу после этого появится окно «Аргументы и функции» с уже выбранной функцией. Вам остается только проставить аргументы и сохранить формулу при помощи кнопки «OK».

Мастер подстановки

Применить его можно следующим образом:

1. Сделайте активной любую ячейку.
2. Нажмите на иконку «Fx» или выполните сочетание клавиш SHIFT +F3 .

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

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

1. Выберите какую-нибудь функцию из предложенного списка.
2. Чтобы продолжить, нужно кликнуть на кнопку «OK».

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

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

Использование вложенных функций

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

Затем придерживайтесь следующей инструкции:

1. Кликните на первую ячейку. Вызовите окно «Вставка функции». Выберите функцию «Если». Для вставки нажмите на «OK».

1. Затем нужно будет составить какое-нибудь логическое выражение. Его необходимо записать в первое поле. Например, можно сложить значения трех ячеек в одной строке и проверить, будет ли сумма больше 10. В случае «истины» указываем текст «Больше 10». Для ложного результата – «Меньше 10». Затем для возврата в рабочее пространство нажимаем на «OK».

1. В итоге мы видим следующее – редактор выдал, что сумма ячеек в третьей строке меньше 10. И это правильно. Значит, наш код работает.

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

1. В итоге редактор пересчитывает наше выражение для каждой строки.

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

Сделать это можно несколькими способами: использовать строку формул или специальный мастер. В первом случае всё просто – кликаете в специальное поле и вручную вводите нужные изменения. Но писать там не совсем удобно.

Единственное, что вы можете сделать, это увеличить поле для ввода. Для этого достаточно кликнуть на указанную иконку или нажать на сочетание клавиш Ctrl +Shift +U .

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

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

1. Сделайте активной клетку с формулой. Нажмите на иконку «Fx».

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

1. Для сохранения внесенных изменений нужно использовать кнопку «OK».

Для того чтобы удалить какое-нибудь выражение, достаточно сделать следующее:

1. Кликните на любую ячейку.

1. Нажмите на кнопку Delete или Backspace . В результате этого клетка окажется пустой.

Добиться точно такого же результата можно и при помощи инструмента «Очистить всё».

Возможные ошибки при составлении формул в редакторе Excel

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

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

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

• не указываются обязательные аргументы функций. Обратите внимание на то, что необязательные аргументы указываются в квадратных скобках. Всё что без них – необходимо для полноценной работы формулы;

• неправильно указываются диапазоны ячеек. Для этого необходимо использовать оператор «:» (двоеточие).

Коды ошибок при работе с формулами

При работе с формулой вы можете увидеть следующие варианты ошибок:

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

При редактировании формулы (ячейки подсвечиваются) вы увидите, что они на самом деле не пересекаются.

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

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

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

1. Вызовите контекстное меню. Выберите пункт «Формат ячеек».

1. Укажите тип «Общий». Для продолжения используйте кнопку «OK».

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

Примеры использования формул

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

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

1. Создайте таблицу с какими-нибудь условными данными.

1. Для того чтобы высчитать сумму, введите следующую формулу. Если хотите прибавить только одно значение, можно использовать оператор сложения («+»).

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

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

Обратите внимание, что символ процента ставится в конце, а не в начале. Кроме этого, при работе с процентами не нужно дополнительно умножать на 100. Это происходит автоматически.

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

=СУММЕСЛИМН(B3:B9;B3:B9;">2";B3:B9;"<6") =СУММЕСЛИМН(C3:C9;C3:C9;">2";C3:C9;"<6")

1. Также можно посчитать количество элементов, которые удовлетворяют какому-то условию. Например, пусть Эксель посчитает, сколько у нас чисел больше 3.

=СЧЁТЕСЛИ(B3:B9;">3") =СЧЁТЕСЛИ(C3:C9;">3")

1. Результат всех формул получится следующим.

Математические функции и графики

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

В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:

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

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

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

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».
1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Заключение

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

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

Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

Файл примеров

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

Видеоинструкция

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

Она состоит. Самое время попробовать программу в деле, вы узнаете, как работать в Excel. Мы создадим новый документ, внесем в него данные, выполним простые расчеты и построим временную диаграмму. После освоения этого урока вы уже сможете работать в Экселе, выполнять лёгкие задачи. Это будет первый уверенный шаг к профессиональному использованию Эксель.

Исходные данные: ежемесячные объёмы продаж товара в 2015 году, стоимость единицы товара, выручка за 2014 год помесячно. Приступаем к расчетам.

Как создать документ Эксель

Если Excel у вас еще не запущен – нажмите Пуск — Все программы — Microsoft Office , и в открывшейся папке – Excel . Программа будет запущена. В открывшемся окне выберите Пустая книга .

Если программа уже запущена – нажмите комбинацию клавиш Ctrl+N для создания новой рабочей книги.

Заполняем лист Excel данными

В результате, перед вами новый пустой документ с активным листом «Лист 1» . Давайте заполним его исходными данными. Начнём с шапки, т.е. определимся со структурой таблицы. Пусть в первом столбце будут месяцы года.

• Кликните левой кнопкой мыши в ячейку А1, она будет активизирована для заполнения. Напишите: «Месяц»
• Аналогично в колонке В1 запишем «Выручка 2015»
• С1 – «Продажи»
• D1 – «Цена»
• E1 – «Выручка 2014»

Шапка готова.

Теперь напишем названия месяцев в столбце А. Установите курсор в ячейку А2, напишите «Январь» и нажмите Enter . Табличный курсор перенесётся на одну строчку вниз. Чтобы заполнить последующие месяцы – воспользуемся функцией Эксель – автозаполнение. Верните курсор в ячейку А2 и наведите мышью на квадратик в нижнем правом углу активной клетки. При этом, курсор превратится в чёрный крестик. Зажмите левую кнопку мыши и тяните курсор вниз, как бы растягивая надпись «Январь» вниз. Эксель автоматически будет записывать новые месяца в каждой следующей строчке.

Пока вы не отпустили мышь – программа показывает подсказку о том, какой месяц будет в соответствующей ячейке. Посмотрите, на рисунке я дотянул список месяцев до мая. Вы же протяните курсор на 11 строчек вниз, на подсказке отобразится «декабрь». Отпускайте мышь и «вуа-ля», одним взмахом руки мы прописали целых 12 месяцев!

Теперь заполним количества и цены. Поставим курсор в клетку С2, запишем количество и нажмём Ввод на клавиатуре. Выделение переместится в С3, там тоже запишем количество. Так, заполняем все продажи до декабря (С13). Аналогично поступим с ценами в столбце D. В нашем примере цены содержат копейки. В русифицированной версии Excel они отделаются от целой части запятой. В оригинальной версии – точкой. Еще заполним выручку 2014 года. Вот так будет выглядеть заполненная таблица данных:

Расчеты в Эксель

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

Активизируем ячейку B2 и вычислим выручку в январе 2015 года. Напишем «=» и кликнем левой кнопкой мыши на ячейку С2, задав ссылку на продажи января. Дальше ставим знак умножения «*» и кликаем на D2 – ссылка на стоимость товара. Жмём Enter и видим, что в выделенной ячейке отобразился результат расчётов, а в строке формул для этой ячейки – введенная нами формула. Excel извлечёт значение из С2 и умножит его на значение в D2. Если изменить любое из них – формула сразу же будет пересчитана. Больше информации о правилах расчета я написал в статьях и . Там всё это расписано более детально.

Давайте протянем эту формулу на все строки таблицы, как мы делали с заполнением месяцев. Тянем за квадратик в правом нижнем углу ячейки с формулой. Результат – программа просчитала выручку для всех месяцев. Обратите внимание, при протягивании изменяются адреса ссылок в формуле. Например, в ячейке В12 у нас формула: =C12*D12 , а не =C2*D2 . Это потому, что у нас по умолчанию применились .

Теперь вычислим разницу между выручкой 2014 и 2015 годов. Назовём столбец F – «Разница». Методика написания формул не меняется, только вычислим =В2-Е2 , протянем формулу для всех месяцев. Видим, например, что в январе 2015 года наша выручка на 7,5 ед. меньше, чем в 2014.

Графики Excel

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

Чтобы построить график, нужно выделить диапазон (почитайте ) данных, на основании которых будет выстроена кривая. Наведите курсор мышки на ячейку А2. Зажмите левую кнопку и тяните курсор к ячейке В13. После этого отпустите кнопку мыши. Если вы всё сделали правильно, диапазон А2:В13 будет выделен тёмным цветом.

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

Сохраняем расчеты

Чтобы сохранить наш документ – нажмите комбинацию клавиш Ctrl+S , в открывшемся окне нажмите Обзор . Открывается стандартное окно сохранения, где нужно указать расположение файла и задать его имя. Нажатием кнопки Сохранить, подтверждаем создание нового файла.

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

Рис. 6.1. Пример таблицы с вычислениями

Любая электронная таблица состоит из ячеек , которые образуют строки и столбцы.

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

Одна электронная таблица называется рабочим листом или просто листом . Максимальное количество столбцов в листе Excel 2007 составляет 16 384, а строк – более 1 млн, что значительно больше, чем в предыдущих версиях программы. Файлы электронных таблиц обычно состоят из нескольких листов и называются книгами .

Место каждой ячейки в таблице определяется ссылкой . Она образуется из обозначений столбца и строки, на пересечении которых находится ячейка. Например, цена диска CD-RW введена в ячейку C2, а итоговая сумма – в ячейку D7 (см. рис. 6.1). Вместо термина ссылка на ячейку могут также употребляться термины имя ячейки или адрес ячейки .

В ячейки электронной таблицы можно вводить текст, числа и даты, а также формулы . Формула – это математическое выражение, по которому производятся вычисления в таблице. Например, в ячейку D2 (см. рис. 6.1) была введена следующая формула: =B2*C2. По этой формуле Excel автоматически перемножит содержимое ячеек B2 и C2 и отобразит результат в ячейке D2. Аналогичные формулы необходимо ввести в остальные ячейки столбца D. Формула всегда начинается со знака =, после которого вводятся ссылки на ячейки и знаки математических операций. В Excel существуют средства для автоматизации ввода формул, которые будут подробно рассмотрены в следующих уроках.

ПРИМЕЧАНИЕ

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

Как и любую другую программу, Excel можно запустить с помощью поиска в меню Пуск. Запустить Excel можно и другим способом, например создать ярлык на Рабочем столе или на Панели быстрого запуска . Внешний вид окна программы Excel изображен на рис. 6.2.

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

Рис. 6.2. Окно программы Microsoft Office Excel 2007

Следует вспомнить основные особенности работы с лентой:

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

? названия кнопок без подписей можно узнать из всплывающих подсказок;

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

? при выборе определенных объектов документа, например рисунков или диаграмм, на ленте автоматически появляются дополнительные вкладки для работы с ними;

? команды для работы с файлами (Создать, Открыть, Сохранить, Печать и др.) находятся в меню, которое открывается с помощью Кнопки «Office», расположенной в левом верхнем углу окна программы;

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

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

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

Рис. 6.3. Строка формул: имя и содержимое текущей ячейки (слева); кнопки завершения и отмены

ввода (справа)

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

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

? Щелчок кнопкой мыши на нужной ячейке.

? Клавиши со стрелками () (они сделают активной соседнюю ячейку в выбранном направлении).

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

Таблица 6.1. Комбинации клавиш для перемещения по листу

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

Для заполнения ячейки таблицы данными необходимо сделать следующее.

1. Выбрать нужную ячейку.

2. Ввести текст, число или формулу.

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

При вводе данных следует учитывать некоторые особенности:

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

?

? для разделения целой и дробной части числа используется запятая; числа удобно набирать на дополнительной цифровой клавиатуре;

? числа, введенные через точку, дефис или знак / рассматриваются как даты; для ввода значений времени числа нужно разделять двоеточием.

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

Excel также имеет средства для автоматизации ввода данных, которые будут рассмотрены в следующих уроках.

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

Таблица 6.2. Приемы редактирования данных в электронной таблице

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

1. Запустите программу Excel с помощью строки поиска в меню Пуск. При этом автоматически будет создана новая книга под названием Книга1.

2. Перемещайтесь по листу различными способами – щелкая кнопкой мыши на ячейках, используя клавиши со стрелками или комбинации клавиш (см. табл. 6.1).

3. Введите произвольный текст в несколько ячеек. Удалите введенный текст нажатием клавиши Delete.

4. Заполните таблицу данными, как показано на рис. 6.4. Сначала введите текст в ячейки столбца A, завершая ввод нажатием клавиши Enter.

Рис. 6.4. Образец таблицы для ввода

Обратите внимание, что текст в некоторых ячейках столбца А не поместится по ширине и займет часть столбца B. Во избежание этого наведите указатель мыши на границу столбца в области заголовков столбцов и переместите эту границу немного вправо. Заполните данными остальные ячейки (см. рис. 6.4).

5. Сделайте активной ячейку D2 и введите в нее следующую формулу: =B2*C2 (рис. 6.5), после чего нажмите клавишу Enter. Не забудьте поставить в начале формулы знак = и переключиться на английский язык ввода. Если вы все сделали правильно, то в ячейке должен отобразиться результат вычисления, в противном случае введите формулу заново.

Рис. 6.5. Процесс (слева) и результат (справа) ввода формулы

6. В ячейке D3 формула должна иметь такой вид: =B3*C3. Введите эту формулу другим способом:

1) сделайте активной ячейку D3 и нажмите клавишу =;

2) щелкните кнопкой мыши на ячейке С3 – адрес ячейки должен автоматически появиться в формуле;

3) нажмите клавишу * (умножение);

4) щелкните кнопкой мыши на ячейке B3 – формула примет вид: =C3*B3;

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

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

7. Введите самостоятельно формулу в ячейку D4. Она будет такой: =B4*C4.

8. Измените несколько чисел в столбцах B и C. Обратите внимание, что все формулы будут пересчитаны автоматически.

9. С помощью ярлыков в нижней части окна перейдите на Лист2, а затем вернитесь на Лист1.

10. Сохраните созданную таблицу. Для этого нажмите кнопку Сохранить на панели быстрого доступа, в появившемся окне выберите папку для сохранения и введите имя книги – Товары (как вы помните, для сохранения работ была создана отдельная папка Уроки по Excel).

11. Закройте программу Excel.

1. Запустите программу Excel.

2. Создайте электронную таблицу расчета за электроэнергию по образцу (рис. 6.6).

3. В ячейке B6 создайте формулу для вычисления расхода (разность между последним и предыдущим показанием счетчика).

4. В ячейке B7 введите формулу для расчета суммы оплаты. Измените показания счетчика и проверьте правильность работы формул.

5. Подберите ширину столбцов и измените размер и начертание шрифта для некоторых ячеек с помощью кнопок вкладки Главная.

6. Сохраните таблицу в учебной папке под именем Электричество.

Рис. 6.6. Пример расчета платы за электроэнергию

Вопросы для проверки

? Для чего предназначена программа Excel?

? Что означают основные термины электронных таблиц: книга, лист, ячейка, строка, столбец, ссылка?

? В чем сходство и различие окон программ Excel и Word?

? Как запустить Excel и перемещаться по листу?

? Как вводить и редактировать данные?

? Как ввести простую формулу для вычислений?

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

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

1. Установите указатель мыши в левую верхнюю ячейку нужного диапазона. Вид указателя должен быть, как на рис. 6.7, слева (большой «плюс»).

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

Рис. 6.7. Выделение отдельной ячейки (слева) и прямоугольного диапазона (справа)

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

Рис. 6.8. Выделение строки (слева), столбца (в центре), таблицы (справа)

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

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

Рис. 6.9. Выделение сложного диапазона

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

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

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

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

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

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

2. В появившемся контекстном меню выполните команду Удалить.

3. В окне Удаление ячеек (рис. 6.10) установите переключатель в нужное положение и нажмите кнопку ОК .

Рис. 6.10. Удаление ячеек: выполнение команды контекстного меню (слева), выбор способа удаления (справа)

СОВЕТ

Быстро удалить строку можно, щелкнув правой кнопкой мыши на нужной строке в области заголовков строк и выполнив команду Удалить контекстного меню. В этом случае удаление выполняется без появления окна, показанного на рис. 6.10, слева. Аналогично можно удалять столбцы.

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

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

2. В группе Ячейки вкладки Главная нажмите стрелку рядом с кнопкой Вставить и выполните команду Вставить ячейки .

3. В появившемся окне (рис. 6.11) укажите способ вставки ячеек и нажмите кнопку OK.

Рис. 6.11. Диалоговое окно Добавление ячеек

Для вставки строк или столбцов можно использовать команды меню – кнопки Вставить – Вставить строки на лист илиВставить столбцы на лист .

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

1. Выберите нужную ячейку или выделите диапазон.

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

3. Удерживая нажатой кнопку мыши, перетащите выделенные данные в нужное место (рис. 6.12, справа ), после чего отпустите кнопку мыши.

Рис. 6.12. Перемещение ячеек перетаскиванием: вид указателя (слева), выделенные данные перемещены (справа)

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

Рис. 6.13. Предупреждение при перемещении ячеек

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

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

Рис. 6.14. Перетаскивание ячеек с помощью правой кнопки мыши

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

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

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

Последовательность копирования ячеек с помощью буфера обмена следующая.

1. Сделайте активной нужную ячейку или выделите их диапазон.

2. Щелкните на кнопке Копировать, которая находится в группе Буфер обмена вкладки Главная. Выбранные ячейки будут окружены бегущей пунктирной рамкой (рис. 6.15).

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

4. Для копирования или перемещения нажмите клавишу Enter. Обратите внимание, что в данном случае нет необходимости выполнять команду Вставить.

Рис. 6.15. Копирование ячеек с помощью кнопки Копировать

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

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

? Команду вставки можно выполнить различными способами: с помощью кнопки Вставить, контекстного меню или комбинации клавиш Ctrl+V. В любом из этих случаев вставку можно повторить несколько раз.

После вставки можно уточнить, как именно ее выполнить, с помощью кнопки Параметрывставки (рис. 6.16).

? Выбирать различные варианты вставки можно, раскрыв меню кнопки Вставить с помощью стрелки в нижней части кнопки (рис. 6.17).

Рис. 6.16. Выбор способа вставки с помощью кнопки Параметры вставки

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

? Команда Специальная вставка (см. рис. 6.17) позволяет не только выбрать вариант вставки, но и выполнить операцию с имеющимися и вставляемыми данными (рис. 6.18).

Рис. 6.17. Выбор способа вставки с помощью меню кнопки Вставить

Рис. 6.18. Выбор вариантов вставки в окне Специальная вставка

Автозавершение позволяет выполнить автоматический повторный ввод текста. Например, вы заполняете таблицу, показанную на рис. 6.19. При повторном наборе в пятой строке слова Колонки Excel автоматически подставит недостающую часть слова, которая будет выделена черным цветом, с чем достаточно согласиться, нажав клавишу Enter. Если в следующей строке нужно ввести другое слово на «Ко», например Корпус , то его следует вводить дальше, не обращая внимание на появившееся автозавершение.

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

Рис. 6.19. Пример автозавершения

1. В первую ячейку введите начальную дату.

2. Сделайте ячейку с начальной датой активной и подведите указатель мыши к маркеру заполнения, который находится в правом нижнем углу активной ячейки. Указатель мыши должен принять вид тонкого «плюса» (рис. 6.20, слева ).

3. Удерживая нажатой кнопку мыши, протяните мышь вниз. Возле указателя мыши появится подсказка со значением текущей ячейки (рис. 6.20, в центре ).

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

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

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

1. Введите первое и второе значение числовой последовательности в смежные ячейки.

2. Выделите обе заполненные ячейки.

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

Аналогично происходит заполнение значениями даты и времени, если шаг отличается от единицы.

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

Рис. 6.20. Последовательность действий при автозаполнении: указатель принял вид тонкого «плюса» (слева), подсказка (в центре), диапазон заполнен последовательностью значений (справа)

Рис. 6.21. Варианты автозаполнения

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

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

1. Щелкните на Кнопке «Office», затем нажмите кнопку Параметры Excel .

2. В появившемся окне нажмите кнопку Изменитьсписки в группе Основные параметры работы с Excel .

Рис. 6.22. Автозаполнение с помощью правой кнопки мыши

3. В следующем окне (рис. 6.23) в поле Списки выберите НОВЫЙ СПИСОК , введите его элементы в поле Элементы списка и нажмите кнопку Добавить .

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

Рис. 6.23. Окно настройки списков автозаполнения

Новая книга обычно содержит три листа, которые называются Лист1, Лист2 и Лист3. Для перехода на другой лист следует щелкнуть кнопкой мыши на его ярлыке в нижней части окна. При большом количестве листов ярлыки можно прокручивать с помощью кнопок, находящихся слева от них. Щелкнув правой кнопкой мыши на этих же кнопках, можно выбрать нужный лист из контекстного меню.

С листами можно выполнять различные операции, которые описаны в табл. 6.3.

Таблица 6.3. Действия с рабочими листами

Задание. Научиться выполнять различные операции с ячейками и диапазонами.

Последовательность выполнения

1. Откройте книгу Товары , сохраненную в предыдущем уроке.

2. Выделите диапазон, строку, столбец так, как показано в уроке 6.2 (см. рис. 6.7– 6.9).

3. Удалите диапазон ячеек A3:D3 (см. рис. 6.10).

4. Выделите диапазон A1:D1 и добавьте ячейки со сдвигом вниз.

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

6. Не закрывая книгу Товары , откройте книгу Электричество .

7. Выделите диапазон A4:B4 и перетащите его во вторую строку (см. рис. 6.12).

8. Верните перемещенные ячейки обратно перетаскиванием с помощью правой кнопки мыши.

9. Скопируйте таблицу из книги Электричество на второй лист книги Товары . Для этого:

1) в книге Электричество выделите диапазон A1:B7 и нажмите кнопку Копировать ;

2) перейдите в книгу Товары с помощью Панели задач Windows;

3) перейдите на второй лист книги (щелкните кнопкой мыши на ярлыке Лист2 в нижней части окна);

4) сделайте активной ячейку A1 и нажмите клавишу Enter.

10. Переименуйте листы – Лист1 в Товары , а Лист2 в Электричество .

11. Перейдите на ЛистЗ и выполните различные варианты автозаполнения ячеек (см. рис. 6.21).

1. Добавьте к созданной в предыдущем задании книге новый лист с именем Календарь.

2. В ячейку А1 нового листа введите слово Январь, в ячейку А2 – Понедельник, а в ячейки ВЗ и В4 – числа 1 и 2.

3. Используя только средства автозаполнения, переноса, копирования и удаления ячеек, создайте календарь, показанный на рис. 6.24.

4. Сохраните изменения.

Рис. 6.24. Календарь нужно создать, используя операции автозаполнения, переноса и копирования ячеек

Вопросы для проверки

? Что такое диапазоны ячеек и как они обозначаются?

? Как выделить несколько смежных ячеек? Как выделить несколько несмежных ячеек?

? В чем разница между очисткой и удалением ячеек?

? Сколько способов копирования ячеек вы знаете? Опишите последовательность копирования одним из них.

? Какая разница между перетаскиванием ячеек с помощью левой и правой кнопок мыши?

? Какие действия можно выполнять с рабочими листами?

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

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

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

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

При установленном общем формате действуют следующие правила ввода данных:

? текст автоматически выравнивается по левому краю, а числа – по правому;

? для разделения целой и дробной части числа используется запятая;

? при вводе чисел через точку, тире или знак / они рассматриваются как даты (если это возможно);

? при вводе чисел через двоеточие они рассматриваются как значения времени;

? если к числу добавить знак %, оно будет представлено в процентном формате;

? числа в виде обычной дроби, например 2/3, следует вводить так: 0 2/3 (нужно обязательно указать целую часть числа, даже если она равна нулю, а также поставить пробел между целой и дробной частью).

ПРИМЕЧАНИЕ

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

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

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

2. На вкладке Главная в группе Число раскройте список Числовой формат и выберите нужный вариант форматирования (рис. 6.25).

Рис. 6.25. Выбор числового форматирования

ПРИМЕЧАНИЕ

При переводе даты в числовой формат Excel отобразит число, соответствующее количеству дней, прошедших с 31.12.1899.

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

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

Процентный формат. Число будет умножено на 100, и к нему будет добавлен символ %.

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

Увеличить разрядность. Увеличивает количество десятичных разрядов в дробной части числа.

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

ВНИМАНИЕ

При расчетах Excel всегда использует действительное значение числа, а не то, которое отображается в ячейке с учетом установленного количества разрядов. Для принудительного округления числа нужно использовать функцию ОКРУГЛ , которая будет рассмотрена далее.

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

Рис. 6.26. Вкладка Число диалогового окна Формат ячеек

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

? формула всегда начинается со знака =;

? в формулах используются следующие знаки арифметических действий:

+ – сложение;

– – вычитание;

* – умножение;

/ – деление;

% – процент;

^ – возведение в степень;

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

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

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

Для облегчения ввода формул можно использовать следующие приемы:

? для быстрого ввода в формулу имени ячейки достаточно щелкнуть на ней кнопкой мыши;

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

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

Детальные описание процесса ввода формул вы найдете в инструкции к практической работе 44.

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

1. Сделайте активной ячейку, в которую следует поместить результат суммирования.

2. Нажмите кнопку Автосумма

которая находится в группе Редактирование вкладкиГлавная. Excel создаст формулу и попытается автоматически определить диапазон для суммирования, который будет выделен пунктирным прямоугольником (рис. 6.27).

Рис. 6.27. Создание формулы для суммирования диапазона с помощью кнопки Автосумма

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

Кроме суммирования вы можете выполнить и другие операции из меню, которое открывается щелчком на стрелке рядом с кнопкой Автосумма (рис. 6.28).

Рис. 6.28. Кнопка Автосумма имеет меню для выбора нужной функции

Назначение команд меню кнопки Автосумма приведено в табл. 6.4.

Таблица 6.4. Назначение команд меню кнопки Автосумма

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

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

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

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

Последовательность выполнения

1. Создайте новую книгу.

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

Рис. 6.29. Исходные данные для расчета заработной платы

3. Для расчета начисленной зарплаты следует умножить ставку на количество часов. Для ввода формулы в ячейку D3 выполните следующие действия:

2) нажмите клавишу = ;

3) щелкните кнопкой мыши на ячейке С3 – адрес ячейки должен появиться в формуле;

4) нажмите клавишу * (умножение);

5) щелкните кнопкой мыши на ячейке B3; формула должна принять вид =C3*B3;

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

Если формула не дала правильный результат, попробуйте ввести ее заново.

4. Для ввода формул в остальные ячейки столбца D воспользуйтесь автозаполнением:

1) сделайте активной ячейку D3;

2) наведите указатель мыши на маркер автозаполнения в правом нижнем углу ячейки (указатель мыши должен принять вид тонкого «плюса» (см. рис. 6.20, слева );

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

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

5. Введите формулу в ячейку E3. По условию задачи ставка налога равна 15 % от начисленной зарплаты, поэтому формула должна иметь вид: =D3*15%. Последовательность ввода этой формулы следующая:

1) сделайте активной ячейку E3 и нажмите клавишу = ;

2) щелкните кнопкой мыши на ячейке D3 – адрес ячейки должен появиться в формуле;

3) нажмите клавишу * (умножение) и введите 15%;

4) нажмите клавишу Enter и проверьте результат вычисления.

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

7. Формула в ячейке F3 будет иметь вид: =D3-E3. Попробуйте самостоятельно ввести ее и заполнить другие ячейки столбца F.

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

9. Для расчета остальных сумм сделайте активной ячейку C7 и выполните автозаполнение вправо.

10. Для расчета количества часов в среднем на одного сотрудника выполните следующие действия:

1) сделайте активной ячейку C8;

2) нажмите стрелку рядом с кнопкой Автосумма и выполните команду Среднее ;

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

для исправления выделите с помощью кнопки мыши диапазон C3:C6, который автоматически будет подставлен в формулу;

4) нажмите клавишу Enter и проверьте результат вычислений.

11. Выполните форматирование таблицы:

1) для изменения шрифта в ячейке выделите ее и воспользуйтесь кнопками группы Шрифт вкладки Главная ;

2) для всех ячеек с денежными значениями установите Формат с разделителем с помощью соответствующей кнопки в группе Число ; если в некоторых ячейках вместо цифр появятся символы #####, следует увеличить ширину соответствующих столбцов;

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

12. Сохраните книгу под именем Зарплата .

Рис. 6.30. Результат расчета зарплаты

ВНИМАНИЕ

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

Задание для самостоятельного выполнения

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

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

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

Рис. 6.31. Таблица для расчета результатов КВН

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

4. Сохраните книгу под именем КВН.

Вопросы для проверки

? Как в Excel представляются и обрабатываются числа?

? Какие существуют особенности ввода данных при использовании общего формата?

? Как изменять представление чисел в ячейках?

? Что такое формула? Каковы правила создания формул?

? Как вводить имена ячеек в формуле с помощью щелчков кнопкой мыши?

? Когда нужно использовать автозаполнение формул? Какова последовательность выполнения этой операции?

? Как создавать различные формулы с помощью кнопки Автосумма?

? Как выполнять вычисления с помощью строки состояния?

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

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

Чтобы ссылка стала абсолютной, к обозначениям строки и столбца необходимо добавить знак доллара ($). Например, ссылка A2 – относительная, а ссылка $A$2 – абсолютная. Абсолютная ссылка не изменяется при копировании или заполнении формул. Особым вариантом являются смешанные ссылки, в которых абсолютным является только столбец или строка, например $A2 или A$2.

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

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

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

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

Последовательность выполнения

1. Создайте таблицу с исходными данными (см. рис. 6.32).

2. Присвойте имя ячейке В2. Для этого:

1) сделайте ее активной;

2) щелкните кнопкой мыши в поле адреса в левой части строки формул;

3) введите новое имя, например Курс(рис. 6.33);

4) нажмите клавишу Enter.

3. Создайте формулу для расчета рублевой цены в ячейке С5. Формула должна иметь вид =В5*Курс. Имена, как и ссылки, не обязательно набирать на клавиатуре, достаточно при вводе формулы щелкнуть кнопкой мыши на нужной ячейке.

4. Выполните автозаполнение для остальных ячеек столбца С и проверьте правильность результатов.

5. Сохраните книгу под именем Прайс.

Рис. 6.32. Пример прайс-листа

Задание для самостоятельного выполнения

Создайте еще одну таблицу начисления зарплаты, которая рассчитывается следующим образом:

? для каждого сотрудника установлена месячная ставка, которую он получит, если отработает установленное количество рабочих дней;

? если сотрудник отработал меньше дней, чем положено, то его зарплата рассчитывается так:

зарплата=ставка*отработано_дней/рабочих_дней_в_месяце;

? сумму налогов примите равной 13 %;

? сумму к выплате для каждого сотрудника нужно перевести в доллары по текущему курсу.

Пример таблицы показан на рис. 6.34. В ячейки диапазона D5:G9 следует ввести соответствующие формулы (на рисунке этот диапазон выделен).

Рис. 6.34. Таблица для расчета зарплаты с использованием именованных ячеек

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

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

В Excel имеется множество встроенных функций, которые позволяют выполнять математические, экономические, научные и другие расчеты по заранее предопределенным формулам. При использовании функции в вычислениях следует записать ее имя, после чего в круглых скобках указать аргументы. Аргументы – это значения, которые используются в данной функции. Для примера рассмотрим использование функции ОКРУГЛ, которая выполняет округление числа до заданного количества десятичных знаков. Допустим, нужно перемножить значения ячеек А2 и В2 , а затем округлить результат до двух десятичных знаков. Формула будет иметь следующий вид:

=ОКРУГЛ(А2*В2;2).

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

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

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

Задание. Используйте функцию ОКРУГЛ для округления цен в прайс-листе из предыдущей практической работы (см. рис. 6.32). Округление необходимо, поскольку при пересчете может получиться результат с несколькими десятичными знаками, а подобные цены не всегда удобны.

Последовательность выполнения

1. Откройте прайс-лист, который был сохранен под именем Прайс.

2. Удалите все формулы из столбца С.

3. Сделайте активной ячейку, в которую нужно ввести формулу. Для данного прайс-листа это ячейка С5.

4. Нажмите кнопку Вставка функции (f x) в строке формул. Появится первое окно Мастера функций (рис. 6.35). Если вы начинаете с Мастера функций, то знак = будет введен автоматически.

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

все функции в Excel разделены на несколько категорий; после выбора нужной категории отобразится список имеющихся в ней функций, например функция ОКРУГЛ относится к категории Математические;

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

Рис. 6.35. Первый шаг Мастера функций – выбор функции

6. После выбора функции появится второе окно Мастера с полями для ввода аргументов (рис. 6.36). Для выбранной функции ОКРУГЛ следует ввести два аргумента – Число и Число_разрядов . В поле Число нужно ввести выражение, которое необходимо округлить. В данном примере оно будет иметь вид B6*Курс . Для ввода в формулу ссылок на ячейки достаточно щелкнуть кнопкой мыши на ячейке; чтобы увидеть нужную ячейку, может понадобиться переместить окно Мастера в другую часть экрана.

Рис. 6.36. Второй шаг Мастера функций – ввод аргументов функции

7. Для ввода второго аргумента щелкните кнопкой мыши в поле Число_разрядов . Обратите внимание на изменившуюся подсказку в нижней части окна Мастера. Для округления цены до десятков копеек установите число разрядов равным 1.

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

9. Перед закрытием Excel сохраните внесенные изменения.

Задание для самостоятельного выполнения.

Создайте таблицу для расчета гипотенузы прямоугольного треугольника по двум известным катетам. Если обозначить катеты буквами a и b, а гипотенузу – c , то формула для расчета гипотенузы будет иметь вид:

Для вычисления корня используйте математическую функцию КОРЕНЬ.

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

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

? Для изменения аргументов ранее введенной функции выполните следующие действия:

1) сделайте активной ячейку, в которой нужно отредактировать формулу;

2) щелкните кнопкой мыши на названии нужной функции в строке формул;

3) нажмите кнопку Вставка функции – появится окно с аргументами выбранной функции, и вы сможете отредактировать их.

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

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

? Для удобного поиска и ввода нужной функции используйте группу Библиотека функций на вкладке Формулы (рис. 6.37). Щелкнув на любой кнопке, выберите из списка нужную функцию – вы перейдете в окно ввода аргументов функции.

Рис. 6.37. Группа Библиотека функций

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

Если вы сделаете синтаксическую ошибку в формуле, например поставите лишние скобки, пропустите обязательный аргумент или знак препинания, то при попытке завершения ввода формулы появится предупреждение, подобное показанному на рис. 6.38. В этом случае следует проанализировать текст формулы и исправить ошибку или ввести формулу заново. Если Excel сможет точно определить место ошибки, то вместо предупреждения появится предложение исправить ошибку автоматически. Лучше не вводить функции вручную, а пользоваться Мастером функций – это значительно снизит вероятность возникновения ошибок.

Рис. 6.38. Сообщение об ошибке в формуле

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

Таблица 6.5. Сообщения об ошибках

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

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

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

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

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

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).

В логическом выражении функции ЕСЛИ могут использоваться числа, даты, ссылки на ячейки, а также знаки > (больше), < (меньше), = (равно), >= (не меньше), <= (не больше), <> (не равно). Функция ЕСЛИ выполняется следующим образом.

1. Вычисляется логическое выражение, которое может иметь одно из двух значений: ИСТИНА или ЛОЖЬ .

2. В зависимости от результата вычисления логического выражения функция возвращает один из двух возможных результатов, которые записаны в аргументах значение_если_истина и значение_если_ложь. Рассмотрим пример. Пусть на предприятии выплачивается надбавка к зарплате за стаж в размере 20 % для работников, имеющих стаж 10 и более лет. Пример расчетной таблицы приведен на рис. 6.39, где показан процесс ввода формулы в ячейку D3 с использованием строки формул. В этой формуле используется логическое выражение C3>=10 , чтобы определить право работника на надбавку. Если логическое выражение будет иметь значение ИСТИНА , то сумма надбавки рассчитывается по формуле B3*20%, в противном случае результат функции будет равен нулю.

Рис. 6.39. Пример ввода логической функции ЕСЛИ

Для объединения нескольких условий в одно можно использовать логическую функцию И , которая возвращает значение ИСТИНА , если все входящие условия имеют значение ИСТИНА . Например, условие «значение ячейки A1 должно быть больше 5 и меньше 10» записывается так: И(A1>5;A1<10). Логическая функция ИЛИ возвращает значение ИСТИНА , если хотя бы одно входящее условие имеет значение ИСТИНА.

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

ПРИМЕЧАНИЕ

Количество вложений функции ЕСЛИ может достигать 64, что позволяет задавать сложные условия. Читатели, имеющие понятие о программировании, должны обратить внимание на то, что функция ЕСЛИ является аналогом условного оператора (if… then… else).

Для решения задачи нужно сформулировать словесный вариант решения. Он может звучать приблизительно так: «ЕСЛИ стаж меньше трех лет, то результат: 0, иначе ЕСЛИ стаж меньше 10 лет, то результат: зарплата * 10 %, иначе результат: зарплата * 20 %. Жирным шрифтом выделены ключевые слова, которые определяют аргументы функций. Необходимо использовать две функции ЕСЛИ, вторая из которых будет вложена в первую.

Последовательность выполнения

1. Создайте таблицу с исходными данными (см. рис. 6.39).

2. Сделайте активной нужную ячейку (в данном примере D3).

3. Выполните команду Формулы > Библиотека функций > Логические и выберите в списке функцию ЕСЛИ.

4. Введите аргументы функции. Следуя словесной формулировке решения, в поле Лог_выражение введите условие С3<3, а в поле Значение_если_истина – число 0.

5. В поле Значение_если_ложь следует создать вложенную функцию ЕСЛИ. Для этого установите курсор в указанное поле и выберите функцию ЕСЛИ из раскрывающегося списка в строке формул (рис. 6.40).

Рис. 6.40. Вставка вложенной функции

6. Укажите аргументы второй функции. Согласно словесной формулировке они будут такие: Лог_выражение – С3<10; Значение_если_истина – В3*10%; Значение_если_ложь – В3*20%.

7. Нажмите кнопку ОК и проверьте результат работы формулы при различных исходных данных. Если все было сделано правильно, формула в ячейке D3 должна быть такой: =ЕСЛИ(СЗ<3;0;ЕСЛИ(СЗ<10;ВЗ*10%;ВЗ*20%)). Эта формула приведена только для проверки, и вводить ее вручную настоятельно не рекомендуется – это нужно сделать описанным выше способом с помощью Мастера функций.

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

Вопросы для проверки

? Какая разница между абсолютными и относительными ссылками на ячейки?

? Какие преимущества дает использование имен для ячеек и диапазонов?

? Как создать имя и как его удалить?

? Что такое функция, аргументы функции?

? Как вводить функции с помощью Мастера функций?

? Что представляют собой логические функции; как выполняется функция ЕСЛИ?

? Какие ошибки могут возникнуть при расчетах и как их исправлять?

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

Оформить созданную таблицу можно различными способами:

? применить для ячеек или таблицы в целом встроенные стили;

? установить параметры форматирования для отдельных ячеек и диапазонов вручную;

? настроить условное форматирование для ячеек;

? создать новые документы на основе готовых шаблонов с предварительно настроенным форматированием.

Каждый из этих способов имеет свои особенности, которые будут описаны в данном уроке.

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

Для форматирования всей таблицы выполните следующие действия.

1. Выделите диапазон ячеек для форматирования в виде таблицы.

2. На вкладке Главная в группе Стили нажмите кнопку Форматировать как таблицу .

3. В появившемся окне (рис. 6.41) выберите нужный стиль.

Рис. 6.41. Выбор стиля таблицы

Для таблиц, отформатированных с помощью стилей, на ленте появляется дополнительная вкладка Работа с таблицами: Конструктор (рис. 6.42), где можно настроить формат таблицы с помощью флажков в группе Параметры стилей таблиц .

Рис. 6.42. Вкладка Работа с таблицами: Конструктор

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

1. Выделите нужную ячейку или диапазон.

2. На вкладке Главная в группе Стили нажмите кнопку Стили ячеек .

3. В появившемся окне (рис. 6.43) выберите нужный стиль.

Рис. 6.43. Выбор стиля ячейки

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

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

СОВЕТ

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

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

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

и типа ее границы

Рассмотрим назначение кнопок в группе Выравнивание.

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

Рис. 6.44. Примеры различных способов выравнивания в ячейке

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

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

Объединить и поместить в центре . Для объединения нескольких ячеек в одну выделите их и нажмите данную кнопку. Повторное ее нажатие вернет состояние ячеек в исходное.

Увеличить отступ , Уменьшить отступ . Изменение значения отступа в ячейке. При выравнивании по левому краю изменяется отступ слева, при выравнивании по правому краю – справа.

Дополнительные возможности для форматирования ячеек вы найдете в диалоговом окне Формат ячеек , которое можно открыть, щелкнув правой кнопкой мыши на ячейке или диапазоне и выполнив в контекстном меню команду Формат ячеек . Это окно состоит из нескольких вкладок; для открытия нужной можно использовать кнопку вызова диалогового окна в группах Шрифт , Выравнивание (рис. 6.45) или Число .

Рис. 6.45. Вкладка Выравнивание диалогового окна Формат ячеек

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

Назначение других вкладок окна Формат ячеек следующее:

? Число. Служит для установки форматов чисел (см. урок 6.3);

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

? Граница. Можно установить границы ячеек и выбрать тип и цвет линий;

? Заливка. Позволяет выбрать заливку для ячейки, но это проще сделать с помощью кнопки Цвет заливки в группе Шрифт;

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

Для изменения ширины столбца следует передвинуть его границу в области заголовков столбцов. Аналогично можно изменять высоту строк. Для более точной настройки ширины столбцов и высоты строк используйте меню кнопки, появляющееся после выполнения команды Главная > Ячейки и нажатия кнопки Формат (рис. 6.46).

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

Рис. 6.46. Команды для форматирования строк и столбцов

Можно также скрывать отдельные строки или столбцы. Например, чтобы скрыть столбец, выделите его и нажмите кнопку Формат и выполните команду Скрыть или отобразить > Скрыть столбцы. Для отображения скрытого столбца следует выделить два столбца, между которыми находится скрытый, и, нажав кнопку Формат, выполнить команду Скрыть или отобразить > Отобразить столбцы. Аналогичным способом можно скрывать и отображать строки.

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

Для создания нового документа на основе шаблона нажмите Кнопку «Office» и выполните команду Создать, чтобы открыть окно Создание книги. Здесь доступны следующие операции:

? для создания новой книги дважды щелкните кнопкой мыши на значке Новая книга в области Пустые и последние;

? для создания документа на основе одного из установленных шаблонов выберите пункт Установленные шаблоны (рис. 6.47), затем дважды щелкните кнопкой мыши на значке нужного шаблона;

Рис. 6.47. Окно выбора шаблона

? при наличии подключения к Интернету можно поискать нужный шаблон в одной из категорий группы Microsoft Office Online; после двойного щелчка на выбранном шаблоне он будет загружен на компьютер, затем на его основе будет создан новый документ;

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

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

Значительно повысить наглядность расчетных листов можно средствами условного форматирования, с помощью которого выделяются ячейки с определенными значениями. Рассмотрим использование условного форматирования на примере таблицы результатов конкурса КВН (см. рис. 6.31), которая была создана в уроке 6.3. После применения условного форматирования она может иметь вид, показанный на рис. 6.48.

Рис. 6.48. Пример таблицы с условным форматированием

Для применения условного форматирования нужно выделить необходимый диапазон ячеек, выполнить команду Главная > Стили > Условное форматирование и выбрать желаемый вариант. Например, для отображения значков рядом с оценками судей (см. рис. 6.48) выделите диапазон C4:E10, в меню кнопки Условное форматирование выберите вариант Наборы значков и укажите желаемый вид значков (рис. 6.49). Для создания столбцов в поле Средний балл выделите диапазон F2:F14 и выберите вариант форматирования Гистограмма.

Рис. 6.49. Выбор варианта условного форматирования

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

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

Рис. 6.50. Диспетчер правил условного форматирования

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

Рис. 6.51. Окно изменения правил форматирования

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

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

Рис. 6.52. Режим Разметка страницы

ПРИМЕЧАНИЕ

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

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

? применить для страницы одну из встроенных тем, как это делалось в программе Word; можно также изменять отдельные элементы темы – цвета, шрифты или эффекты; все эти действия выполняются с помощью кнопок в группе Темы;

? настроить поля, ориентацию страницы, размер бумаги и другие параметры с помощью кнопок группы Параметры страницы;

? напечатать не весь лист, а отдельный диапазон ячеек можно, выделив его, нажав кнопку Область печати и выполнив команду Задать; для отмены печати диапазона нужно нажать кнопку Область печати и выполнить команду Убрать;

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

? для подгонки размера страниц нужно использовать кнопки в группе Вписать; изменяя масштаб, можно напечатать лист в увеличенном или уменьшенном виде; при необходимости подогнать масштаб для печати на заданное количество страниц можно воспользоваться раскрывающимися списками Ширина иВысота;

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

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

Рис. 6.53. Работа с колонтитулами в Excel

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

? Печать. Появится окно параметров печати, которое почти не отличается от стандартного диалога печати большинства программ; единственная особенность – это возможность выбора для печати выделенного диапазона, листа, книги или списка.

? Быстрая печать. Будет выполнена печать листа без появления диалоговых окон.

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

Задание. Отформатировать таблицу по приведенным образцам.

Последовательность выполнения

1. В новой книге создайте таблицу, показанную на рис. 6.54.

Рис. 6.54. Исходная таблица для выполнения форматирования

2. Средствами форматирования ячеек приведите таблицу к следующему виду (см. рис. 6.44) и сохраните ее под именем Форматирование.

3. Откройте ранее созданную книгу с именем КВН (урок 6.3) и с помощью условного форматирования приведите ее к следующему виду (см. рис. 6.48).

4. Откройте книгу Зарплата (урок 6.3) и приведите ее к такому виду (рис. 6.55), используя следующие приемы:

стили ячеек и таблиц;

вставку строк, рисунков, объектов WordArt, колонтитулов и др.;

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

5. Проверьте установленные параметры страницы и выполните предварительный просмотр перед печатью.

6. Напечатайте документ.

7. Сохраните таблицу под именем Ведомость на зарплату.

Рис. 6.55. Пример форматирования расчетной ведомости

Вопросы для проверки

? Какие типы встроенных стилей вы знаете?

? Как создать новый стиль?

? Какие возможности форматирования ячеек доступны в Excel?

? Как открыть окно Формат ячеек?

? Сравните возможности форматирования таблиц в Word и Excel.

? Как в Excel автоматически выделять определенные числа, например жирным шрифтом?

? Как создавать новый документ на основе шаблона?

? Какие возможности для работы с графикой имеет Excel?

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

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

Рис. 6.56. Основные типы диаграмм

Эти диаграммы имеют следующее назначение:

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

? круговую диаграмму следует применять для отображения частей целого; круг принимается за 100 %, а значения, по которым строится диаграмма, отображаются в виде секторов различной величины; разновидностью круговой диаграммы является кольцевая, которая строится аналогично и отличается только внешним видом;

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

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

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

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

2. Выберите тип и вид диаграммы с помощью кнопок на вкладке Вставка группы Диаграммы– диаграмма будет создана автоматически.

3. Отформатируйте диаграмму с помощью инструментов на дополнительных вкладках для работы с диаграммами (рис. 6.57).

Рис. 6.57. Выбор типа гистограммы

Если построенная диаграмма не отвечает требованиям, ее можно доработать с помощью простых и эффективных средств Excel 2007. Для форматирования диаграмм на ленте имеется вкладка Работа с диаграммами , состоящая из трех вкладок: Конструктор , Макет и Формат .

Вкладка Конструктор (рис. 6.58) предназначена для быстрого редактирования и форматирования диаграмм и содержит следующие группы команд.

Рис. 6.58. Работа с диаграммами, вкладка Конструктор

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

? Данные. Иногда для наглядности в диаграмме целесообразно поменять данные по строкам и столбцам – для этого предназначена кнопка Строка/столбец. Кнопка Выбрать данные открывает окно ручной настройки данных.

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

? Стили диаграмм. Предназначена для выбора одного из встроенных стилей оформления. Excel 2007 предлагает три варианта стилей оформления: черно-белые, разноцветные и выполненные в оттенках одного цвета.

? Расположение. Позволяет переместить диаграмму на другой лист или создать для нее новый.

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

Например, для ручной настройки макета диаграммы откройте вкладку Макет, где можно выбрать варианты форматирования названий, подписей, осей, сетки, стенок и других элементов диаграммы. Для этого используйте кнопки с меню в группах Подписи, Оси и Фон (рис. 6.59).

Рис. 6.59. Работа с диаграммами, вкладка Макет

Любая диаграмма состоит из отдельных объектов – заголовки, оси, линии сетки, ряды, подписи данных, легенда и др. Некоторые объекты могут в свою очередь состоять из нескольких более простых объектов. Для каждого объекта диаграммы имеется диалоговое окно установки параметров форматирования. Для вызова этого окна щелкните кнопкой мыши на нужном элементе, затем нажмите кнопку Форматвыделенного фрагмента в группе Текущий фрагмент . Например, на рис. 6.60 показано окно Формат оси , в котором можно настроить шкалу значений, изменить цвет и тип линий, заливку и другие параметры. Аналогичные окна имеются для других элементов диаграммы; выбирать нужный объект можно также с помощью раскрывающегося списка в группе Текущий фрагмент .

Рис. 6.60. Окно изменения формата оси

С помощью вкладки Формат (рис. 6.61) можно применить один из встроенных стилей для отдельных объектов диаграммы, задать тип заливки и контуров и применить различные эффекты для фигур. Элементы для выполнения этих действий расположены в группе Стили фигур. С помощью группы Стили WordArt можно настроить текстовые эффекты для объектов диаграммы. Перед выполнением рассмотренных команд в диаграмме следует выделить нужный объект.

Рис. 6.61. Работа с диаграммами, вкладка Формат

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

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

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

Задание 1. Создать таблицу с данными, построить по таблице гистограмму и отформатировать ее, как показано на рис. 6.62.

Рис. 6.62. Пример гистограммы

Последовательность выполнения

1. Создайте необходимую таблицу с данными (см. рис. 6.62).

2. Выделите нужные для диаграммы данные. В данном примере это диапазон A2:D7.

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

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

5. На вкладке Конструктор нажмите кнопку Строка/столбец . Обратите внимание, как изменяется вид диаграммы. Снова нажмите кнопку Строка/столбец – диаграмма должна вернуться к первоначальному виду.

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

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

8. Измените размер и тип шрифта для текстовых элементов диаграммы. Для этого щелкните на нужном элементе правой кнопкой мыши и воспользуйтесь кнопками появившейся панели.

9. Измените тип диаграммы с помощью соответствующей кнопки вкладки Конструктор, например выберите коническую или пирамидальную.

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

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

Рис. 6.63. Круговая диаграмма

Последовательность выполнения

1. Перейдите на второй лист книги и создайте таблицу с данными (см. рис. 6.63).

2. Выделите диапазоны с данными для диаграммы. В данном примере это диапазон АЗ:В8.

ВНИМАНИЕ

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

3. На вкладке Вставка нажмите кнопку Круговая, выберите тип диаграммы Объемная круговая – диаграмма будет построена автоматически.

4. На вкладке Конструктор примените к диаграмме один из встроенных стилей.

5. Выполните команду Работа с диаграммами > Макет; с помощью кнопок в группе Подписиотключите отображение легенды и попробуйте различные варианты отображения подписей данных.

6. Для получения подписей как на рис. 6.63 выполните в меню кнопки Подписи данных команду Дополнительные параметры подписей данных . В появившемся окне установите следующие флажки: имена категорий , доли , линии выноски и установите переключатель в положение У вершины снаружи .

7. Откройте на ленте вкладку Формат и примените эффекты WordArt для названия диаграммы.

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

Задание 3. Построить график изменения курсов валют в течение года (рис. 6.64).

Рис. 6.64. Пример графика

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

2. В графике (см. рис. 6.64) для значений столбца Доллар используется вспомогательная вертикальная ось. Чтобы добиться такого эффекта, откройте на ленте вкладку Макет , в группе Текущий фрагмент выберите из раскрывающегося списка Ряд«Доллар», затем нажмите кнопку Формат выделенного фрагмента . В появившемся окне установите переключатель в положение По вспомогательной оси и нажмите кнопку Закрыть .

3. С помощью кнопок в группе Подписи вкладки Макет настройте положение подписей осей и легенды так, как показано на рисунке (см. рис. 6.64).

4. Перейдите на вкладку Формат и отформатируйте различные элементы графика по своему усмотрению.

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

Задание для самостоятельного выполнения

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

2. Аналогично постройте диаграмму по результатам выступления команд в КВН (см. урок 6.3).

Вопросы для проверки

? Что такое диаграмма, какие бывают виды диаграмм?

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

? Опишите общую последовательность действий при построении диаграммы.

? Какие элементы диаграмм вы знаете?

? Как изменить параметры диаграммы после ее создания?

? Как изменять формат отдельных элементов диаграммы?

? Какими способами можно выполнить печать диаграммы?

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

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

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

? Все остальные строки списка называются записями. Каждая запись (строка) должна относиться к одному объекту списка. В примере, показанном на рис. 6.65, каждая запись содержит информацию об одном товаре.

? Столбец списка называется полем; в каждом столбце должны находиться данные определенного типа в соответствии с заголовком поля.

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

Рис. 6.65. Пример списка данных

При создании базы данных в Excel следует соблюдать приведенные выше правила и не оставлять полностью пустых строк или столбцов. Можно использовать только одну или несколько верхних строк для создания заголовка списка. Большинство примеров из предыдущих уроков являются списками данных с заголовком в верхней строке.

ПРИМЕЧАНИЕ

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

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

? в группе Редактирование вкладки Главная есть кнопка Сортировка и фильтр, после нажатия которой появляется меню с командами для сортировки и фильтрации (рис. 6.66, слева);

? на вкладке Данные имеется группа Сортировка и фильтр, кнопки которой (рис. 6.66, справа) соответствуют упомянутым выше командам меню кнопки Сортировкаи фильтр.

Рис. 6.66. Меню кнопки Сортировка и фильтр вкладки Главная (слева) и группа команд Сортировка и фильтр на вкладке Данные (справа)

В Excel существует два способа сортировки: быстрая и настраиваемая. Быстрая сортировка выполняется следующим образом.

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

2. Щелкните на кнопке Сортировка и фильтр и выполните одну из команд Сортировка от A до Я и Сортировка от Я до A (см. рис. 6.66, слева). Можно также использовать кнопки в группе команд Сортировка и фильтр (см. рис. 6.66, справа).

3. После выполнения команды Excel автоматически определит диапазон сортировки и упорядочит строки по выбранному столбцу. Если список имеет правильную структуру, то в диапазон сортировки будут включены все имеющиеся записи.

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

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

? расширить диапазон для сортировки;

? сортировать только в пределах выделения;

? отменить сортировку и попытаться правильно выделить диапазон.

ВНИМАНИЕ

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

СОВЕТ

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

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

1. Сделайте активной любую ячейку в списке или выделите для сортировки весь список.

2. Выполните следующую команду: Сортировка и фильтр > Настраиваемая сортировка (см. рис. 6.66, слева) или нажмите кнопку Сортировка в группе Сортировка и фильтр (см. рис. 6.66, справа).

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

Рис. 6.67. Диалоговое окно сортировки диапазона

4. Для сортировки по нескольким полям нажмите кнопку Добавить уровень и введите параметры второго столбца сортировки. Аналогично можно добавить еще один или несколько уровней. Сортировка будет выполнена после нажатия кнопки OK.

Задание 1. Упорядочить записи в таблице (см. рис. 6.65) по убыванию цены товара (от большей к меньшей).

Последовательность выполнения

1. Создайте новую книгу и заполните таблицу данными (см. рис. 6.65). Сохраните ее под именем Товары на складе .

2. Сделайте активной любую ячейку с данными в столбце Цена . Не следует выделять несколько ячеек или целый столбец.

3. Выполните команду Сортировка от Я до А , после чего таблица примет вид, показанный на рис. 6.68.

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

Рис. 6.68. Список данных после сортировки по цене товара

Задание 2. В списке товаров (см. рис. 6.65) выполните сортировку по категориям товаров, а в пределах каждой категории – по убыванию цены.

Последовательность выполнения

1. Сделайте активной любую ячейку с данными и нажмите кнопку Сортировка в группе Сортировка и фильтр вкладки Данные.

2. В списке Сортировать по выберите пункт Название. Если вместо заголовков списка отображаются названия столбцов в Excel, установите флажок Мои данные содержат заголовки.

3. Щелкните на кнопке Добавить уровень и в списке Затем по выберите пункт Цена,$.

4. Установите для пункта Название порядок от А до Я, а для пункта Цена,$ – По убыванию.

5. Нажмите кнопку OK и проверьте правильность сортировки.

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

Задание 1. Отобрать в прайс-листе (см. рис. 6.65) только товары, которые имеются на складе.

Последовательность выполнения

1. Откройте список товаров, который сохранен под именем Товары на складе (см. рис. 6.65).

2. В столбце Наличие выделите любую ячейку со значением Есть.

3. Щелкните правой кнопкой мыши и в контекстном меню выполните команду Фильтр> Фильтр по значению выделенной ячейки (рис. 6.69). Будет включен режим автофильтра, а в списке останутся только записи, которые соответствуют условию.

4. Для отмены фильтра нажмите кнопку Фильтр на вкладке Данные (см. рис. 6.66, б).

Рис. 6.69. Установка фильтра по значению ячейки

Задание 2. Выбрать из списка все принтеры, которые есть на складе и которые можно заказать.

Последовательность выполнения

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

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

1) щелкните на кнопке со стрелкой возле заголовка Название ;

2) снимите флажок Выделить все , затем установите флажок Принтер и нажмите кнопку OK (рис. 6.70).

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

Задание 3. Выберите из списка только мониторы с ценой менее $300.

Последовательность выполнения

1. Отмените предыдущий фильтр и отберите все мониторы одним из описанных выше способов.

Рис. 6.70. Установка фильтра

2. Щелкните на кнопке со стрелкой возле заголовка Цена и выполните команду Числовые фильтры > меньше.

3. В соответствующее поле введите значение 300 (рис. 6.71) и нажмите кнопку ОК для применения фильтра.

Рис. 6.71. Окно установки фильтра по условию

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

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

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

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

Рис. 6.72. Книга учета продаж (будет использована в примерах вычисления промежуточных итогов и построения сводной таблицы)

Последовательность выполнения

1. Создайте таблицу (см. рис. 6.72) и сохраните ее под именем Учет продаж.

2. Отсортируйте таблицу по полю, по которому нужно выполнить группировку (в данном примере это сортировка по дате).

3. Сделайте активной любую ячейку в списке данных и нажмите кнопку Промежуточные итоги в группе Структура вкладки Данные.

4. Выполните команду Данные > Итоги – весь список будет выделен и появится окно Промежуточные итоги.

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

Рис. 6.73. Диалоговое окно Промежуточные итоги

6. Нажмите кнопку OK – Excel выполнит расчет промежуточных итогов (рис. 6.74). Обратите внимание на появившиеся кнопки в левой части окна Excel. С их помощью можно изменять представление списка, скрывая определенные группы данных.

Рис. 6.74. Результат вычисления промежуточных итогов

7. Убрать с листа промежуточные итоги можно, повторно нажав кнопку Промежуточные итоги и в появившемся окне (см. рис. 6.73) нажав кнопку Убрать все .

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

Последовательность выполнения

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

2. На вкладке Вставка нажмите кнопку Сводная таблица .

3. В окне создания сводной таблицы (рис. 6.75) проверьте правильность определения диапазона данных и нажмите кнопку OK.

4. В области Список полей сводной таблицы установите флажки Дата , Наименование товара , Количество и Сумма – сводная таблица будет создана автоматически (рис. 6.76).

Рис. 6.75. Окно создания сводной таблицы

Рис. 6.76. Пример сводной таблицы

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

Рис. 6.77. Сводная таблица после изменения структуры

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

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

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

Вопросы для проверки

? Дайте определение следующих терминов: база данных, поле, запись.

? Каких правил следует придерживаться при создании списков данных?

? Как выполнять сортировку данных?

? Как установить фильтр и как его убрать?

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

? Какими возможностями для анализа обладают промежуточные итоги и сводные таблицы?

? Как создать сводную таблицу и изменить ее свойства?

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

? как можно чаще пробуйте составлять расчетные таблицы;

? сложную задачу не всегда удается решить с первого раза – даже профессионалы часто находят решение только после нескольких попыток;

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

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