Электронная таблица
ТЕМА ЗАНЯТИЯ
На этом занятии вы познакомитесь со структурой электронных таблиц Excel и научитесь выполнять следующие операции:
вводить данные;
копировать и перемещать листы Excel;
выделять ячейки таблицы;
форматировать текст;
настраивать ячейки;
форматировать числа;
добавлять строки и столбцы.
Электронные таблицы предназначены для ввода и обработки табличных данных. С помощью Excel можно выполнять сложные вычисления с большими массивами чисел, строить диаграммы и печатать финансовые отчеты.
Попробуйте самостоятельно заполнить данными один лист электронной таблицы.
Переключитесь на лист Лист4.
Измените название этого листа на Календарь.
Введите в ячейку A3 символы Пн.
Перетащите маркер выделения вниз, чтобы ячейки с A3 по А9 заполнились сокращенными названиями дней недели.
В прямоугольную область B3:F9 введите числа календаря на текущий месяц.
В строке 1 введите заголовок Календарь на текущий меся ц, объедините для него несколько ячеек и выровняйте заголовок по правому краю диапазона.
Добавьте обрамление жирной линией вокруг блока ячеек A3:F9.
Отделите линией столбец А от столбца В.
Как быстро добавить вертикальную линию?
Залейте фон ячеек строк 8 и 9 красным цветом, а шрифт текста этих строк выделите жирным начертанием.
Как изменить фон ячеек?
Объедините ячейки А11:С11 и введите в них текст Всего дней в месяце.
В ячейку D11 вставьте формулу, подсчитывающую число дней месяца.
Как подсчитать число дней месяца?
Скопируйте текущий лист в новую книгу Excel с одним листом и сохраните новую книгу в файле Календарь.xls.
В книге Электронная таблица.xls раскройте лист Клиенты(2).
Выделите строку 9 и удалите ее.
Переименуйте этот лист, дав ему название Формулы. Затем сохраните документ.
На этом занятии вы узнали, как добавлять, копировать и переименовывать листы Excel, строить таблицы, оформлять и форматировать их. Вы познакомились с разными форматами чисел и научились настраивать собственные стили.
В упражнениях занятия вы строили формулы для расчета среднего значения, суммы и количества чисел. Вычисление статистических величин — одно из основных назначений электронных таблиц. Расчеты в Excel выполняются с помощью формул. На следующем занятии вы узнаете основные правила построения формул и познакомитесь с некоторыми функциями Excel.
Понятие электронной таблицы
Документ приложения Excel называется рабочей книгой (workbook) или просто книгой Excel. Такая книга состоит из листов (worksheet), которые представляют собой большие таблицы ячеек с числами и текстовой информацией. Таблицы Excel похожи на базы данных, но предназначены не столько для хранения информации, сколько для проведения математических и статистических расчетов.
Книга Excel
Давайте создадим пустую книгу Excel, введем в нее числа и формулы и поучимся манипулировать с ее листами.
Запустите Excel с помощью команды Пуск > Программы > Microsoft Excel (Start > Programs > Microsoft Excel). В окне Excel сразу откроется новая рабочая книга с тремя листами.
Рис. 9.1.
Лист Excel
Щелкните в ячейке А1 первого листа и введите текст Клиенты.
Нажмите клавишу Enter и введите текст Январь.
Перемещаясь по ячейкам с помощью клавиш со стрелками, клавиш Enter и Tab или с помощью мыши, сформируйте таблицу, показанную на рис. 9.1.
Щелкните на ячейке А9 и введите слово Итого.
Нажмите клавишу Tab и введите =СУММ(ВЗ : В7) (=SUM(B3 : В7)). Эта форму ла автоматически подсчитывает сумму ячеек столбца Январь.
Снова нажмите клавишу Tab и введите 4юрмулу=СУММ(СЗ:С7) (=SUM(C3 :С7)).
Добавьте подобные формулы во все ячейки строки 9, указывая в скобках букву текущего столбца. Теперь Excel автоматически суммирует числа в столбцах и выводит результат в строку 9, как показано на рис. 9.2.
Рис. 9.2.
Таблица с формулами
Щелкните на ячейке D9. Обратите внимание, что в выделенной ячейке с формулой выводится результат расчета, а сама формула видна в строке формулы в верхней части окна Excel. Попробуйте щелкнуть на любой ячейке верхних пяти строк таблицы и изменить находящееся в ней число. Excel автоматически скорректирует сумму столбца в строке Итого. Таким образом, вам теперь не нужно по несколько раз пересчитывать суммы столбцов на калькуляторе. Excel мгновенно реагирует на любые изменения таблицы, и вы всегда
знаете точные итоговые значения. Конечно, Excel умеет выполнять над содержимым ячеек таблицы и более сложные математические операции. По умолчанию книга Excel имеет три листа со стандартными именами. Трех листов может не хватить для всех данных, а стандартные названия листов никак не раскрывают их назначение. Давайте научимся добавлять листы и назначать им более понятные имена.
Примечание
Если значение в ячейке с формулой не обновляется автоматически, выберите команду Сервис > Параметры (Tools > Options), раскройте вкладку Вычисления (Calculation) и выберите положение переключателя Автоматически (Automatic). Затем щелкните на кнопке ОК.
Примечание
Чтобы по умолчанию в книге появлялось другое количество листов, выберите команду Сервис > Параметры, раскройте вкладку Общие (General) и измените величину в счетчике Листов в новой книге (Sheets In New Workbook).
Щелкните правой кнопкой мыши на корешке Лист1.
Выберите в контекстном меню команду Переименовать (Rename).
Введите новое имя Клиенты.
Дважды щелкните на корешке Лист2 и введите имя Расходы.
Щелкните на корешке Расходы правой кнопкой мыши и выберите в контек стном меню команду Переместить/скопировать (Move Or Copy).
В открывшемся окне диалога (рис. 9.3) установите флажок Создавать копию (Create A Copy), чтобы выбранный лист копировался, а не перемещался.
Рис.9.3.
Копирование листа
В списке Перед листом (Before Sheet) щелкните на пункте переместить в конец (Move To End), чтобы скопировать первый лист в конец книги.
Затем щелкните на кнопке ОК.
Примечание
Чтобы скопировать выбранный лист в новую рабочую книгу с одним листом, выберите в раскрывающемся списке В книгу (То Book) пункт Новая книга (New Book).
Чтобы добавить еще один пустой лист, щелкните правой кнопкой мыши на корешке Расходы и выберите команду Добавить (Insert).
На вкладке Общие (General) открывшегося окна диалога Вставка (Insert) щелкните на значке Лист (Worksheet). Затем щелкните на кнопке ОК. В рабочей книге появится пятый лист.
Выберите команду Файл > Сохранить (File > Save).
В появившемся окне диалога сохранения документа щелкните на кнопке Мои документы (My Documents).
В поле Имя файла (File Name) введите название Электронная таблица. Затем щелкните на кнопке ОК, чтобы сохранить созданную книгу Excel.Обратите внимание, что документы Excel имеют расширение xls.
Ввод данных
Электронные таблицы обрабатывают числовые данные, которые размещаются в ячейках листа Excel. Столбцы и строки таблицы могут иметь текстовые названия. На листе Excel могут также располагаться заголовки, подписи и дополнительные ячейки данных с пояснительным текстом. Ввод информации — это один из первых шагов построения электронной таблицы. В этом упражнении вы изучите некоторые приемы ввода данных.
Откройте файл, созданный в предыдущем упражнении, и щелкните на корешке листа Расходы.
Щелкните на ячейке ВЗ и введите в нее текст Январь.
Excel позволяет автоматически заполнять ячейки листа последовательными логически связанными значениями. Поместите указатель на квадратный маркер в правом нижнем углу активной ячейки.
Нажмите кнопку мыши и протащите указатель вправо, чтобы рамка охватила ячейки с ВЗ по НЗ (рис. 9.4), и отпустите кнопку мыши. Семь ячеек строки заполнятся последовательными названиями месяцев года.
Рис. 9.4.
Заполнение ячеек последовательными значениями
Введите в ячейку А4 число 2002, а в ячейку А5 число 2005.
Щелкните на клетке А4.
Нажмите клавишу Shift и щелкните на ячейке А5, чтобы выделить сразу две ячейки. Для выделения ячеек листа Excel можно пользоваться следующими приемами:
щелчок на ячейке выделяет ее;
чтобы выделить прямоугольную область, щелкните на ее угловой клетке, нажмите клавишу Shift и щелкните на клетке противоположного угла области либо протащите мышь по диагонали от первого угла области до второго;
строка или столбец выделяются щелчком на кнопке заголовка строки или столбца;
чтобы выделить несколько строк или столбцов, протащите мышь по их заголовкам или щелкните па первом заголовке, нажмите клавишу Shift и щелкните на последнем заголовке группы;
нажав клавишу Ctrl и щелкая в клетках листа, можно выделить несколько разрозненных ячеек;
чтобы выделить весь лист, щелкните на кнопке листа, расположенной на пересечении линеек заголовков строк и столбцов.
Примечание
Если шаг 4 привел к заполнению всех семи ячеек словом Январь, значит, в Excel не задан список последовательных названий месяцев на русском языке. Чтобы добавить список значений для автоматического ввода, выберите команду Сервис ^ Параметры, раскройте вкладку Списки (Custom Lists). В поле Элементы списка (List Entries) введите последовательные элементы списка (Январь, Февраль, Март и т. д.) по одному на каждую строку и щелкните на кнопке Добавить (Add).
Протащите угловой маркер рамки выделения вниз, чтобы рамка охватила ячейки с А4 но А12. Указанная серия ячеек будет заполнена последовательными числами с шагом 3, который определяется разницей чисел, введенных в две первые ячейки серии.
Введите 1 в ячейку В4 и 3 в ячейку С4. Выделите эти две ячейки и протащите маркер рамки вправо, чтобы охватить все клетки строки вплоть до Н4.
Протащите маркер полученной рамки шириной в 7 клеток вниз, чтобы охватить все строки вплоть до 12. Теперь таблица будет выглядеть так, как показано на рис. 9.5. С помощью подобных приемов таблица быстро заполняется исходными значениями, которые позже можно корректировать по мере надобности. Чтобы таблица стала более попятной, ее нужно снабдить заголовком. Ниже таблицы полезно добавить несколько итоговых значений, рассчитанных на основе данных таблицы,
Введите в ячейку В 1 текст Расходы компании по месяцам. Текст заголовка не влезает в одну ячейку, поэтому следует объединить несколько ячеек.
Примечание
В последней версии Office XP появился новый способ, облегчающий ввод данных. Обратите внимание на значок параметров автозаполнения, который появляется после завершения операции заполнения рядом с угловым маркером последней заполненной ячейки. Раскрывающийся список содержит возможные параметры заполнения.
Рис. 9.5.
Автозаполнение ячеек
Выделите клетки с В1 по Gl.
Щелкните на кнопке Объединить и поместить в центре (Merge And Center) пане ли инструментов Форматирование (Formatting).
Выделите ячейки с В14 по Е14 и щелкните на кнопке Объединить и поместить в центре.
Введите текст Среднее значение таблицы. По окончании ввода текста или числа ячейка находится в режиме редактирования содержимого. Многие команды для работы с ячейками в этом режиме недоступны.
Чтобы выйти из режима редактирования, но остаться в текущей ячейке, щелкните на кнопке Ввод (Enter), расположенной слева от строки формулы.
Примечание
Чтобы переключиться в режим редактирования и изменить содержимое ячейки, дважды щелкните на ней. Щелчок на кнопке Отмена (Cancel), расположенной левее кнопки Ввод, выключает режим редактирования с отменой всех изменений, выполненных с момента последнего входа в этот режим.
Щелчком на кнопке По левому краю (Align Left) панели инструментов Форма тирование выравняйте содержимое объединенной ячейки по левому краю.
Щелкните на ячейке F14. \
В строке формул щелкните на кнопке Изменить формулу (Edit Formula). Откроется окно диалога, помогающее строить корректные формулы.
Рис. 9.6.
Вычисление среднего значения
В раскрывающемся списке Формула (Formula) выберите пункт СРЗНАЧ (AVERAGE).
Щелкните на строке формул и измените величину в скобках на В4: Н12, как показано на рис. 9.6.
Щелкните на кнопке ОК. Теперь в ячейке F14 будет автоматически подсчитываться среднее значение всех чисел прямоугольной области от клетки В4 до клетки Н12. В данной ситуации эта величина равна 7.
Щелкните на ячейке В4 и введите число 500. Нажмите клавишу Enter. Среднее значение таблицы сразу же изменится.
Оформление ячеек
Вы уже умеете вводить числа, заголовки и подписи, вычислять сумму и среднее значение группы ячеек. Однако работать с таблицей намного приятнее, когда ее строки и столбцы окрашены в разные цвета и выделены рамками. Грамотное оформление ячеек помогает быстрее отыскивать числа, которые требуют изменения, и ячейки с результатами вычислений.
Рис. 9.7.
Окно диалога автоформата
Чтобы раскрасить таблицу листа Расходы, выделите прямоугольную область АЗ:Н14 и выберите команду Формат > Автоформат (Format > AutoFormat).
В открывшемся окне диалога (рис. 9.7) щелкните на образце Классический 3 (Classic 3).
Щелкните на кнопке Параметры (Options), открыв этой командой область флажков для установки дополнительных параметров.
Сбросьте флажок ширину и высоту (Width/Height), чтобы инструмент автоформата не изменял размеры ячеек.
Примечание
В формулах Excel прямоугольная область ячеек задается идентификаторами левой верхней и правой нижней ячеек, соединенными двоеточием, например АЗ:Н14. В упражнениях третьей части книги для обозначения группы ячеек (или диапазона) используется аналогичный синтаксис.
Рис. 9.8.
Форматирование ячеек
Затем щелкните на кнопке ОК. Программа Excel сама оформит таблицу выбранным стилем.
Чтобы привести вид заголовков строк в соответствие заголовкам столбцов, выделите ячейки А4:А12 и выберите команду Формат > Ячейки (Format > Cells).
Раскройте вкладку Вид (Patterns), показанную на рис. 9.8.
Щелкните на квадрате темно-синего цвета, сходного с цветом фона заголовков столбцов.
Раскройте вкладку Граница (Border), показанную на рис. 9.9.
Рис. 9.9.
Настройка границ ячеек
В списке Тип линии (Style) выберите линию подходящей толщины.
Щелкните на кнопке Внешние (Outline), чтобы обвести выделенные ячейки рамкой.
Примечание
Чтобы залить выделенные ячейки узором, пользуйтесь раскрывающимся списком Узор (Pattern) вкладки Вид.
Щелчком на кнопке верхней границы уберите линию между ячейками A3 иА4.
Затем щелкните на кнопке ОК.
Цвет ячеек заголовков строк стал темно-синим, и черный текст в них плохо виден. Не отменяя выделения ячеек, щелкните в панели инструментов Фор матирование на стрелке справа от кнопки Цвет шрифта (Font Color).
Выберите в раскрывшейся палитре белый цвет.
Выделите ячейки А12:Н12 и снова выполните команду Формат > Ячейки.
На вкладке Граница выберите тот же тин линии, что и на шаге 10, затем щелк ните на кнопке нижней границы.
Рис. 9.10.
Оформленная таблица
Щелчком на кнопке ОК закройте окно диалога. На листе ниже строки 12 появится разделительная линия. Теперь таблица будет выглядеть так, как показано на рис. 9.10.
Стиль
Конечно, Excel позволяет форматировать не только цвет и рамку ячеек, но также шрифт и расположение их содержимого.
Снова выделите ячейки А4:А12, затем щелкните на них правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells).
В открывшемся окне диалога раскройте вкладку Выравнивание (Alignment), показанную на рис. 9.11.
В раскрывающемся списке по горизонтали (Horizontal) выберите пункт по левому краю (отступ) (Left (Indent)),
С помощью стрелок введите в счетчик отступ (Indent) величину 1, чтобы задать небольшой отступ чисел от левого края ячеек.
Щелкните на кнопке ОК.
В панели инструментов Форматирование щелкните на кнопках Полужирный (Bold) и Курсив (Italic). На занятии 6 обсуждались стили документов Word. Электронные таблицы Excel тоже можно форматировать с помощью стилей.
Рис. 9.11.
Настройка выравнивания текста
Применение стилей позволяет быстро назначать ячейкам определенное заранее заданное оформление. Изменение стиля приводит к моментальному обновлению форматирования всех ячеек, которым назначен этот стиль. Давайте настроим стиль для заголовков и подписей и назначим его двум группам объединенных ячеек листа Расходы.
За основу вновь создаваемого стиля всегда выбирается формат активной ячейки. Щелкните на объединенной ячейке B1:G1, чтобы выбрать формат ее стиля в качестве исходного.
Выполните команду Формат > Стиль (Format > Style).
Введите слово Надпись в поле раскрывающегося списка открывшегося окна диалога. Это будет имя нового стиля. В окне диалога появятся его исходные параметры (то есть режим форматирования диапазона B1:G1).
Щелкните на кнопке Изменить (Modify).
В открывшемся окне диалога Формат ячеек (Format Cells) на вкладке шрифт (Font) выберите полужирный шрифт размером 12 пунктов и щелкните на кнопке ОК.
Рис. 9.12.
Окно настройки стиля
Рис. 9.13.
Форматирование текста
В окне диалога Стиль (Style) сбросьте все флажки, кроме шрифт, как показано на рис. 9.12, чтобы новый стиль влиял только на шрифт ячейки и не изменял другие параметры оформления.
Щелкните на кнопке ОК. Заголовок Расходы компании по месяцам увеличится в соответствии с новым размером шрифта.
Чтобы изменить шрифт подписи Среднее значение таблицы, выделите ячейки B14:F14.
Выполните команду Формат > Стиль, в раскрывающемся списке Имя стиля (Style Name) выберите пункт надпись и щелкните на кнопке ОК.
Примечание
Чтобы создать новый документ на основе шаблона с дополнительными стилями и оформлением, выберите команду Файл > Создать (File > New), раскройте в окне диалога Создание документа (New) вкладку с нужным шаблоном и дважды щелкните на его значке. Любую книгу Excel можно использовать в качестве шаблона, предварительно сохранив ее в файле формата XLT.
Текущий документ был создан без использования шаблона, тем не менее в нем есть несколько стандартных стилей Excel:
Обычный (Normal) — стандартный стиль ячеек;
Процентный (Percent) — стиль ячеек, содержащих процентные величины;
Финансовый (Comma) — денежные значения (рубли и копейки);
Финансовый [0] (Comma [0]) — денежные значения (только рубли);
Денежный (Currency) — денежные значения (рубли, копейки и знак денежной единицы);
Денежный [0] (Currency [0]) — денежные значения (только рубли и знак денежной единицы).
Чтобы задать ячейкам таблицы денежный стиль представления чисел, выделите ячейки В4:Н12.
Нажмите клавишу Ctrl и, не отпуская ее, щелкните на ячейке F14. Эта ячейка будет присоединена к выделению.
Выберите команду Формат > Стиль, в списке Имя стиля выберите пункт Денежный и щелкните на кнопке ОК. Таблица станет такой, как показано на рис. 9.13.
Формат чисел
Так как программа Excel предназначена для обработки чисел, важную роль играет правильная настройка их формата. Для человека число 10 — это просто единица и ноль. С точки зрения Excel эти две цифры могут нести совершенно разную информацию в зависимости от того, обозначают ли они количество работников компании, денежную величину, процентную часть целого или фрагмент заголовка «10 ведущих фирм». Во всех четырех ситуациях это число должно отображаться и обрабатываться по-разному. Excel поддерживает следующие форматы данных:
Общий (General) — текст и числовые значения произвольного типа;
Числовой (Number) — наиболее общий способ представления чисел;
Денежный (Currency) — денежные величины;
Финансовый (Accounting) — денежные величины с выравниванием по разделителю целой и дробной частей;
Дата (Date) — дата или дата и время;
Время (Time) — время или дата и время;
Процентный (Percentage) — значение ячейки, умноженное на 100 с символом «%» в конце;
Дробный (Fraction) — рациональные дроби с числителем и знаменателем;
Экспоненциальный (Scientific) —десятичные дробные числа;
Текстовый (Text) — текстовые данные отображаются точно так же, как вводятся и обрабатываются строки, вне зависимости от их содержимого;
Дополнительный (Special) —форматы для работы с базами данных и списками адресов;
Заказной (Custom) — формат, настраиваемый пользователем.
Наиболее распространенные варианты формата данных можно назначать с помощью панели инструментов Форматирование.
Щелкните на ячейке С4, а затем на кнопке Процентный формат (Percent Style). Величина клетки С4 будет умножена на 100, и к ней добавится знак «%».
Рис. 9.14.
Вкладка выбора формата данных
Нажмите клавишу вниз и щелкните на кнопке Денежный формат (Currency).
Щелкните на ячейке Сб, а затем на кнопке Формат с разделителями (Comma Style). Эта кнопка заставляет числа выравниваться в столбце по разделителю целой и дробной частей.
Выделите ячейку С7 и щелкните на кнопке Увеличить разрядность (Increase Decimal). Эта кнопка не изменяет основной формат, но добавляет один знак в дробной части числа.
Нажмите клавишу Enter и щелкните на кнопке Уменьшить разрядность (Decrease Decimal). Эта операция убирает один знак дробной части и округляет число. Теперь ячейки с С4 по С9 выглядят совершенно по-разному, хотя исходно в них были введены совершенно одинаковые числа. Другие форматы назначаются с помощью следующих действий.
Щелкните на ячейке С10 и выберите команду Формат > Ячейки.
В открывшемся окне диалога раскройте вкладку Число (Number) (рис. 9.14).
В списке Числовые форматы (Category) щелкните на пункте Дата (Date).
В появившемся списке Тип (Type) щелкните на строке 14 мар 01 (14-Mar-Ol). Затем щелкните на кнопке ОК.
Аналогичным образом назначьте ячейке С11 формат Экспоненциальный, а ячейке С12 — формат Числовой. Теперь таблица будет выглядеть так (рис. 9.15). Обратите внимание, что среднее значение таблицы не изменилось, то есть при смене формата изменяется только способ отображения, а сами числовые значения остаются неизменными. Для проверки этого факта выполните следующие шаги.
Дважды щелкните на ячейке СЮ и измените величину 03.01.1900 на 03.02.1900.
Нажмите клавишу Enter. Среднее значение таблицы (которое выводится в денежном формате) моментально изменится на 15.41р. Как войдите, можно суммировать даты с процентами и в результате получать рубли. Это типичный пример неверного назначения форматов данных.
Рис. 9.15.
Различные форматы чисел
Добавление строк и столбцов
Редко удается сразу построить таблицу с нужной структурой. В процессе разработки листа часто приходится добавлять и переставлять столбцы и строки. Давайте поучимся этим операциям.
Выделите ячейки ЕЗ и F3 и выберите команду Вставка >Столбцы (Insert > Columns). Слева от столбца с заголовком Апрель появятся два пустых столбца (рис. 9.16). Их количество соответствует числу ячеек выделенного блока по горизонтали.
Выделите диапазон ВЗ:С12 и нажмите клавиши Ctrl+C, чтобы скопировать содержимое выделенных ячеек.
Щелкните на клетке ЕЗ правой кнопкой мыши и выберите в контекстном меню команду Вставить (Paste). Эта операция вставит данные не только в ячейку ЕЗ, но и в другие ячейки новых столбцов. В правом нижнем углу вставленного фрагмента появился значок вставки. Поместив на него указатель мыши, вы увидите раскрывающийся список, позволяющий выбрать один из параметров вставки: Сохранить форматы оригинала (Keep Source Formatting), Использовать форматы конечных ячеек (Match Destination Formatting), Значения и форматы чисел (Values and Number Formatting), Сохранить ширину столбцов оригинала (Keep Source Column Width), Только форматы (Formatting Only) или Ссылки на ячейки (Link Cells).
Примечание
При вставке блока данных в одну ячейку программа Ехсе1.всегда занимает вставляемой информацией столько ячеек, сколько было вырезано или скопировано. Если перед выполнением вставки было выделено несколько ячеек, то данные будут вставлены только в том случае, если форма диапазона-приемника идентична форме диапазона-источника.
Рис. 9.16.
Добавление столбцов
Щелкните на ячейке Н14, в которую переместилась ячейка с формулой среднего значения таблицы. В строке формул вы увидите текст =CP3HA4(B4:J12) (=AVERAGE(B4:312)). To есть область вычисления среднего значения автоматически расширилась на два вставленных столбца. Таким образом, формулы, работающие с массивами данных, автоматически адаптируются при увеличении размера массива.
Дополнительные строки добавляются точно так же, как столбцы. Вставляя строки или столбцы в таблицу с данными, будьте внимательны. Удалить лишние ячейки гораздо труднее, чем вставить. Выделение ячеек с последующим нажатием клавиши Delete, конечно, очищает ячейки, но размеры таблицы при этом не изменяются.
Размеры ячеек и их замораживание
Чтобы таблица лучше вписывалась в экран компьютера или на страницу принтера, можно подрегулировать ширину и высоту ячеек.
Поместите указатель на границу между заголовками столбцов А и В, чтобы его значок принял форму двунаправленной стрелки.
Нажмите кнопку мыши и перетащите границу столбцов влево, уменьшив этим ширину первого столбца.
Выделите строки 13 и 14.
Выберите команду Формат > Строка > Высота (Format > Row > Height).
В открывшемся окне диалога введите число 15 и щелкните на кнопке ОК. Выделенные строки станут выше.
Чтобы максимально сузить таблицу, протащите мышь по кнопкам строк с 1 по 14, выделив этим все ячейки данных.
Выберите команду Формат > Столбец > Автоподбор ширины (Format > Column > AutoFit Selection). Эта операция максимально сузит столбцы, но лишь до такого размера, чтобы все данные свободно помещались в ячейках таблицы.
Рис. 9.17.
Закрепление ячеек
Иногда при редактировании таблицы приходится копировать или переносить данные между ячейками, далеко отстоящими друг от друга. В этом случае можно закрепить часть строк и столбцов на экране. Работая с большими таблицами, бывает удобно закрепить строку и столбец заголовков, чтобы не запутаться в ячейках. Для закрепления заголовков выполните следующие шаги.
Щелкните на ячейке В4. Затем выберите команду Окно > Закрепить области (Window > Freeze Panes). Ячейки, расположенные выше или левее выделенной, окажутся закрепленными и отгороженными от остальной таблицы тонкими черными линиями.
Щелкните три раза на кнопке прокрутки вниз.
Теперь два раза щелкните на кнопке прокрутки вправо. Лист Excel прокручивается как обычно, но закрепленные ячейки остаются на экране, как показано на рис. 9.17. Создается впечатление, что столбцы В и С, а также строки 4, 5 и 6 временно исчезли с экрана.
Чтобы отменить закрепление, выполните команду Окно > Снять закрепление областей (Window > Unfreeze Panes). Лист будет выглядеть, как прежде.
Формулы
Формулы Excel чрезвычайно многогранны и заслуживают того, чтобы написать о них отдельную книгу. Чтобы лучше изучить формулы, пользуйтесь справочной системой Excel, дающей исчерпывающую информацию обо всех функциях и правилах построения формул.
xls раскройте лист Календарь, созданный
В файле электронная таблица . xls раскройте лист Календарь, созданный в контрольном упражнении предыдущего занятия
Щелкните в ячейке G3, введите формулу, вычисляющую среднее значение чисел строки 3 календаря, и нажмите клавишу Enter.
В ячейку НЗ выведите целую часть этого среднего значения.
С помощью какой функции выделяется целая часть числа ?
Продублируйте две полученные формулы во всех строках календаря.
Как продублировать формулы двух ячеек в нескольких строках?
В ячейке Gil сосчитайте сумму средних значений, а в ячейке Н11 с помощью функции ПРОИЗВЕД произведение целых частей средних значений.
В ячейке F11 с помощью функции СУМ М подсчитайте сумму всех ячеек третьей и четвертой строк листа Excel.
Как задать в качестве аргумента функции две строки листа ?
Скопируйте содержимое ячейки F11.
Щелкните на ячейке F13 и вставьте в нее формулу из буфера обмена.
Сумму каких ячеек подсчитывает вставленная формула?
Подведение итогов
С помощью упражнений этого занятия вы научились вводить формулы, пользоваться функциями, копировать формулы, находить и исправлять ошибки. Вы узнали о механизме относительной адресации ячеек и изучили некоторые функции, работающие с текстовыми строками и величинами типа даты и времени.
Чтобы наглядно представить результаты вычислений, их нужно оформить в виде диаграммы или графика. Именно этому способу отображения данных Excel и посвящено следующее занятие.
Ввод формул
Самым простым способом задания формулы является ее непосредственный ввод в строку формул. У этого варианта есть лишь один недостаток — вы должны помнить правила построения формул Excel, названия встроенных функций и методы ссылок на ячейки листа.
Откройте
фaйл
Электронная таблица.xls, созданный на предыдущем занятии. В этой рабочей книге есть лист Формулы, измененный в контрольном упражнении предыдущего занятия. Раскройте его. Первые семь строк листа должны выглядеть так, как показано па рис. 10.1, остальные ячейки будут пусты. Если это не соответствует действительности, отредактируйте лист.
Щелкните на ячейке В10 и введите формулу =(СЗ-ВЗ)/ВЗ*100.
Знак равенства указывает программе Excel, что ячейка содержит формулу. СЗ и ВЗ — это ссылки на содержимое соответствующих ячеек. Если предположить, что таблица содержит объем продаж продукции различным клиентам, то (СЗ-ВЗ) — это прирост продаж для фирмы Фантом с января по февраль. Разделив полученную величину на ВЗ (продажи за январь), получаем относительный прирост продаж за месяц. Результат умножается на 100, что дает прирост в процентах. Как видите, в ячейки можно вводить простые, арифметические выражения со знаками +, -, *, / и скобками, определяющими последовательность выполнения операций.
В упражнении 2 предыдущего занятия вы изучили прием автозаполнения ячеек последовательными названиями месяцев и числами. Давайте воспользуемся тем же приемом для добавления однотипных формул.
Рис. 10.1.
Ввод формулы
Щелкните на ячейке В10 и перетащите угловой маркер выделения вправо, чтобы оказались охваченными ячейки с В10 по F10.
Примечание
Чтобы результат выводился в процентах, не обязательно умножать его на 100, достаточно сменить формат данных, выделив ячейку и щелкнув на кнопке Процентный формат панели инструментов Форматирование.
На первый взгляд кажется, что в ячейки C10:F10 должна дублироваться формула ячейки В 10, но это не так. Excel поддерживает механизм относительных ссылок на ячейки.
Когда формула переносится на одну клетку вправо, точно так же смещаются все ссылки этой формулы (это верно и для смещения формулы в любых других направлениях на любое число клеток). То есть в ячейке С11 появится формула =(D3-C3)/C3*100, в ячейке D10 - формула =(E3-D3)/D3*100 и т. д. Чтобы проверить это, щелкните на каждой ячейке и изучите содержимое строки фор-•мул. В итоге ячейки строки 10 будут содержать относительный прирост продаж компании Фантом по месяцам.
Выделите строки с третьей по седьмую.
Щелчком на кнопке Вырезать (Cut) панели инструментов Стандартная (Standard) вырежьте их содержимое.
Щелкните на ячейке А2 правой кнопкой мыши и выберите в контекстном меню команду Вставить. Данные таблицы переместятся вверх на одну строку. Теперь снова изучите формулы ячеек строки 10.
При смещении ячеек с данными с помощью операций вырезания и вставки ссылки на эти ячейки в имеющихся формулах модифицируются так, что в обновленной таблице в расчетах принимают участие те ячейки, в которые были перемещены исходные данные.
Примечание
Если необходимо, чтобы знак равенства в начале текста ячейки не воспринимался как индикатор формулы, а интерпретировался как символ, поставьте перед ним одинарную кавычку (').
функции
Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С тремя из них — СУММ (SUM), СРЗНАЧ (AVERAGE) и СЧЕТ (COUNT) — вы познакомились на предыдущем занятии. Чтобы научиться работать с другими функциями, выполните следующие шаги.
Щелкните на ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.
Щелкните на кнопке Вставка функции (Paste Function) панели инструментов Стандартная. Откроется окно диалога, показанное на рис. 10.2. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке категорию: (Select a Category) этого окна диалога, а затем пролистать список Выберите функцию (Select a Function Name). Выделив имя одной из функций, вы увидите ее описание в нижней части окна диалога. Excel делит все функции на следующие категории:
Финансовые (Financial) — функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;
Дата и время (Date & Time) — операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл. 10.1;
Рис. 10.2.
Вставка функции
ТАБЛИЦА 10.1.
Функции обработки даты и времени
|
Функция |
Описание | ||
|
ДАТА(DATE) |
Возвращает дату в формате Excel | ||
|
ДАТАЗНАЧ(DATEVALUE) |
Преобразует дату из текстового формата в код Excel | ||
|
ДЕНЬ(DAY) |
День месяца заданной даты | ||
|
ДНЕЙ360 (DAYS360) |
Вычисляет количество дней между двумя датами на основе З60-дневного года | ||
|
ЧАС (HOUR) |
Час времени, заданного аргументом | ||
|
.МИНУТЫ (MINUTE) |
Минута времени, заданного аргументом | ||
|
МЕСЯЦ (MONTH) |
Номер месяца заданной даты | ||
|
J-ДАТА (NOW) |
Текущая дата и время в числовом формате | ||
|
СЕКУНДЫ (SECOND) |
Секунда времени, заданного аргументом | ||
|
ВРЕМЯ (TIME) |
Преобразует время дня в дробное число от 0 до 1 | ||
|
ВРЕМЗНАЧ (TIMEVALUE) |
Преобразует время из текстового формата в дату в числовом формате | ||
|
ДЕНЬ (TODAY) |
Текущая дата в числовом формате | ||
|
ДЕНЬНЕД (WEEKDAY) |
Номер дня недели заданной даты | ||
|
ГОД(YEAR) |
Год заданной даты |
Математические (Math & Trig) — математические и тригонометрические функции, некоторые из них приведены в табл. 10.2;
ТАБЛИЦА 10.2.
Математические и тригонометрические функции
|
Функция |
Описание |
|
COS, SIN, TAN, |
Тригонометрические функции |
|
ACOS, ASIN, ATAN, ATAN2 |
Обратные тригонометрические функции |
|
COSH, SINH, TANH |
Гиперболические функции |
|
ACOSH, ASINH, ATANH |
Обратные гиперболические функции |
|
LN, LOG, LOG10 |
Натуральный логарифм, логарифмы по основанию 2 и 10 |
|
EXP |
Экспонента |
|
НЕЧЕТ (EVEN), OKPyiTI(ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), 4ETH(ODD) |
Функции округления |
|
ABS |
Модуль (абсолютное значение)'числа |
|
ГРАДУСЫ (DEGREES), |
Преобразование радиан в градусы |
|
РАДИАНЫ (RADIANS) |
Преобразование градусов в радианы |
|
ЦЕЛОЕ (INT) |
Целая часть числа |
|
ОСТАТ (MOD) |
Остаток от деления |
|
ПИ(Р1) |
Число пи |
|
СТЕПЕНЬ (POWER) |
Возведение в степень |
|
ПРОИЗВЕЛ (PRODUCT) |
Произведение ряда чисел |
|
СЛЧИС (RAND) |
Возвращает случайное число |
|
РИМСКОЕ (ROMAN) |
Преобразование арабского числа в римское |
|
КОРЕНЬ (SQRT) |
Квадратный корень |
|
СУММ (SUM) |
Сумма ряда чисел |
|
СУММ КВ (SUMSQ) |
Сумма квадратов ряда чисел |
|
ОТБР (TRUNC) |
Отбрасывает дробную часть |
ТАБЛИЦА 10.3.
Статистические функции
|
Функция |
Описание |
|
СРОТКЛ(AVEDEV) |
Среднее абсолютное значение отклонения от среднего |
|
СРЗНАЧ (AVERAGE), СРЗНАЧА(AVERAGEA) |
Среднее арифметическое аргументов |
|
СЧЕТ (COUNT), СЧЕТЗ (COUNTA), СЧИТАТЬ ПУСТОТЫ (COUNT-BLANK), СЧЕТЕСЛИ (COUNTIF) |
Количество чисел в списке аргументов |
|
КБЛДРОТКЛ (DEVSQ.) |
Сумма квадратов отклонении от среднего но выборке |
|
CFFEOM (GEOMEAN) |
Среднее геометрическое набора положительных чисел |
|
МАКС (МАХ), МАКСА (МАХА) |
Максимальное значение списка аргументов |
|
МИН (M1N), МИНА (MINA) |
Минимальное значение списка аргументов |
|
ДИСП (STDEV), ДИСПА (STDEVA), |
Функции расчета дисперсии |
|
ДИСПР (STDEVP), ДИСПРА (STDEVPA) СТАНДОТ-КЛОН (VAR), СТАНДОТКЛОНА (VARA), СТАНДОТКЛОНП (VARP), СТАНДОТКЛОНПА (VARPA) |
Стандартное отклонение по выборке |
Статистические (Statistical) — функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных
характеристик. Некоторые статистические функции перечислены в табл. 10.3;
Ссылки и массивы (Lookup & Reference) — операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;
Работа с базой данных (Database) — функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;
Текстовые (Text) — функции для работы с текстовыми строками. Часть этих функций перечислена в табл.-10.4.
ТАБЛИЦА 10.4.
Текстовые функции
Функция |
Описание |
СИМВОЛ (CHAR) |
Возвращает символ с заданным кодом |
ПЕЧСЙМВ (CLEAN) |
Удаляет из строки все непечатаемые символы |
КОДСИМВ (CODE) |
Код первого символа строки |
СЦЕПИТЬ(CONCATENATE) |
Объединяет две текстовые строки |
СОВПАД (EXACT) |
Проверяет идентичность двух строк |
ЛЕВСИМВ (LEFT) |
Возвращает несколько левых символов строки |
ДЛСТР (LEN) |
Количество символов в строке |
СТРОЧН (LOWER) |
Делает все буквы текста строчными |
ПРОПНАЧ(PROPER) |
Делает первую букву прописной, а остальные — строчными |
ПОВТОР(REPT) |
Повторяет текст заданное число раз |
ПРАВСИМВ (RIGHT) |
Возвращает несколько Правых символов и роки |
Т, ТЕКСТ(TEXT), ФИКСИРОВАННЫЙ (FIXED) |
Преобразует число в текст |
СЖПРОБЕЛЫ (TRIM) |
Удаляет лишние (двойные) пробелы |
ПРОПИСН (UPPER) |
Делает все буквы прописными |
ЗНАЧЕН (VALUE) |
Преобразует текстовый аргумент в число |
Логические (Logical) — шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение Истина или Ложь. Эти функции перечислены в табл. 10.5;
Проверка свойств и значений (Information) — функции проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий.
ТАБЛИЦА 10.5.
Логические функции
|
Функция |
Описание |
|
И (AND) |
Логическое умножение |
|
ЛОЖЬ (FALSE) |
Возвращает ложное значение |
|
ЕСЛИ (IF) |
Возвращает одно значение, если условие истинно, и другое, если условие ложно |
|
НЕ (NOT) |
Логическое отрицание |
|
ИЛИ (OR) |
Логическое сложение |
|
ИСТИНА (TRUE) |
Возвращает истинное значение |
В списке Категория (Function Category) есть также пункты Рекомендуемый перечень (Recommended), Полный алфавитный перечень (All) и 10 недавно использовавшихся (Most Recently Used), которые выводят соответственно список всех функций и 10 функций, применявшихся последними.
Выберите в списке Категория окна диалога вставки функций (см. рис. 10.2) пункт Статистические.
Выберите в списке Функция (Function Name) пункт МАКС.
Щелкните на кнопке ОК. Откроется окно ввода диапазона ячеек, показанное на рис. 10.3.
Рис. 10.3.
Окно ввода аргументов
Некоторые функции, например SIN, имеют только один аргумент, поскольку нельзя вычислить синус сразу двух чисел. Однако многие функции, подобные МАКС, способны обрабатывать практически неограниченные массивы данных. Такие функции могут воспринимать до 30 аргументов, каждый из которых является числом или ссылкой на одну или несколько ячеек.
Примечание
В качестве аргументов могут выступать также арифметические выражения и другие функции, возвращающие значения нужного типа.
С помощью функции МАКС вы сейчас найдете максимальный уровень продаж за один месяц для компаний Фантом, РИФ и Викинг.
Рис. 10.4.
Выбор ячеек для первого аргумента
Щелкните на кнопке в правой части поля Число 1 (Number 1). Окно диалога свернется в строку, открывая доступ к ячейкам листа.
Выделите все числовые ячейки строки Фантом. Обозначение соответствующего диапазона ячеек появится в строке свернутого окна диалога ввода аргументов (рис. 10.4).
Щелкните на кнопке строки аргумента. На экране снова развернется окно ввода аргументов.
Щелкните на кнопке в правой части поля Число 2 (Number 2).
Выделите ячейки B4:G4 и снова щелкните на кнопке строки ввода аргумента. Поскольку вы ввели уже два аргумента, Excel автоматически добавит поле ввода третьего.
Введите в поле Ч и ел о 3 (Number 3), как показано на рис. 10.5, текст B6:G6. Это диапазон нужных ячеек строки Викинг. В окне диалога правее полей с аргументами демонстрируются их реальные значения. Ниже списка чисел отображается результат вычислений.
Рис. 10.5.
Три диапазона ячеек в качестве аргументов функции МАХ
Щелкните на кнопке ОК. В ячейке 18 появится максимальное число из диапазона, записанного в строках 2,4 и 6. Сама формула появится в строке формул
в верхней части окна Excel. Щелкните в этой строке. Три аргумента функции МАКС, задающие три диапазона ячеек, будут выделены разными цветами, а соответствующие группы ячеек листа Excel окажутся обведенными рамками соответствующих цветов, как показано на рис. 10.6.
Рис. 10.6.
Группы ячеек, выступающие в качестве аргументов формулы
Примечание
Если вы хорошо знакомы с синтаксисом функций и формул Excel, то можете просто вводить формулы в ячейки, как это делалось на предыдущем занятии, не пользуясь окнами диалога выбора функции и ввода аргументов.
Диапазон ячеек
Для ссылки на данные ячеек листа в Excel используются имена клеток, состоящие из буквы столбца (или двух букв, если столбцов больше 26) и номера строки. Так как многие функции могут воспринимать в качестве аргументов целые массивы данных, нужно выучить правила ссылок на такие массивы.
Щелкните на ячейке Н9 и введите текст Сумма. Затем нажмите клавишу Tab.
Введите символы =СУММ () (=SUM ()). Эта формула вычисляет сумму всех величин, указанных в скобках в качестве аргумента.
Щелкните в строке формул и поместите курсор между двумя скобками.
Введите в скобки текст В: В.
Щелкните на кнопке Ввод слева от строки формул. В ячейке 19 появится сумма всех ячеек столбца В, так как символы В:В обозначают все ячейки столбца В.
Снова щелкните в строке формул. В таблице окажется выделенной та группа ячеек, которые описываются аргументом В: В, как показано на рис. 10.7.
Повторите шаги с 3-го по 6-й, поочередно вводя в скобки формул значения из первого столбца табл. 10.6. Изучите, какие диапазоны ячеек соответствуют указанным условным обозначениям. Немного потренировавшись, вы сможете указывать в качестве аргумента 4>ункции любые группы ячеек листа. , Если в разных формулах часто приходится ссылаться на одну и ту же группу ячеек, особенно если в группу входят разрозненные ячейки из разных областей листа, ей удобно присвоить специальное имя.
Протаскиванием мыши выделите ячейки B3:G3.
Нажмите клавишу Ctrl и протащите указатель мыши по ячейкам B5:G5.
Рис. 10.7.
Столбец В в качестве аргумента функции
ТАБЛИЦА 10.6
.Способы ссылки на группы ячеек
|
Обозначение |
Группа ячеек | ||
| F3 | Ячейка на пересечении столбца F
и строки-3 | ||
| Е10:Е20 | Ячейки с 10-й по 20-ю в столбце Е | ||
| В15:Е15 | Ячейки с В по Е в строке 15 | ||
| 5:5 | Все ячейки строки 5 | ||
| 5:10 | Все ячейки строк с 5-й по 10-ю | ||
| В:В | Все ячейки столбца В | ||
| B:J | Все ячейки столбцов c B no J | ||
| А10:Е20 | Прямоугольная область пересечения строк с 10-й по 20-ю и столбцов с А по Е |
Введите в поле Имя ( Name Box) слово Строки, как показано на рис. 10.8.
Нажмите клавишу Enter.
Щелкните на ячейке 19, введите формулу =СУММ(Строки) (=SUМ(Строки)) и нажмите клавишу Enter. В ячейке 19 появится сумма двенадцати ячеек строк Зи5.
Щелкните на стрелке раскрывающегося списка Имя (Name Box).
Рис. 10.8.
Именованная группа ячеек
Выберите пункт Строки. Окажутся выделенными ячейки диапазона Строки. Таблица может содержать несколько именованных диапазонов ячеек. Такие именованные диапазоны значительно упрощают формулы, делая их более наглядными.
Копирование формул
Иногда требуется провести одинаковые расчеты с разными группами ячеек. В такой ситуации можно сэкономить время и скопировать формулы, так как Excel поддерживает относительную адресацию ячеек. В строке 10 нашего примера уже есть формулы, рассчитывающие относительный прирост продаж по месяцам для клиента Фантом. Давайте скопируем их, чтобы получить таблицу прироста продаж для всех клиентов.
Щелкните на любой непустой ячейке в строке 10. Вы увидите, что выбранная формула ссылается на ячейки строки 2.
Выделите диапазон B10:F10.
Нажатием клавиш Ctrl+C скопируйте ячейки.
Щелкните на ячейке В11, а затем на кнопке Вставить панели инструментов Стандартная. Появится еще одна строка ячеек с формулами. Щелкните на любой из них и взгляните в строку формул. Вы увидите, что формулы новых ячеек ссылаются на данные строки 3, как показано на рис. 10.9.
Щелкните на ячейке В 12 и нажмите клавиши Ctrl+V, чтобы вставить еще одну строку. Формулы этой строки уже будут ссылаться на данные строки 4. Таким образом, номера ячеек данных изменяются ровно на столько клеток, на сколько смещается вставляемая формула.
Рис. 10.9.
Копирование формул
Примечание
Обратите внимание, что вырезание ячеек с формулами с последующей их вставкой в другое место листа никак не изменяет ссылки на данные.
Вставьте тот же самый фрагмент в ячейки В13 и В14.
Скопируйте заголовки строк из ячеек А2:А6 в ячейки А10:А14.
Примечание
Обычно Excel формирует относительные ссылки на ячейки. Но иногда нужно сослаться в формуле на клетку с константой, которая не должна меняться при копировании формулы в другую ячейку или при перемещении исходных данных. В этом случае пользуйтесь абсолютными ссылками, которые отличаются от относительных наличием символа $ перед буквой столбца, номером строки или перед обеими этими характеристиками, например $Е$15. В такой ссылке не изменяется та часть, перед которой стоит знак $. Например, копируя формулу =СУММ($В$2:$0$6) (=SUM($B$2:$G$6)) в другую ячейку, вы получите тот же самый результат, что и в исходной клетке формулы.
Операция суммирования строк или столбцов — одна из наиболее распространенных. Чтобы создать формулы сумм строк или столбцов, не нужно даже копировать ячейки, Excel предлагает еще более быстрый способ.
Выделите группу ячеек 12:16.
Щелкните на кнопке Автосумма (AutoSum) панели инструментов Стандартная.
Осталось только ввести в ячейку II заголовок Итого, и столбец общей суммы продаж для всех пяти клиентов готов (рис. 10.10).
Примечание
Обратите внимание на значок вставки, появляющийся в правом нижнем углу вставленного фрагмента. Благодаря ему легко выбрать вариант вставки — хотите ли вы вставить число из копируемой ячейки или абсолютную либо относительную ссылки, нужно ли сохранять формат исходной ячейки и т. д.
Поиск ошибок
По мере изучения формул и функций Excel вы, конечно же, будете делать ошибки. Это может быть неверно заданный диапазон ячеек, неправильное имя функции или просто пропущенная скобка. Excel зафиксирует ошибку и в случае неверного типа аргумента, если, к примеру, в ячейке вместо ожидаемой числовой величины оказалась текстовая.
Рис.
10.10.
Автосумма
ЩелкнитенаячейкеШивведитеформулу СРЗНАЧ B11:F11 (AVERAGE B11:F11).
Нажмите клавишу Enter. В ячейке появится сообщение #ИМЯ (#NAME), которое
\
указывает на ошибку в имени функции. Слева от него находится маленький значок помощника, содержащий раскрывающийся список, в котором приводятся возможные способы устранения ошибки. . В табл. 10.7 приведен список сообщений об ошибках, которые можно встретить в ячейках при работе с формулами.
Чтобы исправить формулу, двойным щелчком на ячейке 111 перейдите в режим редактирования формулы.
Примечание
Инструмент автокоррекции формул сам исправляет наиболее очевидные ошибки. Например, если в рассматриваемой формуле вы забудете ввести только закрывающую скобку, программа сама добавит ее.
ТАБЛИЦА 10.7.
Сообщения об ошибках в формулах
|
Сообщение |
Описание ошибки | ||
| ##### | Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислении в ячейке, отформатированной как данные типа даты или времени | ||
| #ЗНАЧ (#VALUE!) | Неверный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число | ||
| #ДЕЛ/0(#01У/0!) | Деление на 0 | ||
| #ИМЯ (#NAME!) | Excel не может распознать текст, введенный в формулу, например неверное имя функции | ||
| #Н/Д (#N/A) | Данные ячейки одного из аргументов формулы в данный момент недоступны | ||
| *#ССЫЛКА (#REF!) | Неверная ссылка на ячейку | ||
| #ЧИСЛО(#МиМ!) | Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейке | ||
| #ПУСТО (#NULL!) | Результат поиска пересечения двух непересекающихся областей, то есть неверная ссылка |
Измените содержимое ячейки так, чтобы получилась формула СРЗНАЧ (В 11:F11) (AVERAGE(B11:F11)).
Применение функций
До сих пор вы пользовались только функциями СУММ, СРЗНАЧ, СЧЕТ и МАКС. Давайте рассмотрим на примере некоторые функции из разряда текстовых и логических, а также функции работы с датой и временем. Результат всех вычислений, которые будут выполнены в этом упражнении.
В листе Формулы выделите и скопируйте ячейки Bl:Gl.
Разверните лист ЛистЗ (Sheet3).
Щелкните правой кнопкой мыши на ячейке А1 и выберите в контекстном меню команду Вставить.
Введите в ячейку A3 формулу =ЛЕВСИМВ(А1 ;3) (=LEFT(A1; 3)). Эта формула возвращает три левых символа ячейки А1.
Перетащите угловой маркер выделения вправо, чтобы рамка охватила ячейки A3:F3. Теперь в третью строку выводятся сокращенные варианты названий месяцев из ячеек первой строки.
Щелкните наячейкеНЗ и введите формулу =СЦЕПИТЬ(ВЗ ;D3) (CONCATENATE (ВЗ ; D3)). В ячейке НЗ появится объединение строк Фев и Апр.
В ячейку А8 введите формулу =ТДАТА () (=NOW ()) и нажмите клавишу Enter. В ней тут же появятся текущие дата и время.
Введите в ячейки с В8 по В13 формулы = ГОД (А8) (=YEAR(A8)), =МЕСЯЦ(А8) (=MONTH (А8)),=ДЕНЬ(А8)(=DAY(А8)),=ЧАС(А8)(=HOUR(A8)),=МИНУТЫ(А8) (=MINUTE(A8)) и =СЕКУНДЫ(А8) (=SECOND (A8)). В этих ячейках появятся по отдельности все шесть компонентов текущих даты и времени.
Дважды щелкните на ячейке А8, чтобы перевести ее в режим редактирования.
Щелкните на кнопке Ввод, расположенной слева от строки формул. Эта операция приведет к обновлению значения в ячейке А8, что повлияет и на числа ячеек В8:В13.
Теперь давайте выполним логическую операцию.
ВведитевячейкуА4формулу=ЕСЛИ(АЗ="Фев"; "Да"; "Нет") (=IF(АЗ="Фев"; " Да "; " Н е т ")). Эта операция сравнивает значение ячейки A3 с текстовой строкой Фев. В случае равенства выводится текст второго аргумента — Да. В случае неравенства выводится текст третьего аргумента — Нет. Так как в ячейке A3 присутствует текст Янв, то результатом этой операции будет значение Нет.
Растяните маркер ячейки А4 вправо, чтобы рамка охватила диапазон A4:F4. Теперь формулы строки 4 проверяют ячейки строки 3 на совпадение их содержимого со строкой Фев. Как видите, слово Да появилось только в столбце В.
Знаки, которые используются в Excel для сравнения величин, перечислены в табл. 10.8. Обратите внимание, что текстовые строки, выступающие в качестве аргументов функций, должны заключаться в двойные кавычки.
Примечание
Текстовые строки равны, если попарно совпадают все их символы, в противном случае строки не равны. Одна текстовая строка больше другой, если в ней код первого символа, несовпадающего в обеих строках, больше кода того же символа во второй строке.
ТАБЛИЦА 10.8.
Знаки сравнения
|
Знак |
Значение |
|
= |
Равно |
|
> |
Больше |
|
< |
Меньше |
|
>= |
Больше или равно |
|
<= |
Меньше или равно |
|
<> |
Не равно |
Вычисления в Excel
Все вычисления в Excel выполняются с помощью формул, которые можно вводить в любые ячейки листа. Если содержимое ячейки начинается со знака равенства (=), Excel полагает, что вслед за этим знаком идет формула, и пытается выполнить указанную операцию. Когда это удается, в ячейку с такой формулой выводится результат расчета. Если что-то не так, появляется сообщение об ошибке. Сама формула отображается в ячейке только в том случае, когда ячейка находится в режиме редактирования текста (то есть после двойного щелчка на ней). Если ячейка просто выделена, то ее формула выводится в строке формулы в верхней части окна Excel.
и функций можно выполнять математическую
Формулы и функции
ТЕМА ЗАНЯТИЯ
С помощью формул и функций можно выполнять математическую и статистическую обработку данных листа Excel. На этом занятии вы научитесь:
вводить формулы;
пользоваться стандартными функциями;
ссылаться на диапазон ячеек;
выполнять сложные вычисления;
копировать формулы;
исправлять ошибки в формулах.
Вам, наверное, не раз приходилось подсчитывать сумму или среднее значение столбцов или строк таблиц. Excel может выполнять с данными ячеек листа те же вычисления, которые выполняются с помощью калькулятора. Формулы и функции Ехсе1 способны обрабатывать величины как конкретных ячеек таблицы, так и целых блоков (например, строк или столбцов). Формулы Excel могут быть очень сложны, а результат их вычислений, в свою очередь, можно использовать в других расчетах. Неоспоримым преимуществом электронной таблицы является то, что при изменении данных листа результаты вычислений моментально обновляются.
