Табличный процессор Excel Подготовила: Камышная И.Н. - презентация

1 Табличный процессор Excel Подготовила: Камышная И.Н.

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

3 Понятие электронной таблицы MS EXCEL Каждый документ EXCEL представляет собой набор таблиц –рабочую книгу, которая состоит из одного или многих рабочих листов, но сохраняется в едином файле с расширением.xls Листы одной рабочей книги обычно связаны между собой тематически, например, можно создать рабочую книгу, в которой будут храниться данные о продажах товаров за отчетный период. На каждом рабочем листе в этой книге может располагаться отчет за один месяц, на отдельном листе квартальный отчет. В случае необходимости рабочая книга может содержать десятки, и даже сотни рабочих листов. Каждый рабочий лист имеет название, которое можно изменять. На одном рабочем листе могут располагаться несколько таблиц.

4 Понятие электронной таблицы MS EXCEL Если рассматривать окно приложения EXCEL, то видно, что рабочий лист поделен на строки и столбцы. Каждая строка имеет номер, каждый столбец помечен буквой, а рабочий лист состоит из ячеек, которые образуются на пересечении столбцов и строк. Столбцы обозначаются латинскими буквами: А, В, С... Если букв не хватает, используют двухбуквенные обозначения, т.е. после столбца Z идет AA, затем AB и так далее. Максимальное число столбцов в таблице – 256. Строки нумеруются целыми числами. Максимальное число строк, которое может иметь таблица –

5 Понятие электронной таблицы MS EXCEL На пересечении столбцов и строк находятся ячейки. Каждая из ячеек имеет адрес, который, формируется как последовательная запись буквы столбца и номера строки без пробела между ними, таким образом, A1, FH31 и RР65000 – допустимые номера ячеек. Обращение к конкретной ячейке осуществляется по ее адресу как в шахматах. Данные в электронной таблице записаны в ячейки. Программа Excel вводит номера ячеек автоматически. Одна из ячеек на рабочем листе всегда является текущей. В большинстве операций используется именно она. Текущая ячейка обведена жирной рамкой, а ее номер и содержимое отражены в строке формул. Excel сохраняет только те столбцы и строки, в которые действительно вводились данные. При работе с электронной таблицей следует стараться работать с ячейками, расположенными ближе к левому верхнему углу. В этом случае значительно экономится дисковое пространство, используемое при сохранении документа.

6 Понятие электронной таблицы MS EXCEL В стандартной конфигурации присутствуют те же панели Стандартная и Форматирование, но состав их несколько отличается от Word. Названия пунктов главного меню EXCEL также почти совпадают с Word, но отличаются опциями, отсутствует пункт Таблица, вместо него появился пункт Данные. Под панелями инструментов есть еще одна строка – строка формул. Крайнее левое поле в нем – поле имени, в котором отображается адрес или имя текущей ячейки. На горизонтальной полосе прокрутки располагаются ярлыки рабочих листов. Эта полоса поделена на зону прокрутки листа (справа) и зону смены листьев (слева от ярлыков листов). Последняя строка окна – строка состояния.

7 Понятие электронной таблицы MS EXCEL Указатель мыши в табличном процессоре EXCEL также играет важную роль. Форма его зависит от его положения и от функций, которые могут быть выполнены при нажатии левой кнопки мыши.

8 Типы данных Текстовые данные представляют собой строку текста произвольной длины. Программа Excel воспроизводит такие данные точно в том виде, в каком они были введены. Ячейка, содержащая текстовые данные, не может использоваться в вычислениях, но тексты могут обрабатываться с помощью специальных функций. Если Excel не может интерпретировать данные в ячейке как число или как формулу, программа считает, что это текстовые данные. Числовые данные это отдельное число, введенное в ячейку. Excel рассматривает данные как число, если формат данных позволяет это сделать. Как числа рассматриваются данные, определяющие даты или денежные суммы. Ячейки, содержащие числовые данные, могут использоваться в вычислениях. Если ячейка содержит формулу, значит эта ячейка вычисляемая, то есть значение ячейки может зависеть от значений других ячеек таблицы. Содержимое ячейки рассматривается как формула, если оно начинается со знака равенства «=». Формулы в ячейках таблицы не отображаются. Вместо формулы воспроизводится результат, полученный при ее вычислении. Чтобы увидеть формулу, хранящуюся в вычисляемой ячейке, надо выделить эту ячейку и посмотреть в строку формул. Изменения в формулы вносят редактированием в строке формул.

9 Ввод данных и редактирование ячеек Данные в программе Excel всегда вносятся в текущую ячейку. Прежде чем начать ввод, соответствующую ячейку надо выбрать щелчком мыши. Указатель текущей ячейки перемещают мышью или курсорными клавишами. Можно использовать и такие клавиши, как HOME, PAGE UP и PAGE DOWN. Для ввода данных в текущую ячейку не требуется никакой специальной команды. Нажатие клавиши с буквой, цифрой или знаком препинания автоматически начинает ввод данных в ячейку. При этом в ячейке появится текстовый курсор, в строке состояния будет написано Ввод, а часть пунктов меню и кнопок панелей будут недоступны. Вводимая информация одновременно отображается и в строке формул. Закончить ввод можно тремя способами: нажатием клавиши ENTER, щелчком по «зеленой галочке» слева от строки формул, щелчком левой клавишей мыши в новой ячейке. При нажатии клавиши ESC или щелчком по «красному крестику» слева от строки формул ввод отменяется.

10 Ввод данных и редактирование ячеек Чтобы ввести данные в строки в виде списка, заполните ячейку первого столбца, а затем нажмите клавишу табуляции для перемещения в следующую ячейку. После ввода данных в первую строку нажмите клавишу ENTER, чтобы перейти на начало следующей строки. Если ячейка, расположенная в начале следующей строки не становится активной, выберите команду Сервис/Параметры, а затем – вкладку Правка. В группе Параметры установите флажок Переход к другой ячейке после ввода, а затем из списка в направлении выберите Вниз. По окончании ввода программа Excel автоматически выравнивает текстовые данные по левому краю, а числовые – по правому. В случае ввода формулы в ячейке таблицы появляется вычисленное значение. Следует помнить, что при вводе предыдущее содержимое текущей ячейки теряется. Если нужно только отредактировать содержимое ячейки, а не вводить его заново, следует нажать клавишу F2 или дважды щелкнуть по редактируемой ячейке мышью и редактировать содержимое в строке формул или в самой ячейке. Чтобы удалить содержимое ячейки, выделите ее, дважды щелкнув по ней мышью и нажмите клавишу Delete (Del).

11 Ввод и форматирование текста Текстовой по умолчанию считается информация, которую нельзя интерпретировать как число. То есть, вводя с клавиатуры буквы, мы автоматически делаем ячейку текстовой. При вводе текстовой информации, если в ячейке на заданны другие параметры форматирования, она размещается горизонтально в строке. Если следующие ячейки пусты, то строка закроет их, если нет, то строка визуально прервется на границе ячейки. На следующем рисунке приведен пример размещения текста в ячейке А1. Кажется, что ячейки В1 и С1 также заняты. На самом деле нет – они свободны.

12 Ввод и форматирование текста Для задания шрифта или выравнивания, можно воспользоваться знакомыми кнопками панели инструментов Форматирование. Иногда, например, при размещении заголовка таблицы необходимо бывает объединить ячейки и поместить текст в центре. Для этого служит кнопка (Объединить и поместить в центре). Если этих кнопок недостаточно для придания введенному тексту желаемого вида, или нужно отменить объединение, следует выбрать пункт меню Формат/Ячейки. Откроется окно Формат см. следующий рис., где представлены все возможности по форматированию данных в Excel.

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

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

15 Ввод и форматирование числовых данных Информация, которая может быть интерпретирована системой как числовая, будет считаться таковой. Визуально можно определить, что введено число по выравниванию: числа по умолчанию выравниваются вправо, в отличие от текста, который выравнивается влево, если не задан другой формат. Если в системе Windows принят отечественный стандарт записи чисел, то десятичный разделитель – это запятая, то есть число, записанное как , не будет считаться числом. Правильная запись – 23,445. Существует набор форматов, в которых может вводиться и выводиться числовая информация. Форматировать числа можно, выбрав пункт меню Формат/Ячейки, вкладку Число, а также пункт контекстного меню Формат ячейки. Если для ячейки задан определенный формат, то числа и результаты вычислений, вводимые в ячейку впоследствии, будут преобразовываться в этот формат.

16 Форматы чисел Обычный. Число выводится таким образом, чтобы показать наиболее точное приближение к хранящемуся в ячейке значению. Если число не помещается в ячейку, оно округляется так, чтобы поместилась последняя значащая цифра. Если число не может поместиться в ячейку данного размера, она заполняется знаками #####. Чтобы увидеть число, нужно увеличить ширину столбца или уменьшить размер шрифта. Денежный и финансовый форматы устанавливаются командой Формат/Ячейка/Число. При задании такого формата и выбора валюты числу приписывается знак выбранной валюты, например, 120,00 р., $500. Можно воспользоваться кнопкой панели инструментов Денежный формат. Не следует пытаться вводить знак валюты вручную. Процентный формат. 12% - это число в процентном формате, которое реально равно 0,12. Чтобы перейти в процентный формат, воспользуйтесь кнопкой Процентный формат. Дата. Число в формате даты хранится в виде целого числа – номера дня с 1 января 1900 г. При вводе даты используйте точку или дефис в качестве разделителя, например, или Янв-96. Чтобы ввести текущую дату, нажмите клавиши CTRL+; (точка с запятой). Самый простой формат даты краткий формат: – 12 февраля 2003 года. Если записать 12.03, то автоматически будет проставлен текущий год. Если написать 12.13, то система прочитает эту дату по-американски, то есть 13 декабря текущего года. Если будет написано 13.13, то информация будет записана как текст. Время. Тоже хранится как число. Краткий формат времени 12:15. Может содержать секунды. Внутреннее представление – доля прошедших суток, то есть 12:00 при преобразовании в общий формат окажется 0,5. Для отображения времени суток в 12- часовом формате введите букву «a» или «p», отделенную пробелом от значения времени, например 9:00 p. В противном случае время будет интерпретировано на основе 24-часового формата. Чтобы ввести текущее время, нажмите клавиши CTRL+SHIFT+: (двоеточие).

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

18 Автоматизация расчетов. Формулы Таблица может содержать как основные, так и производные данные. Достоинство электронных таблиц заключается в том, что они позволяют организовать автоматическое вычисление производных данных. Для этой цели в ячейках таблицы используют формулы. Формулой называется выражение, в соответствии с которым вычисляется значение ячейки. Под формулой понимается набор операндов (чисел, встроенных функций, адресов отдельных ячеек, блоков ячеек), соединенных знаками математических операций. При составлении формул используются знаки операций «+» – сложение, «-»- вычитание, «*» - умножение, «/» - деление и «^» - возведение в степень.

19 Автоматизация расчетов. Формулы Программа Excel рассматривает содержимое ячейки как формулу, если оно начинается со знака равенства (=). Однако вводить формулы более удобно, если в строке формул щелкнуть на кнопке Изменить формулу. В этом случае непосредственно под строкой формул открывается Палитра формул, содержащая вычисленное значение указанной формулы. Приоритет этих операций соответствует приоритету, принятому в математике. Функции набираются либо вручную, либо с помощью Мастера функций. Мастер функций содержит перечень категорий, в которых перечисляются функции с указанием их назначения, списки аргументов и возвращаемых (вычисляемых) ими значениий. Чтобы задать ссылку на ячейку, надо указать в формуле ее имя. Это можно сделать вручную или щелчком на соответствующей ячейке по ходу ввода формулы. При наборе формул между числами, адресами ячеек и знаками операций допускаются пробелы. Пробел не допускается между именем функции и круглой скобкой, открывающей список аргументов. Названия функций, в которых используются руссие буквы, записываются только заглавными буквами. Ссылки на ячейки в формулах могут относиться к текущему рабочему листу, к разным рабочим листам одной рабочей книги, а также к разным листам разных книг. Для ссылки на другой лист из данной книги перед именем ячейки следует указать номер листа в форме:Лист к!адрес ячейки, где к - номер листа, например Лист 2!Е4. Пробелы в этой записи не допускаются.

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

21 Относительная и абсолютная адресация Предположим, что в столбце А со второй по 11 строку записаны числа, а в столбце В в ответствующих строках должны стоять их утроенные значения. Формула =3*А2 ввели в ячейку В2. Чтобы размножить ее, можно воспользоваться следующим способом: поставить указатель мыши на нижний правый угол ячейки B2, так, чтобы он превратился в черный крестик; нажать левую кнопку мыши и потянуть мышь вниз так, чтобы были выделены ячейки с B3 по В11; отпустить кнопку мыши. Теперь в ячейки диапазона с В3 по В11 будут записаны формулы, аналогичные формуле в ячейке В2, но вместо ссылки А2 будут фигурировать ссылки соответственно с А3 по А11, то есть в ячейке В3 будет записана формула =3*А3, в В4 – =3*А4 и так далее до В11.

22 Относительная ссылка По умолчанию в таблицах для связи с зависимыми ячейками используются относительные ссылки. Относительная ссылка – это ссылка, которая автоматически изменяется при изменении адреса ячейки и обозначается указанием соответствующих столбца и строки, например, А2 Аналогично можно заполнять ячейки формулами и по горизонтали, при этом меняться будет буква столбца. Другой способ размножения формулы: выделить диапазон ячеек так, чтобы ячейка, содержащая копируемую формулу была крайней верхней, или нижней, или левой, или правой; выбрать пункт меню Правка/Заполнить и далее указать направление заполнения (Вниз, Вверх, Вправо, Влево) в зависимости от положения копируемой ячейки. Если скопированные формулы содержат относительные ссылки, Microsoft Excel изменяет ссылки в копии формулы. Например, пусть ячейка А12 содержит формулу =СУММ(А2:А11). После копирования в ячейку В12, формула будет ссылаться на соответствующие ячейки этого столбца: =СУММ(В2:В11).

23 Абсолютная ссылка Однако иногда возникают ситуации, когда при заполнении ячеек формулой необходимо ссылаться на одну и ту же ячейку, например, нам нужно умножить ячейки с А2 по А11 не на число 3, а на некий постоянный коэффициент, который мы предварительно записали в ячейку Е5. Т.е. в ячейке В2 записана формула =Е5*А2. Если мы будем копировать формулу по описанным выше правилам в диапазон В3:В11, то получим во всех ячейках кроме В2 значение ноль, т.к. в ячейках Е6, Е7, Е8 и т.д. ноль. В таком случае используется абсолютная ссылка. Абсолютная ссылка – это ссылка, не изменяющаяся при изменении адреса ячейки. Абсолютные ссылки после копирования формулы не изменяются. Для того чтобы задать ссылку на ячейку как абсолютную, надо задать перед обозначением номера столбца или номера строки символ «$». Для этого при выделенной формулы необходимо нажать клавишу F4. Если использовать данную клавишу несколько раз меняется и сама абсолютная ссылка. В нашем примере в ячейку В2 следовало ввести формулу =Е$5*А2, т.е. как бы заблокировать изменение номера строки. Если нужно было бы заблокировать изменение и номера столбца, мы записали бы $Е$5. При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа «$». Таким образом, ссылка на ячейку Е5, может быть записана в формуле четырьмя способами: Е5, $Е5, Е$5 и $Е$5. Две последние записи будут верны для нашей задачи.

24 Пример таблицы АВВВЕ 1Числа Формула =3*А2Формула =Е5*А2Формула =Е$5*А

25 Формула Если ссылка на ячейку была внесена в формулу методом щелчка на соответствующей ячейке, то выбрать один из четырех возможных вариантов абсолютной и относительной адресации можно нажатием клавиши F4. Чтобы сделать формулы более информативными, можно использовать вместо адресов имена ячеек. Они также не будут меняться при заполнении и перемещении. Задать имя можно одним из следующих способов: ввести его в поле имени для текущей ячейки; воспользоваться меню Вставка/Имя/Присвоить, при этом появится диалоговое окно со списком имен текущей рабочей книги, затем ввести имя в поле Имя и щелкнуть по кнопке Добавить. Хотя второй способ является более трудоемким, он предпочтителен по следующим причинам: невозможно ввести недопустимое имя (т. е. содержащее пробелы); если в ячейке, находящейся выше или слева от текущей находится текст, он предлагается в качестве имени, причем пробелы в нем будут заменены символами подчеркивания; можно откорректировать уже заданные имена. Ячейке можно задать несколько имен. При вычислении в формуле вместо имени будет подставляться значение, находящееся в данной ячейке.

26 Автоматическое суммирование Так как суммирование значений в строке или столбце – самая популярная операция при работе с таблицами, на панели инструментов Стандартная расположена кнопка (Автосумма). Просуммировать значения в ячейках можно следующими способами: выделить диапазон ячеек и нажать кнопку Автосумма; при этом: если диапазон расположен в одном столбце, результат появится в ячейке, расположенной под нижней ячейкой диапазона; если диапазон расположен в одной строке, результат появится в ячейке, расположенной справа от крайней левой ячейки диапазона; если диапазон занимает несколько столбцов и строк, результат появится в ряду, следующем за нижним рядом диапазона. Щелкнуть по ячейке, в которой хотите увидеть сумму, а затем по кнопке Автосумма. Появится формула вида =СУММ(диапазон), причем диапазон будет выделен как в формуле, так и в таблице. Выделите мышью диапазон суммирования и нажмите ENTER.

27 Мастер функций Функции – это программы, написанные не встроенном языке Visual Basic. Каждая из программ имеет имя и набор параметров. Функции можно использовать в формулах. При вычислениях имя функции с параметрами заменяется возвращаемым значением. Мастер функций вызывается одним из следующих способов: Пунктом меню Вставка/Функция. Кнопкой по кнопке Вставка функции панели элементов Стандартная. Щелчком по кнопке Вставка функции строки формул. В режиме ввода формулы вместо поля имени крайним левым полем строки формул является поле функций. Оно содержит список последних использованных функций. Последняя строка в списке – «Другие функции…». Если выбрать ее, вызывается Мастер функций.

28 Мастер функций Мастер функций работает в два этапа. На первом этапе окно мастера функций содержит два списка. Левый список – это список категорий функций, правый – список функций, относящихся к данной категории. В нижней части окна дается синтаксис функции и ее краткое описание. В списке категорий функций первая строка – 10 недавно использовавшихся. В этой «категории» содержатся имена функций, с которыми вы работали последними, причем первой в списке будет функция, которая была самой последней. В категории Полный алфавитный перечень дается список всех функций в алфавитном порядке, причем первыми будут функции, имеющие латинские названия. Если выбрать функцию и нажать ENTER, то Мастер перейдет к следующему шагу. На этом этапе вводятся аргументы функций, причем каждому параметру дается полное описание. Как и в режиме ввода информации в ячейку, можно пользоваться мышью для ввода адресов ячеек. Справа от полей ввода после знака равенства выводится значение аргумента, а внизу по возможности значение функции. Если тип аргумента не соответствует требуемому, то будет выдана ошибка #Знач?, выделенная красным цветом. Переходить от аргумента к аргументу следует клавишей Tab или мышью. При нажатии ENTER ввод завершится, возможно, с ошибкой, и вам придется возвращаться в Мастер функций или дописывать функцию вручную. Если в аргументах функции должна использоваться другая функция, то следует воспользоваться полем функции в строке формул.

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

30 Логические функции К логическим функциям относятся функции И и ИЛИ, выполняющие, соответственно, логическое сложение и умножение аргументов, которые являются логическими (булевыми) данными, то есть ИСТИНА или ЛОЖЬ. Также логической является функция ЕСЛИ, возвращающая одно из двух значений в зависимости от первого аргумента.

31 Логическое выражение Логическое выражение принимает значение ИСТИНА или ЛОЖЬ. Обычно здесь записывается результат операции сравнения (например, A1>0) или результат, возвращаемый функциями И и ИЛИ. Значение_если_истина. Здесь записывается то значение, которое будет возвращено, если логическое выражение истинно. Значение_если_ложь (необязательный). Это значение возвращается, если логическое выражение ложно.

32 Статистические функции К статистическим функциям относятся, во- первых, стандартные групповые функции: СЧЕТ – считает количество числовых значений в диапазоне, СРЗНАЧ – возвращает среднее арифметическое аргументов, МИН и МАКС возвращают, соответственно, минимальное и максимальное значение аргументов. Кроме того, к стандартному набору относятся всевозможные вероятностные функции, дисперсии, распределения, корреляции и другие полезные функции.

33 Связывание данных, находящихся на разных рабочих листах В рабочей книге EXCEL имеется несколько листов. Это позволяет не увеличивать размер таблицы за границы стандартной страницы, а вести работу на разных листах. Переход от листа к листу осуществляется щелчком мыши по соответствующему ярлыку. При внесении в формулу адреса ячейки, находящейся на другом листе, он записывается вместе с именем листа в следующем формате: имя_листа!А1, если имя листа имеет пробел, то оно берется в апострофы: Имя листа. Переименование листов. Можно переименовать лист, дважды щелкнув по нему мышью, или выбрав пункт контекстного меню ярлыка листа Переименовать. Лист обязательно должен иметь имя, поэтому просто стереть имя листа не удастся. Выделение листов. Можно выделить группу листов, щелкая по каждому мышью, нажав при этом клавишу Ctrl. Можно выделить группу подряд идущих листов, щелкнув мышью по первому, нажав Shift и щелкнув по последнему листу. Перемещение листов. Лист можно переместить в пределах рабочей книги, зацепив его мышью и перетащив в новою позицию. Можно переместить или скопировать лист или группу листов, выбрав пункт меню Правка/Переместить/скопировать лист. Как связать ячейки одного листа с ячейками другого листа? Простым присваиванием, то есть, чтобы записать в ячейку А3 листа 2 то, что находится в ячейке B1 листа 1, надо перейти в ячейку А3 на листе 2 и ввести в нее формулу =Лист 1!B1. Копированием со связью: Выделить диапазон ячеек, подлежащих копированию, и скопировать их в буфер обмена. Перейти в первую ячейку диапазона, в который собираетесь копировать. Выбрать меню Правка/Специальная вставка и щелкнуть по кнопке Вставить связь. Итоговый диапазон заполнится формулами типа =Лист 1.B1, причем каждая его ячейка будет приравнена к соответствующей ячейке исходного диапазона.

34 Связь с ячейками другой рабочей книги Чтобы связать ячейки одной рабочей книги с ячейками другой, следует: открыть обе рабочие книги, щелкнуть по нужной ячейке первой книги; поставить знак равенства; щелкнуть по окну второй рабочей книги; в строке формул появится имя второй рабочей книги и адрес ее текущей ячейки; щелкнуть по требуемой ячейке второй рабочей книги; в строке формул появится абсолютный адрес этой ячейки; не переходя на первую рабочую книгу, нажать ENTER. Полученная формула будет иметь вид: [Книга 1]Лист 1!А1. Если Книга 1 уже сохранена на диске, формула будет выглядеть примерно так: [Книга 1.xls]Лист 1!А1. Если окно Книга 1 закрыть, то в формулу перед именем файла будет записан путь к нему. Чтобы удобнее было работать с двумя рабочими книгами, их окна можно расположить рядом, выбрав пункт меню Окно/Расположить, а затем в окне Расположение окон указать, как именно их расположить: рядом, сверху вниз, слева направо, каскадом

35 Проверка условий При решении конкретных, реальных задач редко когда не требуется проверять всевозмодные условия. Вы уже немного знаете о функции ЕСЛИ. Самое время поговорить об этом более подробно. Для проверки условий используется функция ЕСЛИ. Она может проверять как значения так и формулы. Функция ЕСЛИ записывается следующим образом ЕСЛИ(логическое_выражение;значение 1;значение 2), где логическое_выражение –это проверяемое условие, результатом вычисления логического выражения может быть либо значение ИСТИНА, если заданное условие выполнимо, либо – ЛОЖЬ, если заданное условие невыполнимо. значение 1 - это значение или другая формула, которые будут, если логическое выражение имеет значение ИСТИНА. значение 2 - это значение или другая формула, которые будут, если логическое выражение имеет значение ЛОЖЬ. Вместо значения 1 или значения 2 может быть другая формула ЕСЛИ, чтобы создавать более сложные условия. До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значения 1 и значения 2.

36 Логическая функция И Логическая функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. И(логическое_значение 1; логическое_значение 2;...) Логическое_значение 1, логическое_значение 2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. Если ячейка B4 содержит число между 1 и 100, то: И(1

37 Логическая функция ИЛИ Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. ИЛИ(логическое_значение 1;логическое_значение 2;...) Логическое_значение 1, логическое_значение 2,... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ. ИЛИ(1+1=1;2+2=5) результат будет ЛОЖЬ ИЛИ(1+1=2;2+2=5) результат будет ИСТИНА Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то: ИЛИ(A1:A3) результат будет ИСТИНА

38 Логическая функция НЕ Меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине. НЕ(логическое_значение) НЕ(ЛОЖЬ) результат будет ИСТИНА НЕ(1+1=2) результат будет ЛОЖЬ.

39 Анализ и обработка данных с использованием консолидации данных В Excel существует возможность составить отчет по нескольким листам, например составить квартальный отчет, если отчеты за каждый месяц хранились в таблицах на отдельных листах. Для этого следует использовать возможности консолидации данных. Консолидация собирает воедино данные отдельных листов или несмежных диапазонов ячеек, суммирует их или использует какую- то другую обрабатывающую функцию и помещает на указанный рабочий лист. В Excel существует несколько способов консолидации: С помощью трехмерных ссылок, что является наиболее предпочтительным способом. При использовании трехмерных ссылок отсутствуют ограничения по расположению данных в исходных областях. По расположению, если данные исходных областей находятся в одном и том же месте и размещены в одном и том же порядке. Этот способ хорошо использовать для консолидации данных нескольких листов, созданных на основе одного шаблона. Если данные, вводимые с помощью нескольких листов-форм, необходимо выводить на отдельные листы, используйте мастер шаблонов с функцией автоматического сбора данных. По категориям, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Этот способ хорош для консолидации данных листов, имеющих разную структуру, но одинаковые заголовки. С помощью сводной таблицы. Этот способ сходен с консолидацией по категориям, но обеспечивает большую гибкость при реорганизации категорий.

40 Консолидация данных с помощью трехмерных ссылок Если исходные листы имеют различные шаблоны и подписи или если требуется создать собственный шаблон, или применить собственные формулы объединения данных, при консолидации следует использовать трехмерные ссылки. Трехмерные ссылки обновляются автоматически при изменении исходных данных. Если все исходные листы имеют одинаковый шаблон, в трехмерных формулах можно использовать диапазон имен листов. Чтобы ввести в объединение новый лист, скопируйте его в диапазон, на который ссылается формула. Чтобы выполнить консолидацию данных с помощью трехмерных ссылок выполните следующие действия: На листе консолидации скопируйте или задайте надписи для данных консолидации. Укажите ячейку, в которую следует поместить данные консолидации. Введите формулу. Она должна включать ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация. Повторите шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты обработки данных. Чтобы ввести ссылку, не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку. Если ячейка находится на другом листе, перейдите на этот лист и укажите нужную ячейку.

41 Консолидация данных по расположению Чтобы выполнить консолидацию данных по расположению выполните следующие действия: Укажите верхнюю левую ячейку конечной области консолидируемых данных. Выберите команду Данные/Консолидация. Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных. Введите исходную область консолидируемых данных в поле Ссылка. Убедитесь, что исходная область имеет заголовок. Нажмите кнопку Добавить. Повторите шаги 4 и 5 для всех консолидируемых исходных областей. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными. При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную.

42 Консолидация данных по категориям Чтобы выполнить консолидацию данных по категориям выполните следующие действия: Укажите верхнюю левую ячейку конечной области консолидируемых данных. Выберите команду Данные/Консолидация. Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных. Введите исходную область консолидируемых данных в поле Ссылка. Убедитесь, что исходная область имеет заголовок. Нажмите кнопку Добавить. Повторите шаги 4 и 5 для всех консолидируемых исходных областей. В наборе флажков Использовать в качестве имен установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно. Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными. Если метки в одной из исходных областей не совпадают с метками в других исходных областях, то при консолидации данных для них будут созданы отдельные строки или столбцы. Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже участвующие в консолидации.

43 Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации При консолидации данных нескольких списков или листов списки и листы должны иметь одинаковые заголовки строк и столбцов. Если в сводной таблице необходимо поместить итоги, при создании сводной таблицы пропустите итоговые строки и столбцы исходных данных. Чтобы сводная таблица быстрее обновлялась при внесении изменений в исходный диапазон, необходимо присвоить имя каждому исходному диапазону, а также использовать эти имена при создании сводной таблицы. Если в диапазон, которому присвоено имя, добавляются новые данные, то при обновлении сводной таблицы они будут включены в нее. Для отображения каждого диапазона исходных данных в консолидации используются дополнительные поля страниц. Элементы полей страниц представляют один или несколько исходных диапазонов. Например, если имеется консолидация данных по нескольким турагентствам («Бегемот», «Светлый путь» и «Кук»), в поле страницы могут находиться элементы, представляющие данные по каждому агентству или их комбинации. Если установить переключатель в положение В нескольких диапазонах консолидации в диалоговом окне Мастер сводных таблиц - шаг 1 из 4, можно выбрать тип поля страницы. Можно отобразить одну страницу для каждого исходного диапазона или страницу, объединяющую все диапазоны исходных данных. Установите переключатель в положение Создать одно поле страницы в диалоговом окне Мастер сводных таблиц - шаг 2 из 4. Можно создать до четырех полей страниц и назначить каждому элементу исходного диапазона имя, а также создать структуру, не имеющую полей страниц. Установите переключатель в положение Создать поля страницы в диалоговом окне Мастер сводных таблиц - шаг 2 из 4. Этот переключатель используется для сравнения частей исходных данных или отображения всех данных.

44 Задание исходных областей консолидируемых данных Исходные области обрабатываемых данных задаются либо трехмерными формулами, либо в поле Ссылка диалогового окна Консолидация. Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах. При описании исходных областей рекомендуется. Для облегчения работы с исходными областями задайте имя каждого диапазона и используйте в поле Ссылка только имена. Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны. Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «К выдаче», находящийся в книге на листе «Сентябрь», введите Сентябрь!К выдаче». Если исходные области и область назначения находятся в разных книгах, используйте имя книги, имя листа, а затем имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа « Товары » в книге «2003», находящейся в этой же папке, введите: '[2003.xls]Товары'!Продажи Если исходные области и область назначения находятся в разных книгах в разных папках диска, используйте полный путь к файлу книги, имя книги, имя листа, а затем имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» листа « Товары » в книге «2003.xls», которая находится в папке «Промежуточные отчеты», введите: '[C:\Промежуточные отчеты\Отдел продаж.xls] Товары'!Продажи Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Чтобы задать описание источника данных, не нажимая клавиш клавиатуры, укажите поле Ссылка, а затем выделите исходную область. Чтобы задать исходную область в другой книге, нажмите кнопку Обзор. Чтобы убрать диалоговое окно Консолидация на время выбора исходной области, нажмите кнопку свертывания диалогового окна.

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

46 Изменение области данных в итоговой таблице Для изменения области данных выполните следующие действия: Щелкните левую верхнюю ячейку существующей итоговой таблицы. Выберите команду Данные/Консолидация. В списке Список диапазонов укажите исходную область, которую следует изменить. Внесите изменения в выбранную область в поле Ссылка. Нажмите кнопку Добавить. Если старая ссылка не нужна, укажите ее в списке Список диапазонов, а затем нажмите кнопку Удалить. Чтобы пересчитать итоговую таблицу, нажмите кнопку OK. Чтобы сохранить новый набор исходных областей без пересчета итоговой таблицы, нажмите кнопку Закрыть.

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

48 Разрешение вопросов, возникающих при консолидации данных Здесь приводятся советы только для тех случаев, когда консолидация данных осуществлялась с использованием команды Консолидация в меню Данные. Они неприменимы, если использовались трехмерные ссылки. Если в результате консолидации данных получен неправильный результат, проверьте что: Правильно заданы исходные области. В диалоговом окне Консолидация выбрана подходящая функция. Конечная область достаточна для размещения консолидируемых данных. Чтобы избежать трудностей с заданием формы области назначения, указывайте при создании таблицы консолидации только верхний левый угол конечной области. Проверяйте, что для таблицы консолидации отведено достаточно ячеек ниже и правее верхнего левого угла. При консолидации данных по расположению убедитесь, что в каждой исходной области содержатся данные одинакового типа и в надлежащем порядке. При консолидации данных по категориям проверьте, что: Заголовки строк или столбцов были включены в исходные области. В диалоговом окне Консолидация установлены флажки В верхней строке, или В левом столбце, или оба флага одновременно. Заголовки категорий одинаковы во всех областях (включая регистр букв). Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации. Категории, которые не нужно включать в таблицу консолидации, имеют заголовки, которые появляются только в одной исходной области.

49 Работа с таблицей Excel как с базой данных Список EXCEL – это таблица простой структуры, имеющая в первой строке заголовки столбцов и в каждом из столбцов однотипную информацию: либо текстовую, либо числовую. В Microsoft Excel в качестве базы данных можно использовать список. При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных. Столбцы списков становятся полями базы данных. Заголовки столбцов становятся именами полей базы данных. Каждая строка списка преобразуется в запись данных.

50 Рекомендации по созданию списка на листе книги Размер и расположение списка На листе не следует помещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно. Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка. Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

51 Рекомендации по созданию списка на листе книги Заголовки столбцов Заголовки столбцов должны находиться в первой строке списка. Они используются Microsoft Excel при составлении отчетов, поиске и организации данных. Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных. Для отделения заголовков от расположенных ниже данных следует использовать границы ячеек, а не пустые строки или прерывистые линии. Содержание строк и столбцов Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные. Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку. Со списками EXCEL определен ряд действий, вызвать которые можно, выбрав пункт меню Данные.

52 Сортировка Таблицу по определенному столбцу можно отсортировать используя кнопки панели инструментов Стандартная Сортировка по возрастанию или Сортировка по убыванию. Для этого: Щелкнуть по ячейке, находящейся в сортируемом столбце; столбец не выделять. Щелкнуть по одной из кнопок Сортировка. Второй способ сортировки – пунктом меню Данные/Сортировка. При этом открывается диалоговое окно Сортировка диапазона. Можно задать до трех уровней сортировки. Переключатель Идентифицировать поля по подписям, заданный по умолчанию, считает первую строку списка строкой заголовка и не включает ее в диапазон сортировки, если задано …обозначениям столбцов листа, то содержимое первой строки тоже будет участвовать в сортировке.

53 Фильтрация Фильтрация нужна, если из большого списка надо выбрать только те строки, которые необходимы в данный момент. Например, из списка студентов надо выбрать только студентов первого курса, или только студентов определенного факультета. Проще всего воспользоваться автофильтром. Выбрав пункт меню Данные/Фильтр/Автофильтр, получим в строке заголовков в каждой ячейке с именем поля кнопку со стрелкой. Нажав на нее, получим список, в котором столько строк, сколько различных значений в столбце, плюс строки (Все), (Первые 10), (Условие).: Если выбрать строку с конкретным значением, то в списке будут показаны только записи с выбранным значением данного поля. Если выбрать Условие, то откроется окно Пользовательский автофильтр: В первой строке в левом поле со списком выбираем операцию отношения (равно, не равно, больше, больше или равно, а также для символьных строк: «начинается на», «не начинается на» и т.д.), в правом поле выбираем или вводим значение для сравнения. Пользовательским автофильтром можно задать два условия, связанных одной из двух логических операций «И» или «ИЛИ». Если выбрать И, то должны выполняться оба условия, если ИЛИ, то для фильтрации достаточно, чтобы оно удовлетворяло хотя бы одному условию. При задании фильтра для символьных данных можно пользоваться знаками * и ?, то есть условие («равно» «Иванов*») будет означать и Иванов, и Иванова, и Иванов И.И. и т.д.

54 Пример фильтрации

55 Расширенный фильтр В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора. Скопируйте из списка заголовки фильтруемых столбцов. Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора. Введите в строки под заголовками условий требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка. Укажите ячейку в списке. Выберите команду Данные/Фильтр/Расширенный фильтр. Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте. Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение Скопировать результаты в другое место, перейдите в поле Поместить результат в диапазон, а затем укажите верхнюю левую ячейку области вставки. Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов. Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, нажмите кнопку свертывания диалогового окна. Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон.

56 Промежуточные итоги Иногда в таблице необходимо подвести промежуточные итоги, например, просуммировать прибыль поквартально или подсчитать средний балл по факультетам. Итоги подводят следующим образом: Отсортировать список по полю, по которому предполагается группировать данные (например, по кварталу или по факультету). Выбрать меню Данные/Итоги. В появившемся диалоговом окне Промежуточные итоги (см. рис. 5) необходимо определить следующее: в поле При каждом изменении в выбираем поле, по которому предварительно отсортировали список; в поле Операция выбрать обрабатывающую функцию, например, Сумма; в поле Добавить итоги по: выбрать поле (можно несколько полей), по которым подводятся итоги; Флажок Заменить текущие итоги заменит итоги, если они уже были подведены; Конец страницы между группами разместит каждую группу данных с итогами на отдельной странице; Включить флажок Итоги под данными для размещения итогов под данными, в противном случае они разместятся над данными. После щелчка по кнопке ОК в списке появятся итоговые строки для каждой группы данных и общий итог по таблице. В полученной таблице слева от номеров строк появятся линии структуры. Если щелкнуть по кнопке со знаком «минус», можно убрать детали структуры, а щелкнув по кнопке «плюс», можно показать детали.

57 Сводные таблицы Сводная таблица – это средство, позволяющие по-разному группировать, объединять большие объемы данных и быстро получать итоги для последующего анализа. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные. Сводная таблица создается Мастером сводных таблиц, используемым для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, выберите команду Данные/ Сводная таблица.

58 Создание сводной таблицы происходит в 4 шага Первый шаг – выбор источника данных. Сводную таблицу можно создать на основе данных, находящихся в списке или базе данных Microsoft Excel, нескольких листах Microsoft Excel, во внешней базе данных, а также в другой сводной таблице. Второй шаг зависит от источника данных. Мы будем рассматривать в качестве источника единичный список. Тогда на втором шаге следует задать диапазон ячеек, которые содержат данные. Если текущей является ячейка, находящаяся в списке, то диапазон определится автоматически. Третий шаг – создание макета сводной таблицы (отсутствует в EXCEL 2000). Справа в окне Мастера располагаются кнопки с названиями столбцов списка. Если переместить такую кнопку мышью в зону макета, на которой написано Столбец, то все имеющиеся значения данного поля станут заголовками столбцов. Если переместить названия столбца туда, где написано Строка, то в таблице будет столько строк, сколько различных значений данного поля в таблице.

59 Создание сводной таблицы происходит в 4 шага В область Данные помещается то или те поля, по которым будут подводиться итоги. Если поле числовое, то по умолчанию задается функция суммирования, если текстовая, то функция подсчета количества значений. Изменить функцию можно, дважды щелкнув по полю в области данных. Откроется окно Вычисление поля сводной таблицы, в котором можно изменить имя поля, действие со значениями поля, формат числа. Если нажать кнопку Дополнительно, то откроется панель Дополнительные вычисления, с помощью которой задаются функции сравнения: отличие, доля, приведенное отличие и другие; при этом надо задать поле и элемент, с которым проводится сравнение. Можно сделать таблицу трехмерной и даже многомерной, если воспользоваться полем Страница. Туда помещаются поля, по которым желательно производить отбор. На четвертом шаге определяется положение сводной таблицы – на текущем или новом листе и задаются параметры (кнопка Параметры). При работе со сводной таблицей можно изменить ее макет либо перетаскивая мышью поля из одной зоны в другую, либо вызовом Мастера, который загружается кнопкой Мастер сводных таблиц панели Сводная появляющейся при создании таблицы. Подведение итогов в сводной таблице производится с помощью итоговой функции (например «Сумма», «Кол-во значений» или «Среднее»). В таблицу можно автоматически поместить промежуточные или общие итоги, а также добавить формулы в вычисляемые поля или элементы полей. Работу со сводной таблицей рассмотрим, используя в качестве исходной таблицу «Туризм» (см. рис.8). В нижеследующих примерах сводных таблиц подведем итог по данным столбца «Количество выехавших». Но вначале определим, из каких частей состоит сводная таблица.

60 Части сводной таблицы Поле страницы это поле исходного списка или таблицы, помещенное в область страничной ориентации сводной таблицы. В этом примере «Цели поездки» является полем страницы, которое можно использовать для подведения итогов по целям поездок. При указании другого элемента поля страницы происходит пересчет сводной таблицы для отображения итогов, связанных с этим элементом. На рис. 9 представлена сводная таблица с подведением итогов суммы числа выехавших для всех целей поездок, а на рис. 10 – только для цели поездки – обучение. Поле данных это поле исходного списка или таблицы, содержащее данные. В этом примере поле «Количество выехавших» является полем данных, подводящим итоги исходных данных в поле или столбце «Количество выехавших». В поле данных обычно подводятся итоги группы чисел, хотя текущие данные могут быть и текстовыми. По умолчанию в сводной таблице подведение итогов текстовых данных производится с помощью итоговой функции «Кол-во значений», а числовых данных с помощью итоговой функции «Сумма». Поля строки это поля исходного списка или таблицы, помещенные в область строчной ориентации сводной таблицы. В этом примере «Континенты» и «Страны» являются полями строки. Внутренние поля строки (например «Страны») в точности соответствуют области данных; внешние поля строки (например «Континенты») группируют внутренние.

61 Части сводной таблицы Поле столбца это поле исходного списка или таблицы, помещенное в область столбцов. В этом примере «Годы» является полем столбца, включающим четыре элемента поля «2000 г.», «2001 г.», «2002 г.» и «2003 г.». Внутренние поля столбцов содержат элементы, соответствующие области данных; внешние поля столбцов располагаются выше внутренних (в примере показано только одно поле столбца). Элементы поля страницы объединяют записи или значения поля или столбца исходного списка (таблицы). В этом примере на рис.10, элементу «Обучение», отображаемому в поле страницы «Цели», приведены в соответствие все данные по цели поездки обучение. Элементы поля это подкатегории поля сводной таблицы. В данном примере значения «Европа», «Америка» и «Азия» являются элементами поля в поле «Континенты». Элементы поля представляют записи в поле или столбце исходных данных. Элементы поля появляются в виде заголовков строк или столбцов, а также в раскрывающемся списке для полей страниц. Областью данных называется часть сводной таблицы, содержащая итоговые данные. В ячейках области данных отображаются итоги для элементов полей строки или столбца. Значения в каждой ячейке области данных соответствуют исходным данным. В нашем примере суммируются все записи исходных данных количество выехавших, содержащие одинаковое название континента, страны и определенный год.

62 Способы прогнозирования значений с помощью анализа «что будет, если» Используя аналитические методы, можно вводить различные исходные данные в формулы рабочего листа и сравнивать получаемые результаты. С помощью функции Подбор параметров пункта меню Сервис можно определить, насколько должна измениться исследуемая величина, чтобы результатом формулы, в которой она используется, было бы требуемое значение, т.е. если результат для одной формулы известен, а вводимое значение нет, то следует использовать функцию Подбор параметра. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение. Чтобы выполнить подбор параметров: 1Выберите команду Сервис/Подбор параметра. Откроется Окно подбор параметров. 2В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу. Кнопка свертывания окна диалога, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует снова нажать кнопку для вывода на экран диалогового окна. 3Введите искомый результат в поле Значение. 4В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение. Эта ячейка прямо или косвенно должна быть влияющей для ячейки, адрес которой указан в поле Установить в ячейке.

63 Сценарии Сценарием в Microsoft Excel называется набор значений подстановки, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения в листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. С помощью Диспетчера сценариев можно исследовать влияние изменения содержимого сразу нескольких ячеек на результат расчета по формулам, в которых используются эти значения, и сохранить некоторые из наборов полученных исходных значений (сценариев). Чтобы создать текущий сценарий и новые сценарии, используя данные существующего рабочего листа, выполните следующе: 1Выберите команду Сервис/Сценарии. 2Нажмите кнопку Добавить. 3Введите необходимое имя в поле Название сценария. 4Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки. 5Установите необходимые флажки в наборе флажков Защита. 6Нажмите кнопку OK. 7Введите необходимые значения в диалоговом окне Значения ячеек сценария. 8Чтобы создать сценарий, нажмите кнопку OK. 9Для создания дополнительных сценариев нажмите кнопку Добавить, а затем повторите шаги с 3 по 7. После завершения создания сценариев нажмите кнопку OK, а затем – кнопку Закрыть в диалоговом окне Диспетчер сценариев. Программа Поиск решений предназначена для решения задач оптимизации. Можно найти оптимальное или заданное значение ячейки путем подбора значений нескольких ячеек, удовлетворяющих нескольким условиям (ограничениям). При использовании средств поиска решения ячейки, которые необходимо использовать, должны быть связаны формулами.

64 Графическое представление результатов расчетов: мастер диаграмм EXCEL Построение графиков и диаграмм в Excel отличается как широкими возможностями, так и необычной легкостью. Любые данные в таблице всегда можно представить в графическом виде. Для этого используется Мастер диаграмм, который вызывается нажатием на кнопку с таким же названием, расположенную на стандартной панели управления. Эта кнопка принадлежит категории кнопок Диаграмм. Мастер диаграмм позволяет легко создавать диаграммы. В отличие от диаграмм Word, которые создавались в отрыве от исходных таблиц, диаграммы Excel остаются привязанными к диапазону ячеек таблицы. Изменения в таблицах приводят к изменениям в диаграмме и, наоборот, если ячейки не защищены и не содержат формул, то их содержимое можно изменить, меняя диаграмму. После нажатия кнопки Мастер диаграмм нужно выделить на рабочим листе место для размещения диаграммы. Установите курсор мыши на любой из уголков создаваемой области, нажмите кнопку мыши и, удерживайте ее нажатой, выделите прямоугольную область. После того как вы отпустите кнопку мыши, вам будет предложена процедура построение диаграмм, состоящие из четырех шагов. На любом шаге вы можете нажать кнопку Готово, в результате чего построение диаграммы завершается, но лучше пройти все шаги до конца.

65 Что это за шаги: задание типа и вида диаграммы. задание диапазона ячеек, по которым будет строится диаграмма. Окно второго шага Исходные данные содержит две вкладки: Диапазон данных, где указывается диапазон исходных ячеек, и направление рядов данных – в столбцах или строках. Ряд, на которой перечислены ряды данных, их имена и диапазоны ячеек, а также диапазон подписей оси Х, а в случае объемной диаграммы – и оси Y. Здесь можно удалить ненужный ряд и вставить новый, задав его имя и диапазон данных. задание параметров диаграммы: наличие и расположение легенды, заголовки диаграммы и осей, наличие и расположение линий сетки. выбирается место размещения диаграммы либо на отдельном листе, который будет создан, либо на одном из существующих. После создания диаграммы можно вернуться к каждому из этапов, выбрав соответствующий пункт меню Диаграмма, либо контекстного меню диаграммы. Каждый элемент диаграммы может быть отформатирован. Для этого нужно выбрать пункт Формат… контекстного меню объекта. Для построения диаграммы можно также воспользоваться командой. Вставка/Диаграмма.

66 Терминологический словарь Автовычисление – инструмент Excel, позволяющий автоматически вычислять некоторые функции. Автозавершение – инструмент, позволяющий облегчить ввод повторяющихся значений ячеек – при вводе первых символов данных, которые уже присутствуют в данном столбце, Excel предлагает автоматически завершить ввод, а также создает список введенных в столбец значений для последующего выбора. Автозаполнение – инструмент, позволяющий оптимизировать ввод дат, названий месяцев, а также чисел, связанных простой зависимостью. Автофильтр – команда пункта меню Данные/Фильтр, позволяющая проводить фильтрацию данных или работать с подмножеством данных списка, который устанавливается и раскрывается в ячейках названий полей, не сортируя и не перемещая ячейки. Абсолютная ссылка – это ссылка, не изменяющаяся при изменении адреса ячейки. Вирус– это компьютерная программа или макрос, которая «заражает» файлы, вставляя в каждый файл собственную копию. Диапазон ячеек – это две или более ячеек рабочего листа. Консолидация данных – технология Excel, позволяющая собрать воедино данные отдельных листов или несмежных диапазонов ячеек, суммировать их или использовать какую-то другую обрабатывающую функцию и поместить на указанный рабочий лист. Относительная ссылка – это ссылка, которая автоматически изменяется при изменении адреса ячейки и обозначается указанием соответствующих столбца и строки, Подбор параметров – это команда пункта меню Сервис, с помощью которой можно определить, насколько должна измениться исследуемая величина, чтобы результатом формулы, в которой она используется, было бы требуемое значение. Поиск решений - это программа, предназначенная для решения задач оптимизации. Пользовательский автофильтр – это пункт списка автофильтра. Условие, с помощью которого можно задать для одного поля два условия, связанных одной из двух логических операций «И» или «ИЛИ». Рабочая книга – это название по умолчанию файла Excel. Рабочая книга состоит из одного или многих рабочих листов. Расширенный фильтр – это фильтр, формируемый из нескольких условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение. Результат можно расположить отдельно от списка. Сводная таблица –это средство, позволяющие по-разному группировать, объединять большие объемы данных и быстро получать итоги для последующего анализа Сценарий - это набор значений подстановки, используемый для прогнозирования поведения модели. Ячейка – минимальная адресуемая часть электронной таблицы, куда записываются данные.