Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 6
А.М. Кононов. Руководство пользователя для студентов СИТ.
Электронные таблицы Excel.
Компьютерные программы, предназначенные для хранения и обработки данных, представленных в табличном виде, называют электронными таблицами.
ЭТ позволяют объединить удобство и упорядоченность представления информации в табличном виде с вычислительными возможностями компьютера.
ЭТ можно использовать для следующих целей:
Пуск – Программы - Microsoft Excel (или Пуск – Все программы - Microsoft Excel)
При частом использовании программы целесообразно создать ярлык либо на Рабочем столе, либо в Панели быстрого запуска.
Выйти из программы можно:
Удерживая клавишу Alt , нажать клавишу F4 . При появлении окна с вопросом, "Сохранить изменения, сделанные в ...", щелкнуть на кнопке "Нет". Копка "Отмена" позволяет отменить выход из программы. Кнопка "Сохранить" используется в том случае, если необходимо сохранить (записать) документ на диск;
Щелкнуть на пункте "Файл" в меню программы Excel, в открывшемся подменю щелкнуть на строке "Выход";
Щелкнуть на кнопке закрытия программы X в строке заголовков.
После запуска программы на экране появляется окно программы, в котором отображается чистый лист рабочей книги Excel.
Каждый файл в терминологии программы называется рабочей книгой.
Файлы Excel имеют расширение .XLS.
Книга может содержать один или несколько табличных листов. По умолчанию каждая новая рабочая книга содержит три чистых листа, но всегда можно добавить новые и удалить ненужные.
Окно программы включает следующие основные элементы:
Рабочая область программы заполнена ячейками таблицы, образованных пересечением вертикальных столбцов и горизонтальных строк.
Столбцы обозначают латинскими буквами A, B, C... Если букв не хватает, используют двухбуквенные обозначения AA, AB и далее.
Максимальное число столбцов в таблице - 256.
Строки нумеруются целыми числами.
Максимальное число строк в таблицах Excel - 65 536.
Номер ячейки формируется как объединение номеров столбца и строки без пробелов между ними. Например: A1, CZ31, HP14000. Программа Excel вводит номера ячеек автоматически.
Одна из ячеек на рабочем листе всегда является текущей (активной).
Текущая ячейка обведена черной рамкой, а ее адрес отражается в поле Имя, расположенном слева от строки формул.
В правом вернем углу окна программы находится справочное поле Введите вопрос. Для получения справки на конкретную тему вводится интересующее понятие и нажать клавишу Enter. Программа выведет на экран список наиболее близких рубрик раздела справки.
Кроме того, в правой части экрана можно вывести область задач, которая объединяет различные команды меню и позволяет открывать существующие файлы, создавать новые, вставлять данные из буфера обмена. Для ее отображения следует выбрать команду Вид – Область задач.
В нижней части окна видна строка с ярлыками листов, имеющих имена Лист1, Лист2 и т.д. Ярлычки используются для перехода с одного листа на другой.
По умолчанию панель инструментов отображается в виде одной строки и на самом деле объединяет две панели: Стандартную и Форматирование. При этом часть кнопок невидимы. Чтобы получить к ним доступ, необходимо щелкнуть на стрелочке, направленной вниз, в правой части панели. Если есть необходимость видеть все кнопки, то следует щелкнуть по этой стрелке и выбрать пункт Отображать кнопки на двух строках. С помощью пункта Добавить или удалить кнопки – Стандартная (Форматирование) можно поместить на панель другие кнопки. Чтобы узнать назначение той или иной кнопки, следует навести на нее указатель мыши. Рядом с указателем появится всплывающая подсказка с названием команды.
С помощью команды Сервис – Настройка можно вызвать диалоговое окно Настройка, которое позволяет разместить на панели инструментов кнопку для любой команды (или удалить ее). Для этого необходимо на вкладке команды выделить в левой части окна название меню, а в правой желаемую команду. Поместив указатель мыши на выбранную команду, перетащить ее при нажатой левой кнопке на панель инструментов и отпустить кнопку мыши, когда рядом с указателем появится значок " + ".
Набор панелей инструментов не ограничивается панелями Стандартная и Форматирование. Щелкнув правой кнопкой мыши на панели инструментов, можно увидеть список всех доступных панелей инструментов для решения конкретных задач. Чтобы вывести нужную панель на экран, достаточно щелкнуть соответствующей строке списка.
Строка меню позволяет получить доступ ко всем используемым командам Excel. Щелчок на одной из главных категорий команд (Файл, Правка и т.д.) открывает меню выбора команды. Щелчок по элементу списка с многоточием открывает диалоговое окно. Если часть команд не используется, то программа скрывает в меню ненужные. Чтобы увидеть меню полностью, следует щелкнуть мышью на двойной галочке, направленной вниз, в нижней части меню. Можно отключить эту функцию, вызвав панель Параметры из меню Сервис.
Некоторые элементы меню имеют справа стилизованную стрелку в виде треугольника. Если навести указатель мыши на стрелку, то откроется дополнительное меню.
Щелкнув правой кнопкой мыши по любому элементу таблицы, можно открыть контекстное меню.
Рядом с некоторыми командами меню указаны формулы клавиатурных комбинаций, дублирующих эти команды.
Для ввода данных в таблицу следует щелкнуть перекрестием курсора по нужной ячейке и ввести данные.
С точки зрения программы Excel ячейка может содержать три вида данных.
Текстовые данные представляют собой строку текста произвольной длины. Ячейка, содержащая текстовые данные, не может использоваться в вычислениях.
Числовые данные - это отдельное число, введенное в ячейку. Excel рассматривает данные как число, если формат данных позволяет это сделать. Как числа рассматриваются данные, определяющие даты или денежные суммы. Ячейки, содержащие числовые данные, могут использоваться в вычислениях.
Если ячейка содержит формулу, значит это ячейка вычисляемая, т.е. значение ячейки может зависит от значений других ячеек таблицы. Содержимое ячейки рассматривается как формула, если оно начинается со знака равенства ( = ). Все формулы дают числовой результат.
Данные в программе Excel всегда вносятся в текущую ячейку.
Прежде чем начать ввод, соответствующую ячейку надо выбрать.
Указатель текущей ячейки перемещают мышью или курсорными клавишами.
Можно использовать и такие клавиши, как HOME, PAGE UP, PAGE DOWN.
Для ввода данных в текущую ячейку не требуется ни какой специальной команды. Нажатие клавиш с буквами, цифрами или знаками препинания автоматически начинает ввод данных в ячейку. Вводимая информация одновременно отображается в строке формул. Закончить ввод можно нажатием клавиши ENTER.
Если нужно только отредактировать содержимое ячейки, а не вводить его заново, следует нажать клавишу F2 (дважды щелкнуть мышью в ячейке) или редактировать содержимое в строке формул. В этом случае в ячейке появится текстовый курсор, который можно использовать для редактирования.
По окончании ввода программа Excel автоматически выравнивает текстовые данные по левому краю, а числовые - по правому. В случае ввода формулы в таблице появляется вычисляемое значение.
В некоторых операциях могут одновременно участвовать несколько ячеек. Для того, чтобы произвести такую операцию, нужные ячейки необходимо выбрать (выделить). Выбранная группа ячеек выделяется на экране: их содержимое отображается белым цветом на черном фоне, а вся группа ячеек обводится толстой рамкой.
Проще всего выбрать прямоугольную область, т.е. ячейки попадающие в определенную область столбцов и строк. Для этого необходимо перевести указатель на ячейку в одном из углов выбираемой области, нажать кнопку мыши и, не отпуская ее, протянуть указатель в противоположный угол области. После отпускания кнопки мыши все ячейки в прямоугольнике выделяются инвертированным цветом. Цвет первой ячейки остается не инвертированным, чтобы показать, что она является текущей.
Для обозначения группы ячеек используется термин - диапазон.
Диапазон (блок) ячеек обозначается в виде адресов первой и последней ячеек, разделенных
двоеточием, например С20:С46.
Протягивание можно производить в любом направлении.
Например, если надо выбрать ячейки от B3 до D3, то можно производить протягивание не только от B3 к D3, но и от D3 к B3. Если щелкнуть на любой ячейке, выделение отменяется.
Вместо протягивания мыши можно использовать клавишу SHIFT. Щелкнув на первой ячейке диапазона, нажать SHIFT и, не отпуская ее, щелкнуть на последней ячейке. Если последняя ячейка находится за пределами экрана, то завершить операцию можно после прокрутки.
Для выделения целых столбцов или строк можно щелкнуть маркеры строк или столбцов по краям рабочей области.
Щелчок на кнопке в левом верхнем углу рабочей области позволяет выбрать весь рабочий лист целиком.
Если при выборе ячеек удерживать нажатой клавишу CTRL, то можно добавлять новые диапазоны к уже выбранному. Этим приемом можно создавать несвязанные диапазоны.
|
Объект выделения |
Действие |
|
Ячейка |
Щелкнуть по ней мышью |
|
Несколько смежных ячеек |
Поместить указатель в крайнюю ячейку, нажать левую кнопку мыши и перетащить указатель в нужную строку (по горизонтали, вертикали); Щелкнуть по крайней ячейке, нажать Shift и щелкнуть по последней ячейке диапазона |
|
Несколько отдельных ячеек |
Щелкнуть по первой ячейке, нажать клавишу Ctrl и, не отпуская ее, щелкнуть по требуемым ячейкам |
|
Любая строка |
Щелкнуть по номеру строки |
|
Активная строка |
Shift + клавиша Пробел |
|
Любой столбец |
Щелкнуть по названию столбца |
|
Активный столбец |
Ctrl + клавиша Пробел |
|
Вся таблица |
Щелкнуть по полю в левом верхнем углу рабочей области таблицы |
Многие таблицы могут содержать в ячейках одни и те же данные. При заполнении таких таблиц Excel проявляет высокую дружественность. Уже по первой букве программа "догадывается", что хочет ввести пользователь.
После этого следует установить указатель мыши на правый нижний угол рамки текущей ячейки. Это маркер заполнения. Указатель мыши примет форму крестика.
Перетаскивание маркера заполнения позволяет "размножить" содержимое текущей ячейки на несколько ячеек в столбце или строке. Если содержимое представляет собой число, оно будет автоматически увеличено на единицу. По ходу перетаскивания содержимое последней ячейки отображается в небольшом всплывающем окне. Справа от последней ячейки появится кнопка смарт-тега. Щелчок по ней откроет список действий, которые могут быть применимы ко всем ячейкам области заполнения.
Возможны следующие действия:
|
Копировать ячейки |
Копирует во все ячейки содержимое первой ячейки |
|
Заполнить |
Заполняет ячейки в соответствии с известной последовательностью |
|
Заполнить только форматы |
Копируется не содержимое, а только форматирование исходной ячейки |
|
Заполнить только значения |
Ячейки заполняются в соответствии с известной последовательностью, но форматирование игнорируется. |
|
Заполнить по месяцам |
Ячейки заполняются названиями месяцев |
Для ввода полной даты в качестве разделителя используют точку или дефис.
Например: 01.03.04 или янв-2004.
Допускается использование косой черты: 20/02/05.
Для ввода текущей даты можно использовать сочетание клавиш Ctrl + Shift + ; (точка с запятой).
При вводе времени суток в качестве разделителя следует использовать двоеточие. Например, 8:00 или 17:35.
Для ввода текущего времени Ctrl + Shift + : (двоеточие).
Для ввода часто повторяющихся слов или оборотов можно использовать функцию автозамены. Для этого в меню Сервис следует выбрать команду Параметры автозамены.
Для копирования данных их необходимо выбрать (выделить) и воспользоваться командами меню Правка (Копировать, Вырезать, Вставить), соответствующими кнопками на панели инструментов или комбинацией клавиш:
Ctrl + C - копировать
Ctrl + V - вставить
Ctrl + X - вырезать
Чтобы вставить скопированный диапазон, следует поместить курсор в то место, где будет находиться его левая верхняя ячейка. Или выделить диапазон ячеек, в точности равный скопированному, а затем выбрать команду Вставить.
Скопировав в буфер обмена содержимое одной ячейки, можно заполнить им целый диапазон, используя для этого кнопку Вставить на панели инструментов Стандартная.
При сохранении файла ему присваивается имя и определяется место его хранения. Все файлы, которые создаются в программе Excel желательно хранить в одной папке, чтобы не тратить время на их поиск.
Рекомендуется сразу, после начала работы над документом, сохранить его через меню Файл – Сохранить, а затем выполнять сохранение через каждые 15 минут работы с помощью кнопки Сохранить на панели инструментов Стандартная или включить функцию автосохранения, выполнив команду Сервис – Параметры - Сохранение.
Т.к. у каждой ячейки свой адрес, то достаточно сослаться на адрес ячейки и указать действие, которое необходимо проделать.
Все вычисления в Excel выполняются с помощью формул. Программа Excel рассматривает содержимое ячейки как формулу, если оно начинается со знака равенства (=). Чтобы начать ввод формулы в ячейку, достаточно нажать клавишу "=".
Для составления формул можно использовать математические операции:
|
Сложение |
+ |
|
Вычитание |
- |
|
Умножение |
* |
|
Деление |
/ |
|
Возведение в степень |
^ |
|
Изменение порядка вычислений (оператор выполняется первым) |
( ) |
В формулах могут использоваться и логические операции:
|
Равно |
= |
|
Не равно |
<> |
|
Больше чем |
> |
|
Меньше чем |
< |
|
Больше или равно |
>= |
|
Меньше или равно |
<= |
При создании формул можно пользоваться Панелью формул для контроля. Панель формул включают щелчком на кнопке «=» в строке формул. По окончании ввода формулы на панели отобразиться результат. Щелчок на кнопке ОК (нажатие ENTER) подтверждает ввод формулы. Кнопка Отмена используется, если результат неудачен. При использовании сложных формул и функций панель формул позволяет получать дополнительную помощь и видеть промежуточные результаты вычислений.
Порядок ввода формулы
Предположим, что необходимо найти произведение чисел, хранящихся в ячейках B7 и C7, а результат поместить в D7.
Нажать клавишу "="
Щелкнуть на ячейке B7
Нажать клавишу "*"
Щелкнуть на ячейке C7
По окончании ввода формула в таблице не отображается. Вместо нее в ячейке размещается вычисленное значение. Однако если сделать ячейку с формулой текущей, то формулу можно увидеть в строке формул.
При работе с Excel важно не производить ни каких вычислений "в уме". Даже если рассчитать значение, хранящееся в ячейке, совсем нетрудно, все равно надо использовать формулу. Вычисление результата операции в уме и занесение его в таблицу в виде числа - это неправильная операция, последствия которой проявятся далеко не сразу и могут вызвать ошибки при дальнейшей работе с таблицей.
У каждой ячейки есть свой адрес. Он однозначно определяется номерами столбца и строки, т.е. именем ячейки. Когда, например, в D7 вычисляют значение произведения B7 и C7, то используют адреса ячеек, входящих в формулу.
Но если надо подсчитать значение в следующей ячейке, то вновь придется записывать формулу. Это неудобно для больших таблиц, и процесс можно автоматизировать.
Гораздо удобнее было бы записать формулу для всех ячеек, например, столбца D, чтобы в них автоматически записывалось произведение соответствующих ячеек столбцов B и С.
Формула тогда выглядела бы так:
Умножить значение, находящееся на две ячейки левее данной, на значение, расположенное в ячейке слева от данной.
Адресация по методу "левее", "правее", "ниже" и т.п. не требует абсолютного указания адресов ячеек, входящих в формулу, и называется относительной адресацией.
Если ввести правильную формулу в первую ячейку столбца, а затем произвести заполнение этой формулой всего столбца, то формулы в последующих ячейках столбца также окажутся правильными.
|
A |
B |
C |
|
Цена |
Количество |
Итого |
|
98 |
24 |
2352 |
|
140 |
12 |
1680 |
|
255 |
6 |
1530 |
|
50 |
2 |
100 |
|
0 |
||
|
0 |
||
|
0 |
По умолчанию программа Excel рассматривает адреса ячеек как относительные, т.е. именно таким образом. Это позволяет копировать формулы методом заполнения.
Однако может возникнуть ситуация, когда необходимо сохранить абсолютный адрес ячейки, если, например, она содержит значение, используемое при последующих вычислениях в других строках и столбцах.
Абсолютная ссылка всегда адресуется к одной и той же определенной ячейке или определенному диапазону. Для того, чтобы задать ссылку на ячейку, как абсолютную, надо задать перед обозначением номера столбца или номера строки символ "$".
Символ «$» «запирает» ячейку и Excel не может ее изменить при заполнении формулой или копировании в другую ячейку. Иногда используются смешанные ссылки.
Таким образом, ссылка на ячейку, например A1, может быть записана в формуле четырьмя способами: A1, $A1, A$1, $A$1.
Знак $ перед буквой (именем столбца) означает, что нельзя изменить столбец, перед номером строки – что нельзя изменить строку.
При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа "$".
Если же ссылка на ячейку была внесена в формулу методом щелчка на соответствующей ячейке, то выбрать один из четырех вариантов абсолютной и относительной адресации можно нажатием клавиши F4.
|
A |
B |
C |
D |
E |
|
Накладная |
||||
|
Наименование |
Цена |
Количество |
Итого |
|
|
Процессор Pentium 166 MMX |
98 |
24 |
2352 |
|
|
Процессор Pentium 233 MMX |
140 |
12 |
1680 |
|
|
Процессор Pentium II 266 |
255 |
6 |
1530 |
|
|
Процессор AMD k6-166 |
50 |
2 |
=$B$10*$C$10 |
Рассмотрим использование абсолютных адресов для автоматизации вычислений.
Для этого составим таблицу, как в указанном примере.
|
A |
B |
C |
D |
E |
F |
G |
|
|
1 |
Продажа мороженного по округам города N (тыс.руб.) |
||||||
|
2 |
|||||||
|
3 |
Лето 2005 г. |
||||||
|
4 |
|||||||
|
5 |
Июнь |
Июль |
Август |
Всего |
В процентах |
||
|
6 |
Цетральный |
140 |
160 |
120 |
420 |
30% |
|
|
7 |
Западный |
85 |
80 |
100 |
265 |
19% |
|
|
8 |
Северный |
120 |
135 |
140 |
395 |
28% |
|
|
9 |
Южный |
110 |
115 |
105 |
330 |
23% |
|
|
10 |
Всего |
455 |
490 |
465 |
1410 |
100% |
Рассчитаем долю Центрального округа за все лето в полной сумме: =F6/F10.
Если мы попытаемся скопировать данную формулу в ячейки G7-G10, то в соответствующей ячейке появится запись "#ЗНАЧ!". Это сообщение об ошибочном смещении типов данных. Чтобы запретить Excel механически изменять адрес ячейки, достаточно перед номером столбца и номером строки ввести символ "$", т.е. вместо относительного адреса указать абсолютный.
Выделим ячейку G6 и щелкнем в Строке формул. Появится формула =G6/G10.
Вставим символ $, получим формулу =G6/G$10.
Скопируем полученную формулу в G7-G10.
Для перевода долей округов в проценты выделим столбец G и щелкнем на кнопке % в панели инструментов Стандартная. Все доли будут умножены на 100 и помечены знаком "%".
Если часто приходиться ссылаться на какую-нибудь ячейку (например, в формулах) или диапазон ячеек, то целесообразно присвоить им имя.. Для этого следует щелкнуть на ячейке (или выделить диапазон), открыть меню Вставка, навести курсор на строку Имя и в появившемся дополнительном меню щелкнуть на строке Присвоить. Откроется диалоговое окно, в котором требуется ввести имя для ячейки (диапазона) и щелкнуть кнопку Присвоить.
Обычно имя состоит из одного или нескольких слов, разделенных точками или знаками подчеркивания. Например, имена "ПродажаПринтеров", "Продажа_Принтеров" и "Продажа.Принтеров" являются разрешенными, а имя "Продажа Принтеров" - нет. Стараются использовать имена, которые проще набирать и запоминать.
Нельзя использовать такие имена, как FY1265, поскольку они могут совпадать с адресами ячеек.
С помощью имен можно гораздо быстрее перемещаться между ячейками рабочего листа. Для этого достаточно щелкнуть на стрелке поле имени и выбрать имя блока в окне появившегося списка.
Другой способ перемещения по рабочему листу - использование клавиши F5.
Нажать F5. Откроется диалог Переход, т.е. содержащий список всех имен ячеек и блоков ячеек
в рабочей книге.
В окне списка Перейти выделить нужное имя и щелкнуть на ОК. Блок ячеек с нужным именем станет выделенным.
Функция – это уже готовая сложная формула, по которой проводятся операции вычисления над группой данных определенного типа. Функции задаются с помощью формул, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, называемом синтаксисом. Например, функция СУММ складывает значения или группы ячеек, а функция ППЛАТ вычисляет величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки. Функцию можно вводить в ячейку самостоятельно или при помощи Мастера функций.
Функция автосуммирование используется для суммирования данных в строке, столбце или диапазоне.
Выполнять суммирование данных в строках и столбцах позволяет кнопка Автосумма ( ) на панели инструментов Стандартная. Сначала надо выделить ячейки, содержащие складываемые числа, можно включить в выделение и и пустую ячейку, которая должна содержать результат. После щелчка на кнопке Автосумма, Excel добавит формулу в пустую ячейку и просуммирует значения. Результат всегда помещается в последней выделенной пустой ячейке.
Чтобы провести суммирование ячеек в нескольких строках или столбцах и получить общий итог, надо выделить все числа.
|
Например: |
A |
B |
C |
D |
E |
|
|
1 |
Продажа видеокассет |
|||||
|
2 |
Поволжье |
|||||
|
3 |
||||||
|
4 |
Жанр |
Казань |
Самара |
Саратов |
||
|
5 |
Боевики |
179 |
221 |
363 |
||
|
6 |
Комедии |
1197 |
1004 |
898 |
||
|
7 |
Мелодраммы |
765 |
770 |
614 |
||
|
8 |
Мультфильмы |
939 |
672 |
901 |
||
|
9 |
=СУММ(В9:D9) |
|||||
|
10 |
В этом случае программа создаст формулу в последнем выделенном столбце и последней выделенной строке, т.е. в строке 9 и столбце Е. Формула будет такой: =СУММ(В9:D9).
Щелчок на стрелке рядом с кнопкой Автосумма на панели инструментов выведет на экран небольшой список некоторых часто используемых функций. Среди них – функция нахождения среднего значения. Для нахождения среднего значения (максимума, минимума, числа чисел в выделенном диапазоне) необходимо выделить нужный диапазон ячеек и щелкнуть нужную функцию.
Чтобы получить доступ ко всем встроенным функциям программы, следует щелкнуть стрелку рядом с кнопкой автосуммы и в открывшемся меню выбрать пункт Другие функции. Откроется окно мастера функций. В окне мастера можно указать желаемое действие и нажать кнопку Найти. Программа попытается найти подходящую к описанию функцию. По умолчанию окно открывается со списком десяти наиболее часто используемых функций. Щелкнув на строке с названием функции, в нижней части окна можно увидеть краткое описание ее действия и синтаксис функций. Выбрав нужную функцию, следует щелкнуть ОК.
Категории функций
|
Финансовые |
Выполняют различные финансовые расчеты. |
|
Дата и время |
Выполняют операции с данными, содержащими время и даты. |
|
Математические |
Применяют к данным стандартные математические формулы. |
|
Статистические |
Применяются для статистического анализа групп данных. |
|
Ссылки и массивы |
Производят операции с ячейками, ссылками на ячейки и массивами ячеек. |
|
Базы данных |
Выполняют анализ списков или баз данных. |
|
Текстовые |
Выполняют операции с текстовыми данными. |
|
Логические |
Используются для нахождения логических значений. |
|
Информационные |
Выдают информацию об ошибках или других свойствах. |
Вывод всех имеющихся функций производится щелчком в поле Категория на строке Полный алфавитный перечень. Общее количество функций – более 300.
Для вставки функции можно воспользоваться кнопкой на панели инструментов (fx), которая вызывает окно мастера вставки функций. Можно ввести функцию вручную в строке формул.
Все функции имеют похожий синтаксис. После знака равенства записывается наименование функции, а затем в круглых скобках перечисляются ее аргументы:
=ФУНКЦИЯ (a;b;c…)
Аргументов может быть несколько, один или даже ни одного. В последнем случае все равно выводятся пустые круглые скобки. В качестве аргументов могут использоваться числа, выражения, адреса ячеек, а также имена ячеек или диапазонов.
Функция корень вычисляет значение квадратного корня из аргумента.
Например: =КОРЕНЬ (225)
Функция суммирования позволяет одновременно суммировать сразу несколько отдельных ячеек или диапазонов ячеек, т.к. максимальное количество аргументов функции равно 30.
Например: =СУММ(B1:B10;E1:E10)
=СУММ(D2'C5:C12,F25)
Функция условного суммирования используется, когда для вычисления суммы должно соблюдаться какое-либо условие.
Например:
|
A |
B |
C |
D |
E |
|
|
1 |
Товар |
Цена |
Продажи |
Выручка |
|
|
2 |
Фотокамера Pentax MZ-60 |
200 |
2 |
400 |
|
|
3 |
Фотокамера Pentax MZ-30 |
230 |
2 |
460 |
|
|
4 |
Фотокамера Pentax MZ-7 |
270 |
4 |
1080 |
|
|
5 |
Фотокамера Pentax MZ-6 |
300 |
3 |
900 |
|
|
6 |
Фотокамера Pentax MZ-5 |
390 |
5 |
1950 |
|
|
7 |
Фотокамера Pentax MZ-Ы |
890 |
1 |
890 |
Предположим, что нас интересует вклад продаж за месяц фотокамер Pentax ценой большей или равной 300$.
Введем в ячейку E7 формулу: =СУММЕСЛИ(B2:B7,">=300",D2:D7), где:
Функция округления округляет значение числа до заданного количества десятичных разрядов.
Например:
=ОКРУГЛ (23,948;2) - 23,95
=ОКРУГЛ (23,948;1) - 23,90
=ОКРУГЛ (23,948;0) - 24,00
Функция отбрасывания упрощает число, но не путем округления, а простым отбрасыванием лишних разрядов:
=ОТБР (23,948;2) - 23,94
=ОТБР (23,948;1) - 23,90
=ОТБР (23,948;0) - 23,00
Логические функции находят широкое применение для анализа полученных табличных данных.
Функция ЕСЛИ содержит три аргумента:
Например: =ЕСЛИ(B10>0;CУММ(C1:С10);CУММ(Е1:Е10))
Формула приводит к вычислению суммы диапазона C1:C10, если значение в ячейке B10 не отрицательно. В противном случае, вычисляется сумма диапазона E1:E10.
Допускается применение вложенных функций ЕСЛИ.
Например: =ЕСЛИ(B10>0;"Прибыль";ЕСЛИ(В10<0;"Убыток";"Ноль")
Приведенная формула выводит слово "Прибыль" в случае, если значение ячейки В10 положительно, и слово "Убыток", если значение ячейки отрицательно. Если ячейка содержит нулевое значение или пуста. Формула выведет текст "Ноль".
Функция И может содержать до 30 аргументов, в качестве которых выступают логические выражения.
Выдает два значения:
Например, если в ячейке B10 находится число 55, то формула
=И(1<B10;B10<100) выдаст значение ИСТИНА.
Функция ИЛИ выдает значение ИСТИНА в том случае, если хотя бы один аргумент функции принимает значение ИСТИНА.
Например: =ИЛИ(100<B10;B10<10) выведет значение ИСТИНА независимо от того, находится ли в ячейке B10 число 255 или 0,5.
Функция НЕТ используется для проверки того, что значение в конкретной ячейке все еще не соответствует определенному значению. Если логическое выражение в аргументе соответствует истине, функция выдает значение ЛОЖЬ. И наоборот.
Например, если в ячейке B10 находится число 55:
=НЕТ(B10<80) ответ ЛОЖЬ.
Объединение текста из нескольких ячеек в одну используется функция СЦЕПИТЬ.
Например, если в столбце А находятся фамилии, а в столбцах В и С – соответственно имя и отчество, то для получения полного имени используется функция:
=СЦЕПИТЬ(А1,А2,А3)
для объединения текста ячеек можно воспользоваться также значком амперсенда &:
=СЦЕПИТЬ(А1&" "&А2)
Функции регистра используются для изменения регистра символов текста:
|
ПРОПИСН |
преобразует символы текста в прописные |
|
СТРОЧН |
преобразует символы текста в строчные |
|
ПРОПНАЧ |
делает первую букву прописной |
Например: =ПРОПНАЧ(Марк Твен) – получим "Марк Твен"
Функции заданного количества символов – с помощью этих функций можно вывести часть текстовой строки в ячейке. В качестве аргументов используется левый или правый край строки, или позиция символа слева. Вторым аргументом является длина нужного тектса.
|
ЛЕВСИМВ |
выдает заданное количество символов с левого края строки |
|
ПРАВСИМ |
выдает заданное количество символов с правого края строки |
|
ПСТР |
выдает заданное количество символов, начиная с определенного номера символа в строке |
Например, если в ячейке А5 содержится текст "Марк Твен", то, используя формулы
=ПСТР(A5;6;4) – получим "Твен"
=ЛЕВСИМВ(A5;4) – получим "Марк"
Функции длины строки дает возможность определить длину строки текста в конкретной ячейке.
Например, для той же ячейки А5 функция
=ДЛСТР(А5) выведет значение 9
Эта функция часто используется в комбинации с другой функцией, сжимающей пробелы.
Например, используя выражение =ДЛСТР(СЖПРОБЕЛЫ(А5)) получим значение 8.
Для получения текущей системной даты ввести в ячейку следующую функцию:
=СЕГОДНЯ ( )
Функция не имеет аргумента.
Функция ДАТА и ВРЕМЯ позволяют скомпоновать дату и время из данных различных столбцов. Например: в ячейке А5 содержится значение 10, в ячейке В5 – 30, в С5 – 55, тогда, набрав выражение =ВРЕМЯ(А5;В5;С5) получим 10:30:55.
Синтаксис функции ДАТА аналогичен.
Выражение =ДАТА(А5;В5;С5) для значений 8, 3, 2004, помещенных в указанные ячейки, возвратит дату 08.03.2004.
Функции ДЕНЬ и ЧАС выделяют из ячеек, содержащих значения даты или времени, соответственно день или час.
Пусть в ячейке А5 содержится дата 31.12.2004, в ячейке В5 – время 23.30.00.
Тогда
=ДЕНЬ(А5) выведет 31
=ЧАС(В5) выведет 23
Данные функции применяются для статистического анализа данных, и большинство из них требуют определенной математической подготовки.
Функция среднего значения вычисляет среднее арифметическое значение для заданного диапазона или нескольких диапазонов. Общая сумма делится на количество ячеек во всех диапазонах.
=СРЗНАЧ (В1:В50)
= СРЗНАЧ (В1:В50;С1:С50;Е1:Е50)
Если в указанном диапазоне встречаются ячейки, содержащие текст (или пустые), то они не учитываются.
Функция максимального и минимального значения:
=МАКС(В2:Е10) - выдает максимальное значение для заданного диапазона;
=МИН(В2:Е10) - выдает минимально значение для заданного диапазона.
Можно установить также не самое большое, а второе или третье по большинству (или меньшинству) значение, например, для диапазона В2:Е10 второе по величине значение и третье значение снизу можно определить формулами:
=НАИБОЛЬШИЙ (В2:Е10;2)
=НАИМЕНЬШИЙ (В2:Е10;3)
Эта группа объединяет множество специальных финансовых функций для вычисления процентов по вкладам, выплат по займам, амортизационных отчислений и много другого. Большинство имеющихся функций требует бухгалтерской подготовки.
В то же время некоторые из этих функций представляют интерес и для неспециалистов. Как правило, они связаны с оценкой инвестиций, выплатами по кредиту и получению дивидендов.
Чаще всего используются следующие:
Бс (будущая стоимость) – представляет собой сумму инвестиций или кредита после осуществления всех платежей. Для кредита это значение равно нулю.
Плт (платеж) – денежная сумма постоянного размера, которая остается постоянной и выплачивается через определенные периоды времени. Как правило, включает часть основного платежа и проценты.
Кпер (количество периодов) – общее количество периодов платежей. Чаще всего равно количеству месяцев, составляющих срок кредита или инвестиции.
Пс (приведенная или текущая стоимость) – сумма кредита или инвестиции в начальный момент.
Ставка (или норма прибыли) – процентная ставка по кредиту. Как правило, оперируют годовой процентной ставкой. Поэтому, чтобы определить ежемесячную процентную ставку для расчета ежемесячного платежа, её необходимо разделить на 12.
Тип – определяет момент времени, когда осуществляется платеж. Если выплата производится в начале месяца, равен 0, а если в конце – равен 1.
Пример расчета платежей за кредит
Предположим, что вы скопили $6000 и заинтересовались покупкой автомобиля в кредит. Выбрав 2 - 3 модели, нужно оценить, какая покупка будет по силам.
Примем в качестве исходных данных, что процентная ставка за пользование кредитом составит 15%, а максимальный срок погашения – 2,5 года, а первоначальный взнос наличными должен составлять не менее 30% от стоимости автомобиля. Кроме того, при покупке авто обязательным условием является авансовая выплата годовой страховки – 8% от стоимости автомобиля и установка сигнализации.
Исходя из этих условий, составим следующую таблицу:
|
A |
B |
C |
D |
E |
F |
G |
H |
|
|
1 |
Модель |
|||||||
|
2 |
Kia Ria |
Skoda |
Ford |
|||||
|
3 |
||||||||
|
4 |
Стоимость базовая |
9200 |
9800 |
10900 |
||||
|
5 |
||||||||
|
6 |
Страховка |
736 |
||||||
|
7 |
Сигнализация |
350 |
||||||
|
8 |
Первоначальный взнос |
2760 |
||||||
|
9 |
||||||||
|
10 |
Итого начальный платеж |
|||||||
|
11 |
Остаток свободных средств |
|||||||
|
12 |
Кредит |
|||||||
|
13 |
выплаты в месяц при сроке |
|
A |
B |
C |
D |
E |
F |
G |
H |
|
|
1 |
Модель |
|||||||
|
2 |
Kia Ria |
Skoda |
Ford |
|||||
|
3 |
||||||||
|
4 |
Стоимость базовая |
9200 |
9800 |
10900 |
||||
|
5 |
||||||||
|
6 |
Страховка |
736 |
784 |
872 |
||||
|
7 |
Сигнализация |
350 |
350 |
350 |
||||
|
8 |
Первоначальный взнос |
2760 |
2940 |
3270 |
||||
|
9 |
||||||||
|
10 |
Итого начальный платеж |
3846 |
4074 |
4492 |
||||
|
11 |
Остаток свободных средств |
2154 |
1926 |
1508 |
||||
|
12 |
Кредит |
4286 |
4934 |
6122 |
||||
|
13 |
выплаты в месяц при сроке |
30 |
-172,21р. |
-198,24р. |
-245,97р. |
|||
|
14 |
24 |
-192,87р. |
-222,03р. |
-275,49р. |
||||
|
15 |
Формулы массивов позволяют получать сразу несколько результатов для массива данных. Для иллюстрации рассмотрим следующую таблицу:
|
A |
B |
C |
D |
E |
F |
G |
|
|
1 |
Объем продаж за полугодие (руб.) |
||||||
|
2 |
|||||||
|
3 |
Наименование товара |
январь |
февраль |
март |
апрель |
май |
июнь |
|
4 |
|||||||
|
5 |
Компактные камеры |
24000 |
16000 |
15000 |
19800 |
21800 |
30600 |
|
6 |
Зеркальные камеры |
9900 |
0 |
15000 |
0 |
11000 |
18000 |
|
7 |
Цифровые камеры |
10800 |
6500 |
0 |
18000 |
14500 |
24000 |
|
8 |
Фотоальбомы и рамки |
1800 |
400 |
640 |
900 |
780 |
1200 |
|
9 |
Фотопленка |
2400 |
1800 |
1600 |
2000 |
2800 |
3200 |
|
10 |
Проявка и печать |
9600 |
6800 |
7200 |
8400 |
11200 |
12800 |
|
11 |
|||||||
|
12 |
Всего |
58500 |
31500 |
39440 |
49100 |
62080 |
89800 |
|
13 |
Постоянные затраты |
10000 |
11000 |
10500 |
12700 |
13000 |
12300 |
|
14 |
Доход |
Значение дохода будет определяться путем разницы общих продаж в этом месяце и постоянных затрат.
Можно получить сразу во всех ячейках величину дохода. Для этого:
|
14 |
Доход |
48500 |
20500 |
28940 |
36400 |
49080 |
77500 |
Приведенная комбинация клавиш помечает введенную формулу как групповую. Программа автоматически помещает эту формулу в фигурные скобки. Согласно этой формуле, программа находит ячейки одного диапазона данных и вычитает из них значения соответствующих ячеек другого диапазона.
При создании нового файла Excel ширина всех столбцов одинакова, но при необходимости всегда можно изменить ее. С помощью команд меню Формат – Столбец можно отрегулировать ширину столбца желаемым образом, установив ее точное значение.
Если не требуется точное значение, то ширину столбца можно изменить вручную.
Для этого нужно дважды щелкнуть на правой границе заголовка столбца. Столбец будет увеличен до размеров самого длинного текста. Размеры столбцов можно менять произвольным образом, перетаскивая мышью границы их заголовков.
При создании нового файла Excel высота всех строк одинакова. С помощью команд меню Формат – Строка можно отрегулировать высоту строки желаемым образом, установив ее точное значение.
Поменять высоту строк можно с помощью мыши. Для этого нужно дважды щелкнуть на нижней границе заголовка строки. Строка будет увеличены до размеров самого высокого символа в этих рядах ячеек.
Размеры строк можно менять произвольным образом, перетаскивая мышью границы их заголовков.
Для вставки строк и столбцов можно воспользоваться меню Вставка. Для этого следует выделить одну или несколько строк и в меню Вставка щелкнуть по команде Строки. Программа вставит сверху такое же количество. Если выделить один или несколько столбцов и выбрать в меню Вставка команду Столбцы, то программа вставит такое же количество пустых столбцов слева от выделенных.
Для удаления ненужных строк или столбцов надо выделить их и щелкнуть на области выделения правой кнопкой мыши. Откроется контекстное меню, в котором нужно выбрать команду Удалить.
По умолчанию Excel автоматически выравнивает вводимый текст по левому, а числовые значения по правому краю. Выровнять текст по правому или левому краю, по центру столбца можно с помощью кнопок панели инструментов Форматирование: По левому краю, По центру, По правому краю. Если нужно выровнять, например, заголовок по центру нескольких столбцов, то необходимо выделить ячейки, относительно которых надо центрировать заголовок и щелкнуть на кнопке выравнивания По центру.
Можно менять шрифт, начертание, цвет символов и фона, применять различные способы выравнивания и ориентации текста.
Для реализации этих возможностей необходимо выделить область ячеек и вывести диалоговое окно Формат ячеек из меню Формат – Ячейки.
На вкладке Выравнивание в списке По вертикали можно выбрать один из вариантов (например По центру). Здесь же можно изменить способ горизонтального выравнивания, задать размер отступа от края ячейки, направление текста и угол наклона. Excel позволяет поворачивать надписи не только на 900, но на любой другой заданный угол.
Для вертикальных надписей небольшой длины может оказаться полезным способ размещения букв одна под другой, для чего следует щелкнуть на узкой прямоугольной области под словом Ориентация и указать желаемый угол поворота.
Если в одной из ячеек есть слишком длинная строка, то можно установить для нее опцию Переносить по словам. Программа будет автоматически осуществлять перенос строк по словам при вводе текста, не изменяя при этом ширину ячейки. Можно использовать и опцию Автоподбор ширины. В этом случае программа автоматически уменьшит размер шрифта, чтобы содержимое ячейки помещалось в ней.
Можно объединить несколько ячеек в одну и распределить данные одной ячейки на всю область. Для этого нужные данные должны находиться в верхней левой ячейке. Все остальные данные в объединяемой области уничтожаются.
Для объединения ячеек необходимо их выделить и щелкнуть на кнопке Объединить и поместить в центре на панели Форматирование. При этом текст первой ячейке переместиться к центру таблицы, образуя заголовок.
Для отмены распределения следует выбрать объединенную ячейку и щелкнуть по той же кнопке.
Для изменения шрифта, размера или начертания символов, удобнее всего воспользоваться полями Шрифт, Размер шрифта и кнопками Полужирный, Курсив, Подчеркнутый панели Форматирование.
Для изменения атрибутов текста можно открыть окно Формат ячеек. Для этого надо выделить нужные ячейки, а затем вызвать контекстное меню выделенного диапазона и выбрать пункт Формат ячеек. На вкладке Шрифт присутствуют все необходимые возможности Форматирования символов, включая подчеркивание и зачеркивание, выделение цветом, а также верхние и нижние индексы.
Опция Обычный (установка флажка в ней) приведет к сбросу всех параметров форматирования и возвращению к стилю оформления, принятого в Excel по умолчанию.
В несложных случаях для оформления таблиц следует выделить ячейки и воспользоваться кнопками на панели Форматирование – Границы, Цвет фона, Цвет символов.
Максимальные возможности программы по использованию цвета и границ раскрываются только при использовании диалогового окна Формат ячеек.
Вкладка Вид позволяет задать цвет фона ячейки (диапазона). Специальный узор в ячейке (в выделенном диапазоне) можно создать, используя список Узор. Окно просмотра позволяет увидеть, как будет выглядеть заливка перед применением.
Цвет символом задается на вкладке Шрифт.
Для оформления границ надо перейти на вкладку Граница.
Excel предоставляет возможность присвоить числовым значениям в ячейке определенный формат данных. Это позволяет упорядочивать отображение времени, даты, денежных единиц и других числовых значений. Для присвоения желаемого формата следует выделить нужный диапазон ячеек и выбрать в меню Формат команду Ячейки. В окне Формат ячеек перейти на вкладку Число.
Слева расположено окно списка числовых форматов. По умолчанию установлен Общий формат, который отображает числа так, как они вводятся. Можно использовать десятичные символы, но общее число знаков не должно превышать 11.
Доступны следующие форматы:
Нужного формата может не оказаться и на этот случай предусмотрены средства создания произвольных форматов любого вида, например, таких, как 60 км/ч, 12 руб. за кв. м. или 5,80 руб. за десяток.
Произвольные форматы создают в диалоговом окне Формат ячеек, используя специальные коды:
Коды числовых форматов
|
Код |
Применение |
Пример |
|
# |
Указывает значащую цифру |
Формат ###,## отображает 3,50 как 3,5, а 3,977 как 3,98 |
|
0 |
Указывает любую цифру; вместо подстановочных символов справа от десятичного разряда в случае необходимости отображаются нули |
Формат ##0,00 отображает 3,5 как 3,50, а 57,1 как 57,10 |
|
? |
Указывает значащую цифру и выравнивает десятичные разряды |
Формат ???,?? отображает 3,50 как 3,5, 57,10 как 57,1 и 3,977 как 3,98 и выравнивает десятичные разделители |
|
/ |
Отображает число как дробь |
Формат #??/?? отображает 7,5 как 7 1/2 |
|
- |
Дефис, помещаемый в число |
Формат 000-000 отображает число 123456 как 123-456 |
|
“ “ |
Отмечает текстовую строку |
Формат ### ”в час “ отображает число 100 как 100 в час |
Для более наглядного представления табличных данных часто используются графики и диаграммы. Средства программы Excel позволяют создать диаграмму, основанную на данных из электронной таблицы, и разместить ее в той же самой рабочей книге.
Можно создавать диаграммы на одном рабочем листе с данными (внедренная диаграмма) так и на отдельном листе рабочей книги (лист диаграммы).
Excel предлагает в распоряжение пользователя множество различных видов диаграмм: линейные, круговые, кольцевые, лепестковые, трехмерные и другие.
Наиболее удобным является создание диаграммы с помощью Мастера диаграмм. Запуск мастера производится щелчком на кнопке с изображением диаграммы на панели инструментов. Мастер проведет через все этапы создания диаграммы: позволит выбрать тип диаграммы, указать источники данных, определить подписи и заголовки, а также выбрать местоположение диаграммы.
Второй способ позволяет быстро создать диаграмму на основании параметров, принятых по умолчанию. Достаточно выделить область данных и нажать клавишу F11. Программа создаст диаграмму и поместит ее на отдельном листе. При этом одновременно с созданием диаграммы, программа выводит на экран плавающую панель Диаграммы. С помощью этой панели можно редактировать элементы или общий вид диаграммы.
Щелчок на кнопке запуска Мастера диаграмм откроет окно первого шага создания диаграммы (всего шагов 4) – выбор типа диаграммы.
В списке Тип более десятка различных стандартных типов диаграмм.
Среди них:
Гистограмма представляет собой ряд столбиков, каждый из которых соответствует отдельному значению. Группы данных могут отображаться в виде отдельных столбиков или составными столбиками.
Пример такой диаграммы:
Представляет собой ряд линий, расположенных горизонтально. Часто используется для сравнения однотипных данных. Напоминает гистограмму «на боку».
Если нарисовать графики изменения отдельных величин, а затем закрасить очерченные ими области в разные цвета, то получим налагающиеся друг на друга области данных, данные отражают накопленные изменения серии данных:
Графики, как правило, применяются в научных расчетах. Их построение особенно удачно в случаях, когда используется большое количество точек данных для выявления и анализа зависимостей.
Наглядно иллюстрирует долевые пропорции входящих компонентов. В этих диаграммах нет осей. Их использование наиболее целесообразно, когда надо показать относительный вклад в общее целое небольшого количества компонентов.
Точечные диаграммы используются в тех случаях, когда нужно отразить степень зависимости между числовыми значениями в различных группах данных (например, зависимость между ценой и производительностью процессора).
Кольцевая диаграмма аналогична круговой, но обладает большей информативностью, т.к. позволяет показать несколько рядов данных. Для каждого из них используется новое кольцо.
Определившись с типом данных, щелкнуть кнопку Далее для перехода к следующему этапу работы мастера.
На втором этапе следует убедиться в том, что правильно выбран диапазон. Если диапазон выбран ошибочно, то следует воспользоваться сворачивающей кнопкой и выбрать диапазон заново. Указать, как будут группироваться данные в рядах – по строкам или по столбцам. Внесенные изменения отобразятся в окне просмотра. Щелкнуть на кнопке Далее для перехода к следующему этапу.
На третьем этапе откроется окно с несколькими вкладками, в зависимости от типа диаграммы и данных.
На последнем этапе определяется место размещения диаграммы: на текущем или на имеющемся (новом) рабочем листе той же книги. При размещении на отдельном листе диаграмма при печати отображается на отдельной странице в полный размер. При добавлении к текущему листу в качестве объекта, она может печататься отдельно или как часть рабочего листа.
Пример построения диаграммы
Для добавления рядов данных в диаграмму выбрать их и перетащить на диаграмму. Нельзя добавлять отдельные элементы.
Выбрать диаграмму и щелкнуть на нужном объекте. Вокруг выделенного объекта расположатся маркеры выделения. Выбранный объект можно удалить или отформатировать. Для удаления данных выбрать ряд данных или любой элемент ряда, затем нажать клавишу DELETE.
Область диаграммы представляет собой прямоугольник, в котором располагается область построения, очерченная границей. Изменение области построения прямо влияет на размер области диаграммы. Увеличив или уменьшив область построения можно изменить размер всех объектов, размещенных в области построения.
Любой объект диаграммы может быть выделен, а затем отформатирован или удален, за исключением отдельных элементов данных. Элементы данных можно форматировать, но только ряд данных можно добавить или удалить. Для выделения элемента данных выделить содержащий его ряд данных, а затем щелкнуть на нужном элементе.
На панели Диаграммы размещены типичные команды форматирования. Вначале надо выбрать в раскрывающемся списке Элементы диаграммы форматируемый объект, затем использовать кнопки панели Диаграммы для форматирования элемента или всей диаграммы.
Двойной щелчок на объекте открывает диалоговое окно его форматирования. В этом диалоговом окне пять или шесть вкладок. Каждая из них содержит группу установок для форматирования рядов данных. В зависимости от типа диаграммы в диалоговом окне может присутствовать вкладка Фигура или отсутствовать вкладки Ось или Y-погрешности.
ЭТ могут с успехом использоваться для хранения списков однотипных данных – фактически как база данных (БД). Такие списки постоянно применяются в различных областях деятельности. Например, списки работников предприятия с указанием персональных данных для каждого работника, каталоги товаров или аудиозаписей, прайс-листы, телефонные справочники и многое другое. Табличная форма позволяет легко и наглядно представить всю информацию , а также, в случае необходимости произвести выборочный поиск конкретной информации по заданным правилам.
Обычно такие БД содержат столбцы, которые называют полями, и строки с записями данных. Вверху располагается строка заголовка, т.е. название полей.
Примером работы со списками может служить небольшая БД компьютерных игр.
Вначале в строке заголовков вводятся поля: Игра, Разработчик, Издатель и Количество CD. Затем в соответствующие ячейки вводятся данные по каждой игре. Для перехода из ячейки в ячейку следует использовать клавишу Tab, в конце строки – Enter.
Если необходимо создать несколько списков, близких по содержанию, то их необходимо разместить на разных листах. Отдельные листы могут понадобиться для размещения диаграмм, а также однотипных таблиц.
По умолчанию в Excel всегда содержится три листа. Их ярлычки выводятся в нижней части рабочего окна. Щелчок по ярлычку открывает соответствующий рабочий лист. Для добавления нового листа следует выбрать в меню Вставка команду Лист. Новый лист всегда вставляется перед текущим листом.
Операции с листами удобно осуществлять и с помощью контекстного меню. Для добавления листа следует щелкнуть правой кнопкой мыши на ярлычке листа и в появившемся меню выбрать пункт Добавить. Таким же способом можно переименовать, скопировать или удалить ненужные листы.
Можно сразу задать нужное количество листов в рабочей книге, воспользовавшись командой Сервис – Параметры – Общие и в опции Листов на рабочей книге указать нужное количество. После чего следует закрыть программу и вновь загрузить её.
Листы можно добавлять не только по одному, а сразу несколько, предварительно выделив нужное количество листов. Для этого следует щелкнуть на любом ярлычке и, удерживая нажатой клавишу Shift, щелкнуть на последнем в диапазоне ярлычка листа. После выделения нескольких листов программа автоматически объединяет их в группу. В строке заголовка появится слово Группа. Если вводить данные на такие листы, они будут автоматически заноситься в соответствующие ячейки всех листов группы. Чтобы отменить этот режим, следует щелкнуть по одному из ярлычков правой кнопкой мыши и в контекстном меню выбрать Разгруппировать листы.
Для копирования листа следует выбрать в контекстном меню команду Переместить и скопировать. На экране отобразится диалоговое окно, в котором следует указать место, куда копируется лист, и поставить флажок Создавать копию. Программа вставит новый лист, на ярлычке которого будет написано старое имя и рядом в скобках цифра 2. щелкнув по новому ярлычку правой кнопкой мыши и выбрать Переименовать. Название листа выделится и можно ввести новое. Длина имени не должна превышать 31 символ.
Скопировать лист можно и в другую книгу. Для этого следует открыть обе книги. В диалоговом окне Переместить или скопировать появится еще один список, в котором нужно выбрать книгу, куда производится копирование.
Для перемещения листа в пределах рабочей книги нужно просто перетащить мышью его ярлычок в нужное место.
Просматривать длинные списки на листах можно с помощью мыши, щелкая по полосам прокрутки, или с помощью клавиатуры или используя комбинации клавиш:
|
Home |
перемещает курсор к началу текущей строки |
|
Page Up |
переход на один экран вверх |
|
Page Down |
переход на один экран вниз |
|
Ctrl + Home |
перемещает курсор в ячейку A1 |
|
Ctrl + End |
перемещает курсор в крайнюю правую позицию |
По мере увеличения списка точка ввода данных все дальше удаляется от строки заголовка. Большие списки могут занимать несколько экранов. Ориентироваться в таких таблицах, не видя заголовка, очень неудобно. В Excel предусмотрено специальное средство постоянного отображения заголовков или столбца таблицы.
Для этого такие элементы нужно закрепить.
Например, надо постоянно видеть строку заголовков таблицы. Для этого поместим курсор в ячейку столбца, ближайшую к заголовку. Затем откроем меню Окно и выберем команду Закрепить области. Теперь при прокручивании списка заголовок остается на месте.
Чтобы закрепить на экране столбец, необходимо щелкнуть в ячейке справа от этого столбца.
Часто требуется закрепить первый столбец с наименованиями и первую строку с заголовками данных. Для этого следует активизировать ячейку B2 и дать команду Окно - Закрепить области.
Для упрощения процедуры ввода данных можно применить специальную форму. Достаточно иметь строку заголовков таблицы и несколько строк записей. Поместить курсор в крайнюю левую ячейку под последней строкой и выбрать в меню Данные команду Форма. Excel проанализирует список и выведет на экран форму, в которой уже будут предусмотрены все необходимые поля для заполнения списка.
Для добавления новой записи следует прокрутить список до конца вниз, пока вверху справа не появится текст Новая запись (иначе введенные записи будут помещаться в начало списка, вместо старых). Заполнить чистые поля новыми значениями и щелкнуть на кнопке Добавить.
Как правило, списки заполняются спонтанно, по мере поступления данных. В результате через некоторое время получается хаотический список, в котором трудно найти интересующие данные. Используя кнопки сортировки на панели инструментов Стандартная можно одним щелчком привести все записи в порядок.
Щелчок на кнопке Сортировка по возрастанию приведет к расположению текстовых записей в алфавитном порядке. Сортировка по убыванию расположит строки в обратном алфавитном порядке.
Просматривать большие массивы данных в поисках необходимых сведений очень утомительно. Очень часто наличие чрезмерных данных затрудняет поиск. Excel располагает средствами ограничения отображения данных на листах.
Можно скрыть часть элементов таблицы, например, некоторые столбцы или строки. Скрытые элементы не удаляются из таблицы, а лишь временно прячутся.
Чтобы скрыть строку, следует щелкнуть на любой ячейке в этой строке, и выполнить команду Формат – Строка – Скрыть.
Аналогично скрывают столбцы.
Чтобы отобразить скрытые строку или столбец, следует выделить ячейки, расположенные по обе стороны от скрытых элементов. Затем выполнить команду Формат - Строка (Столбец) – Отобразить.
Чтобы вернуть отображение скрытой первой строки или первого столбца следует нажать клавишу F5. Откроется окно Переход, в котором выбрать из списка А1 (или ввести в текстовом поле Ссылка) и ОК. Затем Формат – Строка (Столбец) – Отобразить.
Фильтрация данных позволяет быстро выделить необходимые данные (информацию) из списка, скрыв все ненужные данные. Наиболее простой способ фильтрации данных – использование средства Автофильтр.
Для его применения следует поместить курсор в любое место списка и выполнить команду Данные – Фильтр – Автофильтр. При этом, строка заголовка преобразится и возле каждого заголовка появится кнопка со стрелкой, которая открывает меню. Щелчок по одному из пунктов меню приведет к фильтрации списка по выбранному критерию.
Первые несколько пунктов во всех меню одинаковы:
Остальные пункты перечисляют все элементы данного столбца.
Для примера выберем в меню фильтра столбца Жанр значение RTS (стратегия в реальном времени). Список при этом резко сократится. В нем останется описание только тех игр, которые соответствуют этому критерию. Выберем в столбце Жанр опцию меню фильтра Условие. Откроется диалоговое окно Пользовательский автофильтр. Расширим диапазон отображаемой информации. В верхнем поле справа выберем из списка значение RTS, а в поле под ним – PRG (ролевая игра). В нижнем левом поле выберем значение Равно и щелкнем в середине на переключателе «ИЛИ». Фактически задано условие: отображать все игры, отвечающие как одному, так и другому критерию. Если поставить переключатель в положение «И», то условие ужесточиться настолько, что в списке не окажется ни одной игры, т.к. в исходном списке нет игр, которые отвечали двум критериям сразу.
Возможны и другие сочетания условий, с помощью которых можно конкретизировать запрос.
Доступны следующие значения условий:
Фильтр первые 10 применяется только к числовым значениям. Если поместить курсор в столбец с числовыми данными, то при выборе этого фильтра на экран будет выведено диалоговое окно Наложение условий по списку. На самом деле можно ввести любое число от 1 до 500, а также указать дополнительные условия. Например, наименьшие и наибольшие значения. В самом крайнем справа поле следует выбрать, к чему относится цифра значений: к количеству элементов списка или имеется в виду процентное количество элементов списка.
После применения одно из списка меню Автофильтр стрелка на кнопке становится синей. Для изменения условия следует щелкнуть по ней и выбрать другое значение или выбрать Все, чтобы отменить действия фильтра.
Распечатка таблиц excel имеет свои особенности. Они связаны с отсутствием привязки размеров таблиц к какому-то формату бумаги. В зависимости от потребностей пользователя эти таблицы могут иметь значительные размеры, простираться в длину и в ширину. Поэтому, создавая таблицу, необходимо учитывать эти свойства таблиц заранее.
Перед началом процесса печати следует настроить параметры расположения таблицы на бумаге. Для этого следует открыть меню Файл и выбрать команду Параметры страницы.
Можно визуально определить, как будет выглядеть распечатка, воспользовавшись инструментом предварительного просмотра страниц перед печатью. Для этого следует выполнить команду Файл – Предварительный просмотр. При этом откроется окно, в котором будет показана готовая таблица со всеми элементами оформления.
Кнопка Масштаб позволяет увеличить изображение.
Кнопка Печать вызывает диалоговое окно печати. В нем можно выбрать требуемый принтер (если их несколько), количество копий, необходимый диапазон печати (всю книгу или отдельные листы). Если принтер не подключен, то можно пометить опцию Печать в файл и вывести таблицу на печать позднее.
Кнопка Страница вызывает окно установки параметров страницы.
Кнопка Поля отображает в окне просмотра местоположение заданных полей страницы (выводятся пунктирными линиями)
Кнопка Закрыть возвращает к обычному представлению таблицы.
С помощью кнопки Разметка страницы можно вывести на экран линии разметки страниц, по которым программа будет выводить документ на печать.
Фильтрация списка позволяет выделить из беспорядочного собранных записей группы данных, отвечающих определенному критерию. Однако во многих случаях группировка данных планируется еще на начальной стадии создания таблицы. Информация записывается в определенном порядке, облегчающем ее последующее восприятие. Например, прайс-листы крупных фирм содержат не просто список имеющегося ассортимента товаров, а разбиты не несколько крупных товарных групп, которые в свою очередь, могут быть разделены на более мелкие подгруппы.
Такой метод можно назвать структурным. Весь массив разбивается на несколько уровней вложенности, а каждый подраздел представляет собой определенный уровень структуры. Для нахождения интересующих данных не требуется изучать весь список, нужно лишь пролистать его в поисках нужной структурной группы.
Для организации информации таким образом Excel располагает специальным средством структурирования.
Рассмотрим структурирование табличных данных на примере таблицы:
Данные в таблице должны быть правильно подготовлены, а все формулы правильно введены и согласованы. Согласование подразумевает одинаковый порядок их расположения на рабочем листе. Структура может быть применена ко всему рабочему листу или только к выделенному диапазону данных.
Для создания структуры следует выделить нужный диапазон и выполнить команду Данные – Группа и структура – Создание структуры. Произойдет преобразование данных, и на рабочем листе появятся дополнительные элементы управления структурой – линии группировки данных в виде квадратных скобок и кнопки включения и выключения отображения структуры, расположенные на специальных панелях слева и вверху таблицы. Кнопки с цифрами 1,2,3,..соотвествуют номеру вложенности. Внешний уровень имеет номер 1. Кнопки со знаком «+» и «-» включают и отключают отображение структурной группы соответствующего уровня. Если структура полностью развернута, то об этом будут свидетельствовать кнопки со знаками «-».
Возможна ситуация, когда рабочий лист не содержит формул, а отражает лишь список данных. И хотя эти данные также могут быть разбиты на группы, попытка воспользоваться автоматической функцией создания структуры окончиться неудачей.
Для этого воспользуемся таблицей:
Поместим курсор в любую ячейку столбца Жанр и щелкнем на кнопке сортировки по алфавиту. Записи будут сгруппированы по принадлежности к тому или иному жанру игры. Вставим перед каждой группой пустую строку и введем название жанра. Выделим эту строку цветом и полужирным шрифтом.
Для создания структуры следует выделить строки, содержащие данные одной группы, и сгруппировать их (Данные – Группа и структура – Сгруппировать). При этом итоговая строка или строка заголовка не включается в группу.
Если произошла ошибка и сгруппированы не те данные, можно отменить группировку командой Данные – Группа и структура – Разгруппировать.
Открыть меню Данные и выбрать команду Группа и структура – Удалить структуру. Независимо от состояния структуры в момент команды она полностью разворачивается, а все элементы управления структурой удаляются.
Сводные таблицы образуются путем извлечения и пересчета исходных данных из списка или базы данных. Они образуются не в результате изменения исходных данных, а представляют собой новую структуру, которая может быть размещена на том же листе, где находятся исходные данные, или на отдельном рабочем листе. При этом новая структура сохраняет динамическую связь с источником данных и может легко обновляться при добавлении новых данных.
Наилучшим исходным материалом являются списки или внешние базы данных. Список с исходными данными содержит поля (столбцы) и записи (строки).
Поля представляют собой основные категории, которые объединяют различные элементы (подкатегории). Записи в строках не обязательно сортировать, нет необходимости вводить и какие-либо формулы. Механизм создания сводных таблиц подразумевает автоматическое подведение итогов по категориям, что позволяет получить выборки данных из огромной таблицы в считанные минуты.
Рассмотрим процедуру создания сводной таблицы на примере следующих данных:
Скроем сведения о месячных продажах
Откроем меню данные и выберем команду Сводные таблицы. Откроется окно мастера создания сводных таблиц и диаграмм. На первом этапе следует определить исходные данные для создаваемой сводной таблицы. Мы используем имеющуюся таблицу, поэтому оставим переключатель «в списке или базе данных Microsoft Excel» (по умолчанию). Если исходные данные содержаться во внешней базе данных, то следует изменить положение переключателя.
Переход к следующему этапу осуществляется щелчком по кнопке Далее.
На втором этапе надо указать диапазон исходных данных для создания сводной таблицы. Если данные были предварительно выделены до запуска мастера, то координаты нужного диапазона уже будут введены в поле Диапазон. В противном случае следует щелкнуть по кнопке на правой границе поля. Диалоговое окно свернется в узкую полоску, освободив экран. Выделим необходимый диапазон и опять щелкнем на кнопке справа для возвращения к диалоговому окну. Щелкнем Далее.
На следующем этапе следует указать местоположение создаваемой таблицы.
Возможны два варианта:
По умолчанию предлагается создать таблицу на новом листе. Оставим этот вариант и щелкнем Готово для завершения процедуры.
Откроется новый лист рабочей книги с шаблоном макета сводной таблицы. Одновременно появится палитра со списком полей сводной таблицы и панель инструментов Сводная таблица.
Шаблон содержит несколько областей:
В зависимости от того, какой элемент из палитры полей будет перемещен в конкретную область, внешний вид таблицы сильно измениться. Все доступные поля использовать вовсе необязательно. Главная задача – получить структуру максимальной наглядности и информативности. Если вариант размещения какого-либо элемента нас не устраивает. Можно перетащить его на другую позицию или вообще вывести из шаблона, чтобы попробовать другой вариант.
В палитре списка полей несколько элементов:
Перетащим элемент Производитель оборудования на область шаблона «поля строк» Вид и форма шаблона изменятся. В нем появится список производителей с заголовком, рядом с которым находится треугольная стрелка, с помощью которой открывается список активных элементов. Вначале активны все элементы и будут видны все производители оборудования в списке.
Перетащим элемент Тип камеры на область «поля страницы». На шаблоне появится новый элемент и дополнительное поле со списком. По умолчанию на нем указано Все, т.е. данные приводятся по всем видам камер.
Для завершения операции перетащим из списка элементов поле «1-й квартал» в большую область «поле данных» на шаблоне. Сводная таблица примет законченный вид: появится не только внешнее поле Сумма по полю 1 квартал, но и столбец Итого с данными о продажах за этот период.
Сводная таблица позволяет быстро проанализировать результаты продаж по любому производителю техники или любой группы производителей, а также по типу фотокамеры. Для этого щелкнем на кнопке, открывающей список, и уберем галочки с неинтересных нам элементов. Результат в столбце Итого будет моментально меняться в зависимости от выбранных значений.
Перетащим в область данных еще один элемент из списка палитры элементов, например, 2-й квартал. Когда указатель находится над областью сводной таблицы, рядом с курсором появляется пиктограмма. Пиктограмма рядом с курсором разбита на четыре части, соответствующие областям шаблона. Синим цветом отмечается область, в которую будет помещено перетаскиваемое поле.
Новый элемент сразу усложнит строение таблицы. Для каждого элемента строки появятся два поля с данными по 1-му и 2-му кварталам. Для упрощения оставим в списке лишь фирмы Canon и Olympus.
Для этого можно использовать следующие операции:
Некоторые параметры полей сводной таблицы можно изменить. Для этого нужно вызвать контекстное меню щелчком правой кнопки мыши по кнопке поля и выбрать пункт Параметры поля. На экране откроется диалоговое окно Вычисление поля сводной таблицы, которое содержит следующие параметры:
Если не хватает информации об итогах, приведенных в сводной таблице автоматически, то можно вставить дополнительное вычисляемое поле. Если при этом нет возможности вводить какие-либо формулы или дополнительные строки и столбцы, можно использовать уже имеющиеся поля, указав программе, какие операции над ними нужно выполнить.
Для создания вычисляемого поля надо вызвать палитру Сводные таблицы, а затем Формулы – Вычисляемое поле. На экране откроется окно Вставка вычисляемого поля. Надо ввести имя нового поля. В поле Формула можно составить формулы для вычисления новых значений, используя уже имеющиеся поля, приведенные в списке. Выбрав название поля следует щелкнуть Добавить. Название поля появится в списке формул после знака равенства. Затем можно выбрать название другого поля и Добавить. Составив формулу, щелкаем на кнопке ОК. Новое поле автоматически добавляется в область данных.
PAGE