Автоформат
Excel позволяет профессионально оформить таблицу по выбранному образцу из набора различных форматов. Для быстрого оформления таблицы выделите прямоугольный диапазон с данными и выберите в меню Формат (Format) команду Автоформат (AutoFormat). Диалоговое окно команды (рис. 15.12) содержит список форматов, отличающихся обрамлением и заливкой ячеек, параметрами шрифта и цветовым оформлением. Автоформат располагает заголовки строк и столбцов соответственно в левом столбце и верхних строках. Выделите понравившийся вам образец автоформата в диалоговом окне.
Если нажать кнопку Параметры (Options), то внизу окна отобразятся дополнительные параметры применения автоформата, объединенные в группу Изменить (Formats to apply). Они позволяют изменить формат чисел (Number), шрифт (Font), выравнивание (Alignment), рамки (Border), узоры (Patterns), ширину и высоту (Width/Height). Снимите флажки с атрибутов, которые не нужно применять к элементам таблицы и нажмите кнопку ОК. Выбранный формат будет автоматически применен к таблице.
Рис. 15.12 Диалоговое окно Автоформат после нажатия кнопки Параметры
Для удаления форматирования, выполненного с помощью команды Автоформат (AutoFormat) выделите диапазон ячеек и выберите в меню Формат (Format) эту команду. В диалоговом окне Автоформат (AutoFormat) выберите вариант Нет (None) и нажмите кнопку ОК.
Форматирование позволяет более наглядно отобразить
Форматирование позволяет более наглядно отобразить числовые или текстовые данные, хранящиеся в электронной таблице. В этой главе вы познакомитесь со следующими вопросами:
форматирование ячеек, строк и столбцов;
копирование форматов;
защита ячеек, листов и книг и их удаление.
Напомним, что форматы чисел, выравнивание текста и чисел в таблице рассмотрено в главе 14, в разделе «Ввод данных в таблицу».
Форматирование ячеек
Команда Формат ячеек (Format Cells) есть в контекстном меню, которое отображается после щелчка правой кнопкой мыши выделенной ячейки.
Форматирование строк и столбцов
Форматирование строки позволяет изменить ее высоту или скрыть, форматирование столбца — изменить его ширину или скрыть. Форматирование строки и столбца производят для более наглядного представления таблицы, ее заголовков, для выделения итоговых результатов.
Форматирования символов
Перечень всех установленных на компьютере шрифтов приводится в списке Шрифт (Font). По умолчанию в этом поле установлен шрифт Anal. Выбрав шрифт, нажмите кнопку мыши. Назначение отдельных элементов вкладки Шрифт рассмотрено в главе 6, в разделе «Использование различных шрифтов для оформления документа».
и защита листа Excel
Форматирование и защита листа Excel 2003
Форматирование ячеек
Форматирование строк и столбцов
Автоформат
Копирование форматов
Защита ячеек, листов и книг
Горизонтальное и вертикальное выравнивание текста и чисел в ячейках
Используя вкладку Выравнивание (Alignment) диалогового окна Формат ячеек (Format Cells), можно выполнить горизонтальное и вертикальное выравнивание текста и чисел в ячейках (рис. 15.1). Возможность расположить содержимое ячеек не только горизонтально, но под заданным углом позволяет освободить больше места для просмотра данных. При изменении выравнивания тип данных остается прежним.
В поле с раскрывающимся списком по горизонтали (Horizontal) можно выбрать один из следующих элементов:
по значению (General) — параметр выравнивания содержимого ячейки, используемый по умолчанию. Когда установлен этот параметр Excel выравнивает текст по левому краю, числа — по правому, а логические значения или сообщения об ошибках выравниваются по центру (ячейки А1 и В1 на рис. 15.2);
Рис. 15.1 Диалоговое окно Формат ячеек. Открытая вкладка Выравнивание позволяет задать выравнивание символов и поворот текста в ячейке
по левому краю (отступ) (Left (Indent)) — содержимое ячейки выравнивается по левому краю (ячейка С1);
по центру (Center) — содержимое ячейки выравнивается по центру (ячейка D1);
по правому краю (Right) — содержимое ячейки выравнивается по правому краю (ячейка А2);
с заполнением (Fill) позволяет заполнить ячейку повторяющимися символами, которые находились в ячейке на момент выбора. Например, если в ячейку будет введен символ «&123» после выбора этого параметра, то ячейка заполнится символами «&123&123» (ячейка В2);
по ширине (Justify) — содержимое ячейки выравнивается по левому и правому краю. В случае, если текст не помещается по ширине в одной строке, то он разбивается на несколько строк (ячейка СЗ);
по центру выделения (Center across selection) — содержимое ячейки центрируется не только по центру самой ячейки, но и по центру выделенного диапазона, что позволяет выровнять заголовки. Необходимый диапазон должен быть выделен до выбора команды и выбора элемента списка (диапазон А5:С7).
Рис. 15. 2 Различные варианты выравнивания содержимого ячейки по горизонтали
В раскрывающемся списке по вертикали (Vertical) (см. рис. 15.1) можно выбрать следующие элементы:
по верхнему краю (Тор) — содержимое ячейки выравнивается по верхнему краю ячейки (ячейка A3 на рис. 15.3);
по центру (Center) —содержимое ячейки выравнивается по центру ячейки (ячейка А4);
по нижнему краю (Bottom) — содержимое ячейки выравнивается по нижнему краю ячейки (ячейка А5);
по высоте (Justify) — содержимое ячейки выравнивается по верхнему и нижнему краям ячейки. Если текст не помещается в ячейку ее высота автоматически увеличивается для его размещения (ячейка А7).
Выбрав элемент списка нажмите кнопку ОК.
Рис. 15.3 Различные варианты выравнивания содержимого ячейки по вертикали
Использование команд меню для форматирования строк и столбцов
В диалоговом окне Высота строки (Row Height) (рис. 15.10) задайте размер выделенных строк в пунктах от 0 до 409,5 (пункт обозначается буквами pt, 1 pt = 0,352мм = 1/72 дюйма). При нулевом значении высоты строка становится скрытой. Стандартная высота строки составляет 1,275 высоты шрифта.
Чтобы изменить ширину одного или нескольких столбцов, выделите их заголовки, выберите в меню Формат (Format) команды Столбец, Ширина (Column, Width), и в диалоговом, окне Ширина столбца (Column Width) введите нужную величину в пунктах (рис. 15.11). Если задать 0, то столбец становится скрытым. С этой же целью можно выделить одну или несколько строк (группу ячеек) и выбрать в меню Формат (Format) команду Строка (Row), Скрыть (Hide).
Рис. 15.10 Диалоговое окно, используемое для задания высоты строки в пунктах
Рис. 15.11 Диалоговое окно, используемое для задания ширины столбца в пунктах
Команда Столбец (Column), Скрыть (Hide) в меню Формат (Format) позволяет не показывать выделенные столбцы, чтобы скрыть данные на экране и при печати или освободить место на экране для отображения соседних частей таблицы. (Аналогичная команда применяется для строк.)
Для отображения скрытых строк или столбцов на экране выделите столбцы или строки, между которыми должна появиться спрятанная часть таблицы, и воспользуйтесь в меню Формат (Format) командой Отобразить (Unhide). Для отображения всех скрытых строк листа выделите лист и выберите команды Строка (Row), Отобразить (Unhide) в меню Формат (Format). Для отображения всех скрытых столбцов листа выделите лист и выберите команды Столбец (Column), Отобразить (Unhide) в меню Формат (Format). (Лист можно выделить нажатием кнопки, которая находится на пересечении заголовков строк и столбцов.)
Чтобы вернуться к стандартной ширине столбца, выберите команды Столбец (Column), Стандартная ширина (Standard Width) в меню Формат (Format). Для установки высоты строки, соответствующей максимальному размеру шрифта, введенного в ячейки, выберите в меню Формат (Format) команды Строка (Row), Автоподбор высоты (AutoFit). Команду можно выбрать до ввода данных в ячейки строки.
Для задания ширины .столбца по самой заполненной в ширину ячейке выделите ее и выберите в меню Формат (Format) команды Столбец (Column), Автоподбор ширины (AutoFit) или выделите столбец и дважды щелкните его правую границу в области заголовка.
Использование рамок и обрамляющих линий в ячейках
Рамки и обрамляющие линии помогают более наглядно оформить создаваемый документ.
В группе Линия (Line) выберите, какой вариант линии вы хотите использовать.
В группе Все (Presets) можно отказаться от проведения границы, выбрав нет (None), или провести линии: внешние (Outline), внутренние (Inside).
В группе Отдельные (Border) щелчком мыши нажмите кнопки линий, которые вы хотите провести или снять. Например, чтобы провести левую границу, очерчивающую блок, нажмите кнопку с изображением этой линии
В раскрывающемся списке цвет (Color) выберите цвет границы рамки.
Нажмите кнопку ОК после того, как вы сделаете необходимые установки.
Рис. 15.6 Выбор границы для ячейки или блока ячеек
Изменение ширины столбца с помощью мыши
Для изменения ширины столбца с помощью мыши установите курсор на правой границе столбца и перемещайте ее до тех пор, пока ширина столбца не достигнет необходимого размера.. Для изменения ширины нескольких столбцов выделите их и переместите в строке заголовков столбцов правую границу. Чтобы изменить ширину всех столбцов на листе, нажмите кнопку Выделить все, а затем переместите границу заголовка любого столбца. Для подгонки ширины столбца в соответствии с содержимым его ячеек, установите указатель на правую границу заголовка, и дважды нажмите кнопку мыши.
Изменение высоты строки с помощью мыши
Для изменения высоты строки с помощью мыши установите курсор на нижнюю линию сетки в поле заголовков строк. Курсор примет вид перекрестия со стрелками, направленными в противоположные стороны. Удерживая нажатой кнопку мыши, переместите нижнюю границу заголовка строки до тех пор, пока высота строки не достигнет необходимого размера. Для изменения высоты нескольких строк выделите эти строки, а затем переместите нижнюю границу загголовка строки. Чтобы изменить высоту всех строк на листе, нажмите кнопку Выделить все, а затем переместите нижнюю границу заголовка любой строки. Для подгонки высоты строки с содержимым ее ячеек установите указатель на нижнюю границу заголовка, и дважды нажмите кнопку мыши.
Копирование форматов
Excel позволяет копировать не только содержимое ячейки или диапазона, но и их формат.
Копирование формата ячейки или диапазона диапазон
Чтобы скопировать формат одной ячейки в диапазон, выделите эту ячейку и выполните следующие действия:
выберите в меню Правка (Edit) команду Копировать (Сору);
выделите диапазон, в который будет скопирован формат;
выберите команду Специальная вставка (Paste Special) в меню Правка (Edit);
в диалоговом окне Специальная вставка (Paste Special) в группе Вставить (Paste) поставьте переключатель в положение Форматы (Formats) и нажмите кнопку ОК.
Формат выделенной ячейки можно скопировать с помощью кнопки Формат по образцу (Format Painter) панели инструментов Стандартная. Для выполнения копирования выполните следующие действия:
выделите ячейку, формат которой копируется;
нажмите кнопку Формат по образцу (Format Painter). Указатель принимает вид знака плюс, рядом с которым расположена кисть;
выделите ячейки, в которые копируется формат.
Копирование форматов прямоугольного диапазона в один или несколько диапазонов
Для копирования форматов прямоугольного диапазона в один или несколько диапазонов выделите диапазон или левую верхнюю ячейку диапазона, формат которого будет копироваться, и выполните те же действия, что при копировании формата одной ячейки.
Применение стилей
При оформлении книги Excel можно использовать различные форматы. Набор форматов, например размер шрифта, узоры и выравнивание, которые можно создавать и сохранять как единое целое называется стилем. По умолчанию для всех ячеек рабочего листа используется стиль Обычный. Для применения другoго стиля выделите ячейки, формат которых необходимо изменить, и в меню Формат (Format) выберите команду Стиль (Style). В поле Имя стиля (Style Name) выберите нужный стиль (рис. 15.13). Снимите флажки тех форматов, которые не будут применяться.
Рис. 15.13 Диалоговое окно Стиль
Для быстрого применения стандартных стилей к числам в выделенных ячейках нажмите кнопку Формат с разделителями (Comma Style), Денежный формат (Currency) или Процентный формат (Percent Style) на панели инструментов Форматирование. Если для оформления рабочего листа используется несколько форматов, то им можно присвоить имя стиля.
Различные способы отображения содержимого ячейки
В рамке Отображение (Text control) на вкладке Выравнивание (Alignment) диалогового окна Формат ячеек (Format Cells) (см. рис. 15.1) можно установить следующие флажки:
переносить по словам (Wrap text) — позволяет отображать данные в ячейке в нескольких строк, если в ячейке видны не все данные. Это дает возможность не менять ширину столбца или размер шрифта (ячейка А2 на рис. 15.4). Отметим, что можно не устанавливать флажок, а для разделения текста на отдельные строки в месте расположения курсора, нажать клавиши Alt+Enter;
объединение ячеек (Merge cells) — позволяет объединить две или более ячейки выделенного диапазона в одну. Во вновь образованной ячейке можно задать другой формат. Для ссылки на объединенные ячейки используется верхняя левая ячейка исходного диапазона (диапазон А4:В5);
Рис. 15.4 Различные способы отображения содержимого ячейки
автоподбор ширины (Shrink to fit) — обеспечивает уменьшение размеров символов шрифта так, чтобы содержимое ячейки умещалось в столбце (ячейка СЗ);. При изменении ширины столбца размер символов изменяется автоматически.
Рисование границ с помощью панели инструментов Форматирование
Чтобы использовать ранее выбранный тип рамки для рисования границ ячеек, нажмите кнопку Границы (Borders) на панели инструментов Форматирование. Для использования другого типа рамки нажмите стрелку рядом с кнопкой Границы (Borders) и выберите требуемый тип рамки из списка (рис. 15.7).
Рис. 15.7 Различные варианты проведения границы ячеек
Для изменения типа линии уже существующей рамки выделите ячейки, на которых рамка отображена. На вкладке Граница (Border) в поле Тип линии (Line) выберите необходимый тип, а затем в диаграмме, расположенной ниже поля Отдельные (Border), укажите границу, которую необходимо изменить.
На панели инструментов Форматирование нажмите стрелку рядом с кнопкой Границы (Borders) и выберите в раскрывающемся списке элемент Нарисовать границы (Draw Borders) (рис. 15.7). На экране отобразится панель инструментов Граница (рис. 15.8). Для рисования линий по границам ячейки или выделенного диапазона ячеек выберите инструмент Граница рисунка (Draw Border). Инструмент Сетка по границе рисунка (Draw Border Grid) позволяет быстро провести границы ячеек выделенного диапазона. Другие кнопки панели инструментов Граница позволяют выбрать вид и цвет линии. Отметим, что меню кнопки Нарисовать границы можно переместить мышью в удобное для пользователя место.
Рис. 15.8 Панель инструментов Граница. Инструменты: 1 — Нарисовать границу/Сетка по границе рисунка, 2 — Стереть границу, 3 — Вид линии, 4 — Цвет линии
Рисование границы ячеек
Для создания границы ячеек можно использовать карандаш. На панели инструментов Форматирование в раскрывающемся списке Границы выберите пункт Нарисовать границы. Используйте карандаш для создания необходимой границы. Имеется возможность изменения цвета, толщины и стиля линии а также отображения сетки по границам ячеек.
Удаление защиты листа или книги
Для удаления защиты листа выберите в меню Сервис (Tools) команду Защита (Protection), Снять защиту листа. Если для защиты листа был установлен пароль, то необходимо ввести этот пароль в диалоговое окно Снять защиту листа (Unprotect Sheet). Аналогичным образом можно снять защиту книгу, выбрав в меню Сервис (Tools) команду Защита (Protection), Снять защиту книги (Unprotect Workbook) (рис. 15.17). Если для защиты книги был установлен пароль, то следует ввести этот пароль и нажать кнопку ОК.
Рис. 15.17 Диалоговое окно, позволяющее снять защиту книги
в ней несколько наименований товаров
1. Создайте таблицу Excel, укажите в ней несколько наименований товаров и приведите их продажу за несколько месяцев. Выполните заголовок таблицы шрифтом Times New Roman, полужирный размер 20 и расположите erg в ячейке В15. Используйте средства Автозаполнения при вводе названий месяцев. Измените в таблице высоту строк. Для уменьшения ширины столбцов расположите их заголовки вертикально. Очертите границы таблицы жирной линией.
В качестве примера на рис. 15.18 показана таблица Продажа автомобилей. Заголовок таблицы написан полужирным шрифтом Times New Roman. Размер шрифта 26. Высота строки увеличена до 30. Для заголовков столбцов выделили диапазон B4:G4 и, выбрав команду Ячейки в меню Формат, открыли вкладку Шрифт и задали Anal, полужирный, размер 12. Для вертикального расположения заголовков столбцов использовали вкладку Выравнивание (Alignment). Ее открыли после выделения диапазона ячеек B4:G4, выбрав команду Ячейки в меню Формат. Для этого диапазона в меню Формат выбрали команду Строка, Автоподбор высоты.
Рис. 15.18 Пример форматирования таблицы
Для столбца В ввели Модель, для остальных — Январь, Февраль, Март, Апрель Май.
Для создания линий границ таблиц, выбрали команду Ячейки в меню Формат, открыли вкладку Граница, задали Тип линии для внешних и внутренних границ.
2. В таблице, приведенной в п.1 выполните форматирование заголовков. Выберите команду Ячейки (Cells) в меню Формат (Format), откройте вкладку Выравнивание (Alignment) окна Формат ячеек (Format Cells) (рис. 15.1) и проведите горизонтальное и вертикальное выравнивание заголовков. Расположите надписи горизонтально, вертикально и под углом 30 градусов.
3. Выберите команду Автоформат (AutoFormat) в меню Формат (Format) для форматирования таблицы, созданной согласно упражнению 1. Посмотрите, как изменится вид таблицы после выбора одного из форматов.
Вращение границ
Для применения рамок к выделенным ячейкам, содержащим повернутый текст, используйте кнопки Внешние (Outline) и Внутренние (Inside) на вкладке Граница (Border). Рамка будет проведена на границах ячеек, повернутых на тот же угол, что и текст.
Вращение текста
Данные в столбце часто занимают гораздо меньше места, чем заголовок столбца. Чтобы не создавать неоправданно широкие столбцы и не использовать сокращения в заголовках столбцов, можно повернуть текст, задав в группе Ориентация (Orientation) расположение текста в ячейках под углом к горизонтали (см. рис. 15.1). Угол поворота задается в поле градусов (Degrees) или перемещением до требуемого значения стрелки после слова Надпись (Text). Текст в ячейке можно расположить вертикально (в виде столбика) (ячейка Е1 на рис. 15.5).
Рис. 15.5 Различные способы ориентации содержимого ячейки
Выбор узора и цвета фона ячеек
Для более наглядного оформления данных можно для ячеек использовать различные цвета фона и узоры.
Рис. 15.9 Диалоговое окно Формат ячеек с открытой вкладкой Вид, позволяющей задать узор и цвет заливки ячеек
В раскрывающемся списке Узор (Pattern) можно выбрать шаблон узора ячеек.
В рамке Образец (Sample) демонстрируется внешний вид ячейки после выбора всех параметров.
Закрасить фон выделенных ячеек в понравившийся вам цвет можно также с помощью кнопки Цвет заливки (Fill Color)
Для форматирования ячеек выделите их,
1. Для форматирования ячеек выделите их, выберите команду Ячейки (Cells) в меню Формат (Format) и используйте соответствующую вкладку.
2. Чтобы изменить высоту строк, выделите заголовки строк, щелкните их правой кнопкой мыши и выберите в контекстном меню команду Высота строки или в меню Формат (Format) выберите команды Строка (Row), Высота (Height).
3. Чтобы изменить ширину одного или нескольких столбцов, выделите их заголовки, выберите в меню Формат (Format) команды Столбец (Column), Ширина (Width) и в диалоговом окне Ширина столбца (Column Width) задайте ширину столбца в пунктах.
4. Команда Автоформат (AutoFormat) в меню Формат (Format) позволяет профессионально оформить таблицу за счет использования стилей оформления таблицы.
5. Если скопировать формат Общий (General) в ячейку с датой, то дата будет отображена в виде числа, показывающего столько дней прошло с 1 января 1900 года, например, вместо 5 апреля 2001 года мы увидим число 36986.
6. Для защиты листа или рабочей книги от исправлений выберите в меню Сервис (Tools) команды Зашита (Protection), Защитить лист (Protect Sheet) или Защита (Protection), Защитить книгу (Protect Workbook).
Защита ячеек
Защита ячеек не действует, если не включена защита листа.
Защита ячеек, листов и книг
Excel позволяет избежать несанкционированного изменения данных, а также скрыть часть информации установкой защиты ячеек, листов и рабочих книг. Например, можно скрыть формулы, чтобы они не появлялись в строке формул, если вы не хотите показывать их посторонним.
Рис. 15.14 Диалоговое окно, позволяющее установить защиту ячейки или скрыть формулу
Защита книги
Защита книги, как правило, производится в тех случаях, когда информация, подлежащая защите, находится на нескольких листах. Защита паролем книги позволяет сохранить ее структуру и избежать вставки, перемещения или удаления листов.
В диалоговом окне Защита книги (Protect Workbook) (рис. 15.16) установите флажки:
структуру (Structure) — обеспечивает защиту структуры книги, что предотвращает удаление, перенос, скрытие, открытие, переименование и вставку новых листов;
окна (Windows) — предотвращает перемещение, изменение размеров, скрытие, показ и закрытие окон.
Рис. 15.16 Диалоговое окно, позволяющее установить защиту книги
При необходимости введите пароль в поле Пароль (Password).
Защита листов
Если необходимо защитить лист от несанкционированного внесения в него изменений, можно присвоить документу пароль, предотвращающий открытие файла пользователями, которые не имеют права доступа к документу.
Откроется диалоговое окно, показанное на рис. 15.15. В поле Пароль для отключения защиты листа (Password to unprotect sheet) введите пароль, который может содержать до 255 символов. При вводе пароля различаются строчные и прописные символы. В рамке Разрешить всем пользователям этого листа (Allow all users of this worksheet to) -можно установить флажки, разрешающие пользователям форматирование ячеек, столбцов, строк, вставку столбцов, строк, удаление столбцов, строк и т.д.
Рис. 15.15 Диалоговое окно, позволяющее установить защиту листа
Абсолютная и относительная ссылка
Ссылка — это указание адреса ячейки. Различают относительные или абсолютные ссылки.
Например, нам надо подсчитать сумму чисел, хранящихся в ячейках Al, A2, A3. Щелкнем дважды ячейку А5, и поместим в нее формулу=А1+А2+АЗ. Чтобы вычислить сумму чисел, хранящихся в ячейках Cl, C2, СЗ, можно не писать заново формулу, а скопировать ее из ячейки А5 в G5 (см. главу 14, раздел «Копирование и перемещение содержимого ячеек»). Excel автоматически изменит относительные ссылки и формула примет вид =С1+С2+СЗ. Измененную формулу можно увидеть, щелкнув ячейку С5. (Для отображения формул в окне приложения установите флажок формулы на вкладке Вид диалогового окна Параметры (рис. 19.8).)
Например, если скопировать формулу из ячейки А7 с абсолютными ссылками в ячейку С7, то формула не изменится. Для указания абсолютной ссылки используется знак доллара $. $А$7, $С$7 (рис. 16.3). Адреса ячеек, использованные в формуле, и границы этих ячеек будут закрашены в одинаковые цвета.
Рис. 16.3 Отображение формул с абсолютными, и относительными ссылками в окне Excel
В тех случаях, когда при копировании или перемещении формулы необходимо сохранить неизменным только номер строки или только наименование столбца, применяют смешанную ссылку, например $D7 или F$5. Более подробно использование абсолютной ссылки в формуле рассмотрено ниже в разделе «Копирование формулы».
Арифметические операторы
Арифметическими операторы получили наиболее широкое распространение. Они обеспечивают сложение, вычитание, умножение, деление, возведение в степень, нахождение процента по данным, приведенным в ячейках электронной таблицы. Примеры использования арифметических операторов приведены в таблице 16.2. Предполагается, что в ячейках Al, A2 и A3 содержатся числа 4, 5 и 10 соответственно.
Таблица 16.2. Примеры использования арифметических операторов
| Оператор | Пример формулы | Результат |
| + | А1+А2 | 9 |
| - | А2-А1 | 1 |
| * | А1*А2 | 20 |
| / | АЗ/А2 | 2 |
| л | АГ2 | 16 |
| % | 2Л3 | 8 |
Excel выполняет вычисления в формулах слева направо и соблюдает принятый в математике приоритет выполнения арифметических операций. Первыми выполняются операции возведения в степень, затем умножение и деление, в последнюю очередь сложение и вычитание, для изменения порядка выполнения операций используются скобки.
Скобки должны быть парными, пробелы перед скобками или после них не допускаются, например:
=(АЗ+15)/В4,
где A3, В4 — ссылки на ячейки, 15 — числовая константа, «+» и «/» — операторы сложения и деления.
Операции в скобках выполняются первыми. Например, в формуле =(А1+А2+АЗ)/3 сначала вычисляется сумма чисел, содержащихся в ячейках Al, A2 и A3, потом найденная сумма делится на 3, в то время как в формуле =А1+А2+АЗ/3 на 3 делится только последнее слагаемое, а не вся сумма. Внутри скобок можно помещать другие скобки, что называется вложением скобок.
Автокоррекция при вводе формул
Excel 2003 может распознать наиболее распространенные ошибки, допускаемые пользователями при вводе формул в ячейку. Например, автоматически исправляются ошибки, связанные с неправильными ссылками, полученными в результате перемещения ячеек. Введенный по ошибке символ «х» автоматически преобразовывается в знак умножения и т.д. При этом на экране появляется запрос о необходимости произвести исправление.
Использование поля Введите вопрос пая решения вопросов, связанных с использованием функции
Для решения вопросов, связанных с использованием функции, ее синтаксисом, определением к какой категории она относится, можно ввести название функции в поле Введите вопрос (Type a question for help) и нажать клавишу Enter. Так, если у вас возникли вопросы по использованию корреляционной функции, введите в поле: корреляция (correlation). Будут предложены возможные варианты использования функции (рис. 16.7).
Рис. 16.7 Информация, предоставляемая помощником о функции
Нажмите кнопку интересующего вас варианта ответа. В приведенной справке будет указано назначение функции, ее синтаксис и даны примеры.
Формула массива
Если формула массива возвращает одно значение, укажите ячейку, в которую необходимо ввести формулу. Если формула массива возвращает несколько значений, то выделите диапазон ячеек, в которые необходимо ввести формулу. Наберите формулу и нажмите клавиши Ctrl+Shift+Enter. При вводе формулы массива Excel автоматически заключает ее в фигурные скобки {} после нажатия указанных клавиш.
Использование массива позволяет ввести формулу один раз и не повторять ее для всех ячеек диапазона. В связи с тем, что массив обрабатывается как единый модуль, то нельзя перемещать или удалять часть массива.
Выполнение расчетов по формулам
Выполнение расчетов по формулам в Excel 2003
Использование формул в Excel
Операторы, используемые в Microsoft Excel
Ввод формулы в ячейку
Различные способы суммирования данных
Работа с формулами с клавиатуры
Использование функций
Копирование формул
Сообщение об ошибке
Обнаружение и исправление ошибок в выполненных расчетах
Окно контрольного значения
Настройка Excel на выполнение вычислений
Использование формул в Excel
Excel содержит мощные средства вычислений по формулам. Формула позволяет определить значения в заданной ячейке листа.
В качестве операндов могут использоваться постоянные значения (числовые или текстовые константы), ссылки на ячейки или диапазоны ячеек, заголовки, имена, функции, логические величины (например, ИСТИНА или ЛОЖЬ) и массивы. Константой считается число или текст, которые непосредственно вводятся в ячейку, например, текст «Московские известия».
Формула также может включать встроенные функции, которые обеспечивают выполнение стандартных вычислительных операций. В Excel используется более 200 встроенных функций.
Для обращения к ячейке введите ее адрес в формуле. Например, если ввести в ячейкуА2 формулу =СЗ, то после пересчета текущего листа Excel в ячейке А2 покажет текущее значение СЗ.
Использование функций
Excel содержит обширный список стандартных функций, призванных облегчить выполнение простых и сложных вычислений.
Например, функция ДОХОД используется для вычисления дохода по облигациям, который составляет периодические процентные выплаты. Все функции имеют одинаковый формат записи, который включает имя функции и перечень аргументов. Аргументы располагаются в последовательности, определяемой синтаксисом функции, и разделяются запятой.
Запись функции начинается с указания ее имени, затем следует открывающаяся скобка, аргументы и закрывающая скобка. Функция может не иметь аргументов. Она может вводиться в ячейку листа как часть формулы. Функция позволяет выполнить вычисления на листах книги и на листах макросов.
Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложения функций. При неправильной записи формулы на экране может появиться сообщение о циклической ссылке. Имена функций можно набирать строчными буквами. Они будут преобразованы в прописные после нажатия клавиши Enter.
Для вставки функции нажмите кнопку Вставка функции (Insert Function), На экране отобразится панель формул (Formula Toolbar)
Использование ссыпок в формуле
Формула может содержать ссылку на ячейку (ее адрес) или на диапазон ячеек, а также на имена, представляющие ячейки или диапазоны ячеек. Для описания ссылок на диапазоны ячеек используются операторы, приведенные в таблице 16.3.
Таблица 16.3. Операторы ссылки
| Операторы ссылки | Значение (пример) |
| : (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона. Такое сочетание является ссыпкой на диапазон (В5:В15). |
| ; (точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(В5:В15;О5:В15)). |
| (пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 С6:С8). |
Чтобы сослаться на диапазон ячеек, введите ссылку на верхнюю левую ячейку диапазона, поставьте двоеточие (:), а затем — ссылку на правый нижний угол диапазона. Например, А2:С5. Ссылка на все ячейки между 6-й и 15-й строками включительно имеет вид 6:15, на все ячейки в столбце С — С:С.
Для задания обращения ко всем ячейкам, находящимся в нескольких диапазонах одновременно используется пересечение. Для задания пересечения диапазонов используется пробел. Например, формула =СУММ(А2:ВЗ В2:СЗ) вычисляет сумму чисел в пересекающихся диапазонах А2:ВЗ и В2:СЗ. Для обращения ко всем ячейкам, включенным в два диапазона, используется объединение. Если в ячейке ВЗ записано Петров, а ячейке A3 — Владимир, то формула АЗ=ВЗ&", "&АЗ будет означать объединение указанных выше имен, разделенных запятой (Петров, Владимир). При использовании операторов объединения и пересечения удобно пользоваться именованными диапазонами.
В формулу можно вводить ссылки на ячейки различных рабочих листов и книг. Ссылки, распространяющиеся на несколько рабочих листов, называются трехмерными. Ячейка, содержащая формулу, называется зависимой ячейкой. Ее значение зависит от значений ячеек, на которые имеются ссылки в формуле.
Примеры записей диапазонов ячеек в функции:
=СУММ(Е:Е) — определяется сумма числовых значений, содержащихся в столбце Е;
=СУММ(Е2:С5) — подсчитывается сумма числовых значений, расположенных в диапазоне от Е2 до G5;
=СУММ(5:5) — суммируются данные всех ячеек пятой строки;
=СУММ(2;4) — подсчитывается сумма 2+4.
Допускается смешанная запись адресов ячеек и блоков ячеек. В этом случае формула может выглядеть следующим образом:
=СУММ(С8;О4;Е2:Е5;Р5)
Копирование формул
Копирование формул производят по тем же правилам, что и копирование данных листа. Ниже рассмотрены различные методы копирования формул.
Копирование формул с помощью команды Заполнить в меню Правка
При повторных вычислениях по одним и тем же формулам можно воспользоваться еще одним способом копирования формул — командой Заполнить (Fill) в меню Правка (Edit). В качестве примера используем эту команду для нахождение среднего арифметического значения данных по столбцам в ячейках В10 и В11, С10 и С11, D10 и D11 (рис. 16.10).
Введем в ячейки исходные данные.
Поставим курсор в ячейку В12 и выберем команду Функция (Function) в меню Вставка (Insert).
В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.6) в списке Категория (Function category) в раскрывающемся списке Категория выберем строку Статистические (Statistical), в списке Выберите функцию (Select a function) элемент СРЗНАЧ (AVERAGE) и нажмем кнопку ОК. В диалоговом окне Аргументы функции (Function Arguments) (рис. 16.6). Мастер функции предложит найти среднее арифметическое значения в ячейках В10:В11. После нажатия кнопки ОК в ячейке В12 появится результат.
Рис. 16.10 Копирование формул с помощью команды Заполнить в меню Правка
Выделим ячейки В12, С12 и D12.
Выберем в меню Правка (Edit) команду Заполнить (Fill), Вправо (Right).
Формула из ячейки В12 скопируется в ячейки С12 и D12 и мы увидим результаты вычислений. Если в ячейке В12 находилась формула =СРЗНАЧ(В10:В11) (AVERAGE)(B10:B11), то в ячейках'С12 и D12, она примет соответственно вид =СРЗНАЧ(С10:С11) и =CP3HA4(D10:D11).
Копирования формулы методом перетаскивания
Приведем пример копирования формулы методом перетаскивания. Предположим надо возвести в третью степень числа 5, 7 и 10, которые находятся в ячейках A3, А4 и А5. Результаты вычислений запишем соответственно в ячейки ВЗ, В4 и В5. Выделим ячейку ВЗ и введем в нее формулу =АЗА3 (символ «Л» используется как оператор возведения в степень). Нажмем клавишу Enter. В ячейке появится результат 125. Выделим еще раз ячейку ВЗ. Установим указатель мыши на маленький черный квадратик — маркер заполнения. Нажмем кнопку мыши и растянем рамку еще на две ячейки вниз. В выделенных ячейках отобразятся результаты вычислений: 343 и 1000. Щелкнем ячейку В4 — в строке формул увидим =А4Л3, т.е. относительный адрес ячейки изменился.
Если при копировании формулы необходимо оставить ее адрес неизменным, то используется абсолютная ссылка. Напишем формулу в виде =$А$3^3.
При копировании этой формулы в любое место таблицы всегда будут возведены в третью степень данные, находящиеся в ячейке A3.
Копирование формул с помощью команды Копировать в меню Правка
Чтобы скопировать формулу, выделите ячейку с формулой и выберите в меню Правка (Edit) команду Копировать (Сору). Затем выделите ячейку или диапазон ячеек, куда будет вставлена формула, и выберите команду Вставить (Insert) в меню Правка (Edit). Ячейки, в которые копируется формула, могут находиться на другом листе или в другой книге.
Нахождение ячеек, влияющих на заданную ячейку
Excel позволяет найти ячейки, влияющие на интересующую нас ячейку. Для определения этих ячеек выполните следующие действия:
выделите заданную ячейку;
выберите команду Перейти (Go To) в меню Правка (Edit) и нажмите кнопку Выделить (Special) в диалоговом окне Переход (Go To) (см. главу 13, рис. 13.2);
на экране отобразится диалоговое окно Выделение группы ячеек (Go To Special) (рис. 16.13). Чтобы найти ячейки, влияющие на заданную ячейку, установите переключатель влияющие ячейки (Precedents) и один из связанных с ним переключателей на всех уровнях (All levels) или только непосредственно (Direct only);
после того, как вы нажмете кнопку ОК, на листе будут отмечены все влияющие ячейки.
Рис. 16.13 Диалоговое окно Выделение группы ячеек
Настройка Excel на выполнение вычислений
Внесение новых данных в ячейки приводит к автоматическому пересчету всех формул. Если открытые листы содержат большое количество формул, которые требуют много времени на автоматический пересчет, то можно сократить временные затраты. Чтобы отменить автоматический пересчет после каждого внесения изменений в таблицу, выберите команду Параметры (Options) в меню Сервис (Tools), а затем откройте вкладку Вычисления (Calculation) (рис. 16.14).
В группе Вычисления (Calculation) можно установить переключатель в одно из положений:
автоматически (Automatic) — обеспечивает вычисление значений формул при каждом внесении изменений в данные, формулу или название. Эта установка используется по умолчанию;
автоматически кроме таблиц (Automatic except tables) — обеспечивает вычисление всех зависящих формул, за исключением таблиц данных. Пересчет таблиц данных будет производиться только при нажатии кнопки Вычислить (Calc Now), отображенной на вкладке, или клавиши F9;
вручную (Manual) —- выполнение вычислений на всех открытых листах будет производиться после нажатия кнопки Вычислить (Calc Now), отображенной на вкладке Вычисления или клавиши F9. Для выполнения расчетов только на активном листе нажмите клавиши Shift+F9. После установки переключателя в это положение Excel автоматически устанавливает флажок пересчет перед сохранением (Recalculate before save) с тем, чтобы обеспечить сохранение и учет всех введенных данных.
Установив флажок итерации (Iterations), в поля Предельное число итераций (Maximum iterations) и Относительная погрешность (Maximum change) можно ввести числовые значения.
Назначение некоторых флажков в разделе Параметры книги (Workbook options):
обновлять удаленные ссылки (Update remote references) — обеспечивает вычисление и обновление формул, содержащих ссылки на другие приложения;
точность как на экране (Precision as displayed) — понижает точность вычислений с внутреннего представления чисел (15 знаков после запятой) до точности, используемой при отображении ячеек на экране. Эта операция приводит к потере значащих разрядов, и отменить ее невозможно;
сохранять значения внешних связей (Save external link values) — сохраняет копии значений, содержащихся во внешнем документе, соединенном с листом Microsoft Excel. Если лист, связанный с большим диапазоном ячеек во внешних документах, требует большого количества дисковой памяти или открывается очень долго, снимите флажок, чтобы улучшить эти показатели.
Обнаружение и исправление ошибок в выполненных расчетах
Кнопка Проверка наличия ошибок (Error Checking tool) позволяет найти на рабочем листе неверные, с точки зрения Excel формулы. После нахождения ошибки отображается диалоговое окно с возможными вариантами исправлений. Если по вашему мнению ошибки нет, то нажмите кнопку Пропустить ошибку (Ignore).
Excel 2003 следит за тем, не имеет ли какая-либо ячейка, содержащая число, текстовый формат; контролирует, не берет ли какая-нибудь формула данные из пустой ячейки (вследствие неправильного указания ее параметров). Параметры отслеживания ошибок можно задать после выбора команды Параметры (Options) в меню Сервис (Tools) на вкладке Проверка ошибок (Error Checking). Если в ячейке обнаружена ошибка, эта ячейка помечается зеленым треугольником в левом верхнем углу.
Окно контрольного значения
Окно контрольного значения позволяет следить за тем, как меняются после ввода в таблицу новых исходных данных значения в ячейках связанных между собою формулами. Новые значения могут вводиться, например, для получения требуемого результата.
Для наблюдения за формулами и их результатами выполните следующие действия:
До отображения окна выделите ячейки для просмотра. Чтобы выделить на листе все ячейки, содержащие формулы, в меню Правка (Edit) выберите команду Перейти (Go To), нажмите кнопку Выделить (Special), а затем установите кнопку переключателя в положение формулы (formulas).
Рис. 16.14 Вкладка Вычисления диалогового окна Параметры, позволяющая ограничить время, затрачиваемое на вычисления
В меню Сервис (Tool) укажите на пункт Зависимости формул (Formula Auditing), а затем выберите команду Показать окно контрольного значения (Show Watch Window).
Нажмите кнопку Добавить контрольное значение. (Add Watch).
Нажмите кнопку Добавить (Add).
Переместите панель инструментов Окно контрольного значения в верхнюю, нижнюю, левую или правую часть окна.
Чтобы изменить ширину столбца, перетащите правую границу заголовка столбца.
Чтобы отобразить ячейку, на которую ссылается ячейка на панели инструментов Окно контрольного значения, дважды щелкните ячейку.
Отметим, что ячейки, которые имеют связи с другими книгами, отображаются на панели инструментов Окно контрольного значения, только если те книги открыты.
Указать ячейку, текущее значение которой будут отображаться в окне после вычисления по формуле, позволяет кнопка Показать окно контрольного значения (Add watch) панели инструментов Зависимости.
Операторы, используемые в Microsoft Excel
В Excel используют следующие операторы:
Арифметические операторы — применяются при работе с числами. Результатом выполнения арифметической операции всегда является число.
Операторы сравнения — используются для сравнения двух чисел. В результате выполнения операции сравнения получается логическое значение: истина или ложь.
Текстовый оператор — применяется для обозначения операции объединения нескольких последовательностей символов в одну последовательность символов.
Адресные операторы — используются при ссылках на ячейки. Назначение операторов приведено в таблице 16.1.
Таблица 16.1. Назначение используемых в Excel операторов
| Арифметические операторы | Назначение операторов |
| + (знак плюс) | Сложение |
| - (знак минус) | Вычитание (или унарный минус, например, —1) |
| / (косая черта) | Деление |
| * (звездочка) | Умножение |
| % (знак процента) | Процент |
| А (крышка) | Возведение в степень |
| Операторы сравнения |   |
| = (знак равенства) | Равно |
| > (знак больше) | Больше |
| < (знак меньше) | Меньше |
| >= (знак больше и знак равенства) | Больше или равно |
| <= (знак меньше и знак равенства) | Меньше или равно |
| <> (знак меньше и знак больше) | Не равно |
| Текстовый оператор |   |
| & (амперсанд) | Объединение двух текстовых строк в одну |
| Адресные операторы |   |
| : (двоеточие) | Ссылка на все ячейки между границами диапазона включительно. |
| , (запятая) | Ссылка на объединение ячеек диапазонов. |
| (пробел) | Ссылка на общие ячейки диапазонов. |
Отображение формулы в ячейке
При стандартной настройке в ячейке с формулой отображается результат вычислений, а не сама формула. В некоторых случаях, например, при составлении и проверки сложных расчетов в ячейке удобнее отображать не числовое значение, а саму формулу.
Для возврата к принятому режиму отображения формул снимите флажок. . Чтобы перейти в режим отображения формул или вернуться в обычный режим нажмите клавиши Ctrl+' (клавиша обратного апострофа находится на той же клавише, что и тильда «~» — ниже клавиши Esc).
Отслеживание ячеек, участвующих в вычислениях
Ячейки, из которых берутся значения для формулы в активной ячейке, называются влияющими. Ячейки, содержащие результат вычислений или формулы, полученные с использованием текущей в данной момент ячейки, называются зависимыми.
Проследить путь от исходных данных к результатам позволяют команды Зависимости формул (Formula Auditing), Влияющие ячейки (Trace Precedents) и Зависимые ячейки (Trace dependents) в меню Сервис (Tools) или одноименные кнопки на панели инструментов Зависимости (Formula Auditing).
В качестве примера проследим, какие ячейки влияют на результат вычислений по формуле, приведенной в ячейке F5 (рис. 16.12). Выделим ячейку F5 и нажмем кнопку Влияющие ячейки панели инструментов Зависимости. В таблице появятся стрелки, показывающие ячейки, участвующие в формуле, Чтобы убрать стрелки, нажмите кнопку
Перед тем, как отобразить панель инструментов Зависимости следует убедиться, что на вкладке Вид (View) (см. главу 19, рис. 19.8) в окне команды Параметры (Options) из меню Сервис (Tools) переключатель в группе Объекты (Objects) не стоит в положении не отображать (Hide all).
Рис. 16.12 Отображение стрелок, показывающих, какие ячейки участвуют в получении результата
После выделения ячейки, содержащей ошибку, можно нажать кнопку
Кнопка Убрать все стрелки (Remove All Arrows) удаляет все стрелки на вя листе
Панель аудита формул
Панель аудита формул предлагает ряд элементов управления для доступа к функциям аудита, таким как средство проверки формул и окно контрольного значения. Данная функция позволяет выполнить вычисление по шагам и понять, каким образом получается итоговый результат. Она также предоставляет некоторые дополнительные возможности, например переключение в режим отображения всех формул на листе.
Для проверки вычислений в формулах выделите ячейку, которую необходимо проверить. Откройте панель аудита формул и нажмите кнопку Вычислить формулу ().
Подсказки аргументов функций
При создании новой формулы в Excel отображается информация об аргументах функции. Кроме того, всплывающие подсказки обеспечивают быстрый доступ к нужным разделам справки. Пользователю достаточно щелкнуть мышью имя любой функции или аргумента, отображаемых во всплывающей подсказке.
Работа с формулами с клавиатуры
При работе с формулами с клавиатуры используют клавиши, приведенные в таблице 16.3.
Таблица 16.4 Клавиши, используемые при работе с формулами с клавиатуры
| Выполняемая операция | Клавши |
| Начать формулу | Знак равенства |
| Скопировать формулу (значение) верхней ячейки в текущую ячейку или в строку формул | Ctrl+' (апостроф) |
| Выполнить автосуммирование | Alt+= (знак равенства) |
| Пересчитать текущий лист | Shift+F9 |
| Скопировать содержимое верхней ячейки в текущую ячейку или з строку формул | Ctrl+Shift+" (двойная кавычка) |
| Переключить режимы отображения значения ячейки и формулы ячейки | Ctrl+' (знак левой кавычки) |
| Ввести набранную формулу в качестве ,формулы массива | Ctrl+Shift+Enter |
| После ввода имени функции в формулу отобразить панель формул | Ctrl+Ф |
| Вставить в круглых скобках список аргументов, после набора в формуле имени функции | Ctrl-fShift+Ф |
Различные способы суммирования данных
В большинстве случаев Excel предусматривает несколько вариантов выполнения расчетов. Покажем это на примере суммирования данных. Для сложения чисел в диапазоне ячеек используется функция СУММ, которая является самой часто используемой функцией.
Введем слагаемые в ячейки D3, D4, D5. Суммирование можно выполнить следующими способами:
1 способ.
Выделим ячейку D6 и введем в нее формулу для расчета, сделав ссылку на ячейки, содержащие исходные данные: -D3+D4+D5.
Нажмем клавишу Enter, и в ячейке D6 появится результат.
2 способ.
Введем знак «=» в ячейку D6, щелкнем ячейку D3 — после знака равенства в ячейке D6 отобразится адрес ячейки D3.
Поставим знак «+» и щелкнем ячейку D4. В ячейке D6 появится запись «=D3+D4». Введем знак «+», щелкнем ячейку D5 и нажмем клавишу Enter. В ячейке D6 появится результат.
3 способ.
Щелкнем ячейку D6 и нажмем кнопку Автосумма (AutoSum) на панели инструментов Стандартная. В ячейке появится запись =СУММ(ВЗ:О5), т.е. предлагается провести суммирование данных, записанных в ячейках от D3 до D5, расположенных в одном столбце с D6.
Нажмем клавишу Enter и мы увидим результат суммирования.
При выделении диапазона ячеек и нажатии кнопки Автосумма (AutoSum) в пустую ячейку, следующую за диапазоном, будет вставлена формула подсчета суммы этих ячеек. Эта возможность также может быть использована для подсчета общей суммы по столбцу, содержащему частичные суммы данного столбца.
Смарт-тег проверки ошибок
0 возможной ошибке в ячейке пользователя оповещает смарт-тег проверки ошибок. Пользователю предлагается исправить ошибку, пропустить ее или получить доступ к дополнительным параметрам средства проверки ошибок. Попробуйте вычислить сумму по столбцу, пропустив последнюю ячейку группы. Наведите указатель на ячейку, содержащую сумму, чтобы отобразились варианты, предлагаемые функцией проверки ошибок.
Сообщение об ошибке
Сообщение об ошибке начинается со знака #, например, если при выполнении расчетов в ячейке появится #ЗНАЧ!, то это означает, что программа не может найти исходные данные. В зависимости от причины возникновения ошибки меняется вид сообщения. Так сообщение #ДЕЛ/0! (#DIV/0!) появляется, когда в формуле предлагается провести деление на ноль (рис. 16.11). При подводе указателя мыши к значку смарт-тега рядом с ними отображается, кнопка. Щелкните значок, чтобы открыть меню, из которого вы узнаете, какого типа ошибка обнаружена, сможете просмотреть этапы вычислений, провести изменения в строке формул и т.д.
Рис. 16.11 Сообщение об ошибке
Если Excel считает, что ошибку во введенном выражении можно исправить, то появится окно с предложением, как отредактировать формулу.
Средства статистического анализа данных
В состав Microsoft Excel входит пакет анализа, предназначенный для решения сложных статистических и инженерных задач. Средства, которые включены в пакет анализа данных, доступны через команду Анализ данных в меню Сервис. Если этой команды нет в меню, необходимо загрузить надстройку Пакет анализа.
Средства анализа данных позволяют выполнять несколько видов дисперсионного анализа, корреляционный и ковариационный анализ, создавать одномерный статистический отчет, содержащего информацию о центральной тенденции и изменчивости входных данных, производить выборку из генеральной совокупности и т.д.
и 44, используя метод копирования
Найдите значения 24, З4 и 44, используя метод копирования формулы перетаскиванием.
1. Используйте ячейку A3 для расчета выражения 5+(2*3-1)/5. Обратите внимание, что в ячейке отобразится результат, а в строке формул — расчетная формула. Не забудьте поставить знак равенства перед тем, как начать вводить формулу.
2. Для проверки, как работает формат времени, решите простейшую задачу: поезд отправляется в 22 часа и. через 4 часа прибывает на станцию назначения. Определить время прибытия поезда на станцию назначения. Решение: запишите в ячейке С2 время 22:00 и в ячейку СЗ — время 4:00. Выделите ячейку С4. и нажмите кнопку Автосумма (AutoSum)
Нажмите клавишу Enter. В ячейке появится результат 2:00.
3. Создайте ведомость, учитывающую продажу компьютеров через магазин, которая показана на рис. 16.4. Для этого выполните следующие операции:
выделите ячейки А1,В1,С1иВ1и выберите в меню Формат команду Ячейки. Откройте вкладку Выравнивание и поставьте флажок Объединение ячеек (см. рис. 15.1);
введите в ячейку А1 текст: «Продажа компьютеров»;
выберите в меню Формат команду Строка, Автоподбор высоты, чтобы был виден заголовок, написанный более крупным шрифтом;
выделите текст и, раскрыв список Размер на панели форматирования, увеличьте размер шрифта до 20 пунктов;
нажмите кнопку Ж на панели форматирования, чтобы заголовок был написан полужирным шрифтом;
увеличьте ширину столбцов А, В, С и D, выбрав в меню Формат команду Столбец, Автоподбор ширины чтобы была виден весь текст в столбцах;
введите в ячейку А2 название столбца Модель; в В2 — Цена, в С2 — Количество, в D2 — Сумма;
выделите все четыре ячейки, установите на панели форматирования для них размер 14 и расположение По центру;
заполните ячейки, в которых приводятся модели, цены и количество;
выделите ячейку D3 и введите в нее формулу =ВЗ*СЗ;
установите курсор на маркере заполнения ячейки D3 (маркер заполнения расположен в правом нижнем углу ячейки и имеет вид черно го квадратика) и, нажав кнопку мыши, растяните вниз рамку так, чтобы все строки с моделями компьютеров попали в рамку (в данном случае это ячейки D3 — D5). В ячейках D4 — D5 будут отображаться результаты расчетов по скопированным формулам;
Рис. 16.4 Пример таблицы, исходные данные которой используются для вычислений по определенным формулам
в ячейку А7 введите Итого;
выделите ячейку D7, нажмите кнопку Автосумма (AutoSum) на панели Стандартная. В ячейке появится запись = CУMM(D3:D6). Нажмите клавишу Enter;
выделите все ячейки таблицы, и на панели форматирования в раскрывающемся списке границы нажмите кнопку Все границы, обеспечивающую проведение линий наружных границ таблицы и границ между ячейками.
1. Excel помогает вам найти вычислить различные справочные данные, не пользуясь справочниками. Найдите десятичный логарифм числа 250. Выполните упражнение следующим образом:
Выделите ячейку, в которую надо вставить функцию, и нажмите кнопку Вставка функции (Insert Function)
2. Найдите значения тригонометрических функций: синуса, косинуса, тангенса, котангенса для угла 10 градусов.
Рис. 16.8 Вычисление десятичного логарифма.
Выделите ячейку, в которую надо вставить функцию, и нажмите кнопку Вставка функции (Insert Function)
3. Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 образцов и испытаны на излом. Значения выборки (1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303, 1299) сохранены в ячейках А2:ЕЗ соответственно. Оцените дисперсию по выборке. Для решения упражнения используем статистическую функцию. Сделаем текущей первую свободную ячейку в столбце А, например #* А4, и нажмем кнопку Вставка функций (Paste Function)
Функция ДИСП предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, вычисляйте дисперсию, используя функцию ДИСПР. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, пользуйтесь функцией рабочего листа ДИСПА (VARA). Синтаксис функции: ДИСП(число1;число2;...)
Число1, число2, ... — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.
Нажмите кнопку ОК или клавишу Enter. В окне Excel ниже строки формул отобразится панель формул. Выделите диапазон ячеек А2:ЕЗ, в котором хранятся данные. Внизу панели формул увидите надпись Значение (Formula Result): 754,3. Нажмите кнопку ОК. Таким образом, дисперсия сопротивления на излом для всех инструментов будет равна 754,3.
Рис. 16.9 Окно, используемое для вычислений функции времени
4. Найдите значение времени в виде десятинной дроби для 8 часов 53 минут 14 секунд. Выполните упражнение следующим образом: Нажмите клавиши Shift+F3. В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) в раскрывающемся списке Категорию выделите строку Дата и время (Date& Time), а в нижнем списке Выберите функцию (Select a function) — ВРЕМЯ (Time) (рис. 16.5) и нажмите кнопку ОК. На экране отобразится диалоговое окно Аргументы функции (Function Arguments) (рис. 16.9). В поле Час (Hour) введите 8, в поле Минута (Minute) — 53, в поле Секунда (Second) — 14 и нажмите кнопку ОК. В ячейке получим значение 0,370301.
Внешние и умаленные ссылки
Можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками.
Ввод формулы в ячейку
Для ввода формулы в ячейку выделите ее и введите в строку формул или в ячейку знак «=».
При переходе в режим ввода формул поле Имя (Name), расположенное в левой части строки формул, заменяется кнопкой, на которой отображена последняя использовавшаяся функция. Расположенная справа от нее кнопка со стрелкой открывает список, который содержит 10 последних использовавшихся функций и пункт Другие функции (More/unctions) (рис. 16.1).
Рис. 16.1 Окно Excel в режиме ввода формул
В качестве примера введем формулу в ячейку D4. Закончив ввод формулы, нажмем клавишу Enter или щелкнем в строке формул по кнопке Ввод (Enter). В ячейке, содержащей формулу, отобразится результат вычисления, сама формула будет видна в строке формул (рис. 16.2).
Рис. 16.2 Отображение расчетной формулы в строке формул
Ввод в формулу ссылок на ячейки с помощью мыши
Так, если вам необходимо после знака = в формуле дать ссылку на ячейку А4, щелкните на этой ячейке мышью, вокруг ячейки появится бегущая рамка, а в формуле отобразится ссылка на эту ячейку. Для ввода ссылки на диапазон ячеек щелкните по угловой ячейке диапазона и, удерживая нажатой кнопку мыши, перетащите указатель в противоположный угол для выделения всего диапазона.
Выполнение расчета с использованием стандартной функции
С Excel 2003 поставляются следующие стандартные функции:
Финансовые.
Дата и времени.
Математические.
Статистические.
Ссылки и массивы.
Работа с базой данных.
Текстовые.
Логические.
Проверка свойств и значений.
Рассмотрим несколько наиболее широко распространенных функций.
Математические функции
Математические функции используются в научных и инженерных расчетах для выполнения различных математических операций: вычисления логарифмов, тригонометрических функций и т.д. Пример использования математической функции рассмотрен ниже в упражнениях 1 и 2.
Статистические функции
Статистические функции используются для анализа диапазонов данных, вычисления параметров, характеризующих случайные величины, представленных множеством чисел, или их распределений, например, стандартного отклонения, среднего значения, и т.п. В частности, мы можем найти уравнение прямой или экспоненциальной кривой, оптимально согласующейся с опытными данными. Пример использования статистической функции рассмотрен ниже в упражнении 3.
Функции для работы с датами и временем
Для работы с датами и временем используется более десятка функций. Выберите команду Функция (Function) в меню Вставка (Insert). В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.6) в списке или категорию (Function category) выделите Дата и время (Date&Time).
Пример использования функции Дата и время (Date&Time) рассмотрен ниже (см. Упражнения).
Текстовые
Функция Текстовые (Text) преобразует числовое значение в форматированный текст, и результат больше не участвует в вычислениях как число. Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.
Например, в ячейке ВЗ вы можете записать фамилию продавца (Петров), в ячейке СЗ — объем его продаж (5000). После записи в какой-нибудь ячейке ВЗ& «продал»&ТЕКСТ(СЗ; «0,00 руб.»)&" единиц товара» при проведении вычислений произойдет объединение содержимого ячеек в одну фразу: Петров продал на 5000,00 руб. единиц товара.
Выполнение расчета с использованием стандартных функций
Чтобы выполнить расчет, используя стандартную функцию, выполните следующие действия:
1. Выделите ячейку, в которую надо вставить функцию, введите «=», а затем в раскрывающемся списке Функции в строке формул выберите нужную из списка (см. рис. 16.1). На экране отобразится диалоговое окно Аргументы функции (Function Arguments) (рис. 16.6). Если в раскрывающемся списке выбрать Другие функции (More functions) то откроется диалоговое окно Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.5).
Другие способы отображения диалогового окна Мастер функций — шаг 1 из 2 (Insert Function):
нажмите кнопку Вставка функции (Insert Function) в строке формул;
выберите команду Функция (Function) в меню Вставка (Insert);
нажмите клавиши Shift+F3.
2. В диалоговом окне Мастер функций — шаг 1 из 2 (Insert Function) (рис. 16.5) в поле Поиск функции (Search for a function) введите описание действия, которое вы хотели бы выполнить. Например, чтобы найти функции, связанные с логарифмами чисел, введите log. Если вы не знаете, к какой категории относится ваша функция, то в раскрывающемся списке или категория (Or select a category) выберите строку Полный алфавитный перечень (АН) и просмотрите список всех функций в алфавитном порядке. Полоса прокрутки позволяет просмотреть невидимые в данный
Рис. 16.5 Диалоговое окно мастера функций — шаг 1 из 2
момент элементы списка. В нижней части окна дается определение выделенной функции и ее аргументов. Чтобы получить описание функции, выберите функцию в списке и щелкните ссылку Справка по этой функции (Help on this function). Выделите нужную строку в списке Выберите функцию (Select a function) и нажмите кнопку (Жили клавишу Enter. 3. На экране отобразится диалоговое окно Аргументы функции (Function Arguments) (рис. 16.6). В верхней части окна размещаются поля, предназначенные для ввода аргументов, в нижней части — справочная информация: имя выбранной функции, все ее аргументы, назначение функции и каждого аргумента, текущий результат функции и всей формулы.
В тех случаях, когда аргумент приведен полужирным шрифтом, он является обязательным, если обычным шрифтом, то его можно пропустить. Чтобы панель формул не закрывала диапазон ячеек с данными, ее можно переместить, удерживая нажатой кнопку мыши. Чтобы ввести в качестве аргумента ссылку на ячейку, щелкните значок
Высота диалогового окна уменьшиться. Выбрав ячейки, щелкните значок, позволяющий вернуть диалоговому окну первоначальный размер. Если диалоговое окно Аргументы функции (Function Arguments) позволяет ввести несколько аргументов, то переход от одного поля аргумента к другому можно выполнять клавишей Tab. После ввода аргументов будет выведен текущий результат.
Рис. 16.6 Использование панели формул для оценки дисперсии по выборке
В этой главе вы познакомитесь
В этой главе вы познакомитесь со следующими вопросами, связанными с выполнением расчетов по формулам:
ввод формулы в ячейку;
использование в формуле ссылок и функций;
копирование формул;
настройка Excel на выполнение вычислений.
в Excel начинается со знака
1. Формула в Excel начинается со знака равенства «=», за которым следуют вычисляемые элементы (операнды), разделенные операторами. При изменении хотя бы одного значения в ячейках, участвующих в формуле, автоматически происходит перерасчет результата по новым данным. Результат вычисления помещается в ячейку, в которой находится формула.
2. Относительные ссылки автоматически изменяются при копировании формул в другие строки и столбцы. Абсолютная ссылка сохраняет адрес определенной ячейки независимо местоположения ячейки с формулой формулы.
3. Копирование упрощает ввод в таблицу однотипных формул. При копировании формулы автоматически изменяются относительные ссылки ячеек, входящие в формулу, в соответствии с ее новым положением на листе книги.
4.При проверке выполненных расчетов можно проследить путь от исходных данных к результатам с помощью команды Зависимости формул (Formula Auditing), Влияющие ячейки (Trace Precedents) в меню Сервис (Tools) или панели инструментов Зависимости (Formula Auditing).
5. Чтобы отменить автоматический пересчет после каждого внесения изменений в таблиду, содержащую большое количество формул, которые трeбуют много времени на автоматический пересчет, выберите команду Параметры (Options) в меню Сервис (Tools), а затем откройте вкладку Вычисления (Calculation) (рис. 16.14). Чтобы изменить предельное число итераций и относительную погрешность, ограничивающие итерации, установите флажок итерации (Iterations), введите значения в поля Предельное число итераций (Maximum iterations) и Относительная погрешность (Maximum change).
