Использование различных форматов чисел
Перед решением математических, экономических и других задач следует разобраться, как 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. Внимательно ознакомьтесь с ним, поскольку в нем будут рассмотрены базовые приемы работы, которые применяются при решении большинства расчетных задач.
Практическая работа 44. Создание простой таблицы с формулами
Задание. Составить таблицу расчета заработной платы для работников отдела, имея следующие исходные данные: фамилии сотрудников, их часовые ставки и количество отработанных часов.
В таблице требуется вычислить: сумму начисленной зарплаты для каждого сотрудника, сумму удержанных налогов (для упрощения примем единую ставку налога 15 %), сумму, которую необходимо выплатить на руки. Кроме этого, нужно посчитать общую сумму отработанного времени, зарплаты и налогов по всему отделу, а также определить средние значения этих показателей.
Последовательность выполнения
1. Создайте новую книгу.
2. В первую строку введите заголовок таблицы, а во вторую – ее «шапку». При необходимости увеличьте ширину столбцов, перемещая их границы в области заголовков столбцов. Заполните таблицу исходными данными, как показано на рис. 6.29.
Рис. 6.29. Исходные данные для расчета заработной платы
3. Для расчета начисленной зарплаты следует умножить ставку на количество часов. Для ввода формулы в ячейку D3 выполните следующие действия:
1) сделайте активной ячейку 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 вы ознакомитесь со способами форматирования готовых расчетных таблиц.