Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
НЕГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧЕРЕЖДЕНИЕ
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
«Омский юридический институт»
Методические указания
к практическим занятиям
по дисциплине «Информатика и математика»
раздел «Основы информатики»
по теме «Табличный процессор Excel»
для студентов всех форм обучения
Специальность 030501
Омск 2005
Составители: к.т.н., зав. кафедрой А.А. Пальянов;
к.т.н., ст.преподаватель В.Е. Огрызков;
к.п.н., преподаватель М.А. Екимова;
к.т.н., преподаватель О.А. Григорьева.
Рецензент: проректор по учебной работе, к.т.н., доц. Ю.Н. Косоротова.
Методические указания одобрены на заседании кафедры правовой информатики НОУ ВПО «Омский юридический институт», протокол № 3 от 24.11.2005.
Методические указания утверждены на заседании методического совета НОУ ВПО «Омский юридический институт», протокол № 36 от 16.12.2005
Методические указания к практическим занятиям по дисциплине «Информатика и математика» раздел «Основы информатики» по теме «Табличный процессор Excel» / Сост. А.А. Пальянов, В.Е. Огрызков, М.А. Екимова, О.А. Григорьева; НОУ ВПО ОмЮИ. – Омск: 2005. – 52 с.
Одним из самых удобных средств для записи исходных данных и результатов их обработки при решении различных финансовых, бухгалтерских, математических, статистических, инженерных задач являются таблицы. Идея создания электронной таблицы, в ячейки которой записываются формулы для выполнения операций над другими ячейками, принадлежит Дэниэлу Бриклину. В 1979 году он с помощью программиста Роберта Френкстона создал первую в мире электронную таблицу для экономических расчетов – Visicaic (наглядный калькулятор).
Современные электронные таблицы называются табличными процессорами и представляют собой программные пакеты с многочисленными возможностями. С 1994 года самым распространенным табличным процессором в мире является MS Excel.
Любой табличный процессор представляет собой универсальное средство, которое можно использовать для автоматизации вычислений, графического представления табличных данных, для анализа и управления данными, математического моделирования и т.д.
Запустить табличный процессор можно одним из известных способов: командой Microsoft Excel из меню Программы (выводится на экран кнопкой Пуск на панели задач Windows), используя созданный на рабочем столе ярлык или с помощью исполняемого файла программы.
После запуска Excel автоматически создается новый документ с условным названием Книга 1 и на экран выводится окно программы (рис. 1). Документ Excel называется рабочей книгой Excel, состоящей из размеченных листов. По умолчанию в новой книге содержится три листа. Один из листов книги всегда активен. Ярлык активного (текущего) листа выделен белым цветом. Лист содержит 256 столбцов с именами от А до IV и 65536 строк с номерами от 1 до 65536. На пересечении строк и столбцов расположены ячейки (более 16 миллионов), каждая из них имеет свой буквенно-цифровой адрес.
Excel входит в стандартный пакет программ MS Office, поэтому большинство элементов окна на рисунке 1 (строки названия, состояния и меню, панели инструментов, полосы прокрутки и рабочее поле) такие же, как и у других приложений этого пакета. Новым элементом является строка формул, с помощью которой можно вводить, просматривать и редактировать данные (например, формулу, число или текст) в выбранной ячейке электронной таблицы. Слева в строке формул находится контекстное ниспадающее меню. В обычном состоянии оно служит для присваивания ячейкам какого-нибудь имени, при вводе формул - для выбора математического, логического или иного действия над данными в выделенной ячейке.
Рисунок 1. Рабочее окно табличного процессора.
Адрес любой ячейки в книге Excel задается тремя координатами – именем листа, идентификатором столбца и номером строки, например, ЛистЗ!F7 (имя листа отделяется от остальной части адреса восклицательным знаком). Если таблица расположена на одном листе, то для адресации ячеек его имя не используют и ограничиваются только буквенным обозначением столбца и номером строки, например A3 или DA25. Адреса ячеек в табличных процессорах называют ссылками. Адрес можно задавать не только для отдельной ячейки, но и для группы смежных ячеек, образующих прямоугольную область, называемую диапазоном. Адрес диапазона ячеек задается в виде последовательности адресов его левой верхней и правой нижней ячеек, разделенных двоеточием, например, A3:D6. Ячейкам и диапазонам можно присваивать собственные имена и использовать их для адресации указанных объектов наряду со ссылками. Кроме того, можно ссылаться на ячейки, находящиеся в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками, а ссылки на данные других приложений - удаленными ссылками.
В Excel можно использовать ссылки двух типов. В ссылках первого типа столбцы обозначаются буквами, а строки числами, например В3. В ссылках второго типа для обозначения строк и столбцов употребляются арабские цифры с буквами R и С, соответственно перед номерами строки и столбца. В адресе сначала указывается номер строки, а потом столбца, например, R7C2. Изменить тип ссылок можно при помощи переключателя Стиль ссылок на вкладке Общие, она выводится на экран командой Сервис-Параметры.
Рабочая книга - это документ табличного процессора, который может содержать от одного до 255 рабочих листов, на которых можно создавать и хранить табличные, графические и текстовые объекты.
Документ с именем Книга1 создается автоматически при запуске Excel. Для создания следующего нового документа нужно в меню Файл (рис. 2) выбрать команду Создать, либо щелкнуть мышью по кнопке на стандартной панели инструментов. Excel присваивает очередному документу условное имя КнигаN , где N – его порядковый номер.
Рисунок 2. Содержание меню файл.
Для сохранения документов применяют команды Сохранить как... или Сохранить из меню Файл (рис. 2). Обычно команда Сохранить как... используется для сохранения создаваемого или загруженного с диска файла под новым именем, а команда Сохранить для сохранения изменений в редактируемом файле документа (сохраненном ранее на диске или загруженном с диска) под прежним именем. После подачи команды Сохранить как... на экран выводится диалоговое окно, в котором нужно выбрать место расположения сохраняемого документа (диск и папку в ниспадающем меню Папка) и ввести его новое имя (в строку Имя файла). Выполнив необходимые установки, диалоговое окно следует закрыть щелчком мыши по кнопке Сохранить. После выбора команды Файл-Сохранить, при повторном сохранении файла документа, диалоговое окно на экран не выводится, измененный файл сохраняется на прежнем месте под своим именем.
Активизировать загрузку файла рабочей книги с диска можно либо командой Открыть из меню Файл (рис. 2), либо щелчком мыши по кнопке на стандартной панели инструментов. В любом случае на экран выводится диалоговое окно Открытие документа, в котором нужно указать место расположения искомого файла (диск и папку) в ниспадающем меню Папка, а затем открыть его, либо двойным щелчком мыши по значку файла, либо щелчком мыши по кнопке Открыть после выделения файла.
Основными типами листов, используемых в рабочих книгах Excel, являются:
лист таблицы - это лист для создания электронных таблиц, встроенных графиков, диаграмм и форматированных текстовых документов;
лист диаграммы, создается Мастером диаграмм ( стр. 25) для размещения отдельной диаграммы имеющей размеры, рабочего поля, которая полностью видна, если окно книги развернуто на весь экран.
В окне рабочей книги ( рис. 1) отображается только текущий рабочий лист, он называется активным. Для смены отображаемого на экране листа служат ярлыки листов, которые располагаются в нижней части окна. Для активизации требуемого листа нужно щелкнуть мышью по ярлыку этого листа. Если в рабочей книге много листов, то часть ярлычков будет скрыта. Доступ к ним обеспечивается кнопками в левом нижнем углу окна. Крайние кнопки служат для перехода на первый или последний лист. Средние кнопки двигают указатель на один лист влево или вправо.
Рабочие листы можно вставлять и удалять, перемещать и копировать, переименовывать и скрывать.
Рабочие листы в книгу Excel можно добавлять командой Вставка-Лист, после подачи этой команды новый лист будет вставлен перед активным рабочим листом.
Для удаления выделенного листа служит команда Правка-Удалить лист. Удалить лист можно также командой Удалить контекстного меню листа, которое выводится на экран щелчком правой кнопки мыши по ярлыку листа.
Изменить расположение выделенного листа и создать его копию можно при помощи диалогового окна Переместить или Скопировать, которое выводится на экран командой Правка-Переместить/скопировать лист… главного меню или командой Переместить/скопировать... контекстного меню листа. Операцию перемещения листа удобно выполнять мышью, для этого нужно установить указатель мыши на ярлыке листа, нажать левую кнопку мыши и, не отпуская ее, перетащить ярлык в нужное место. Копирование можно выполнить аналогично при нажатой клавише Ctrl.
Для изменения имени рабочего листа его выделяют двойным щелчком мыши или командой Переименовать из меню Формат-Лист или из контекстного меню (выводится на экран после щелчка правой кнопкой мыши по ярлыку листа), а затем набрать на клавиатуре новое имя длиной не более 31 символа. Имя листа не должно содержать квадратных скобок, двоеточия, звездочки, вопросительного знака, прямой и обратной косой черты. Ввод имени завершается нажатием клавиши Enter или щелчком мыши в любом месте рабочего поля.
В Excel имеется несколько возможностей для защиты информации. Можно, например, ввести пароль для доступа к файлу рабочей книги, установить для него режим «только для чтения», защитить паролем всю книгу или отдельный лист от несанкционированной модификации, скрыть отдельные листы в рабочей книге и т.д.
Защита книги от внесения изменений выполняется в диалоговом окне, которое выводится на экран командой Сервис-Защита-Защитить книгу (кроме параметров здесь можно установить пароль). Защита листа производится при помощи аналогичной команды Сервис-Защита-Защитить лист. По умолчанию, все ячейки листа имеют статус «Защищаемая ячейка». Если снять это ограничение с некоторых выделенных ячеек (команда меню Формат-Ячейки-Защита), а потом защитить целый лист, то внесение изменений будет запрещено во все ячейки, кроме тех, с которых была снята защита. Данное средство обычно применяют при составлении различного рода бланков, счетов и т. д., имеющих постоянные подписи и данные, а также области для заполнения.
Отдельные листы книги можно защитить от несанкционированного просмотра. Для управления отображением листов в рабочей книге используются команды Скрыть и Отобразить... из меню Формат-Лист. После применения первой команды ярлык скрытого листа в книге отображаться не будет. Доступ к скрытым листам обеспечивается командой Отобразить..., которая появляется в меню Формат-Лист, если в книге имеются скрытые листы.
К указанным операциям относятся ввод данных в ячейки таблицы, их редактирование и форматирование. К операциям редактирования относятся операции изменяющие содержимое ячеек. Форматирование позволяет изменить оформление ячеек, форму представления или вид данных в ячейках.
Любые операции можно выполнять только с выделенными ячейками. Для выделения одной ячейки нужно переместить рамку выделения на эту ячейку щелчком мыши или при помощи клавиш управления курсором. Многие операции можно выполнять с диапазоном ячеек, с группой отдельных несмежных ячеек или диапазонов. Диапазон ячеек можно выделить если, удерживая левую кнопку мыши, протянуть указатель по выделяемой группе ячеек диапазона, либо удерживая клавишу Shift увеличивать область выделения клавишами навигации. Столбец или строку ячеек таблицы целиком можно выделить щелчком мыши по соответствующему заголовку. Несмежные ячейки и диапазоны ячеек выделяются щелчками мыши или протягиванием мыши при нажатой клавише Ctrl.
Ввести данные в активную (выделенную рамкой) ячейку рабочего листа можно с клавиатуры, а также путем копирования или перемещения из другой ячейки. Для активизации ячейки нужно переместить в нее указатель мыши, имеющий вид белого крестика, и один раз щелкнуть левой кнопкой мыши. Далее можно вводить данные с клавиатуры непосредственно в ячейку (при этом курсор ввода находится внутри ячейки, набранные символы будут отображаться в активной ячейке и в правой часть строки формул), либо щелкнуть левой кнопкой мыши в строке формул (при этом курсор ввода переместится в строку формул) и после этого вводить данные в ячейку, набирая их в строке формул. Первый режим ввода называется редактированием в ячейке, а второй – редактированием в строке формул.
Закончить ввод данных можно различными способами: кнопкой строки формул (при этом курсор остается в ячейке), клавишей Enter на клавиатуре (курсор перемещается вниз в соседнюю строку), клавишей Tab на клавиатуре (курсор перемещается вправо в соседнюю ячейку), клавишами навигации на клавиатуре (курсор перемещается в соседнюю ячейку в указанном направлении).
Ввод данных является самым трудоемким и сопряженным с ошибками этапом работы пользователя с любым приложением. Ошибки ввода в Excel можно исправить клавишами Backspace (удаляет символ слева от курсора) и Delete (удаляет символ справа от курсора). Отменить ввод данных в ячейку можно командой Отменить ввод из меню Правка, кнопкой Отменить на панели инструментов Стандартная или комбинацией клавиш Ctrl+Z. После этого отмененное действие можно повторить командой Вернуть ввод из меню Правка, кнопкой Вернуть на панели инструментов Стандартная, комбинацией клавиш Ctrl+Y или клавишей F4 До завершения ввода его отмену можно выполнить также клавишей Esc. Также для отмены ввода данных в ячейку можно использовать кнопку строки формул .
Для включения режима редактирования ячейки следует нажать клавишу F2 или сделать по этой ячейке двойной щелчок мышью. В ячейке появится курсор и ее можно редактировать как обычный текст.
Копирование и перемещение данных из ячейки (диапазона ячеек) в другое место электронной таблицы производится для упрощения и ускорения ввода повторяющихся данных, а также для изменения их расположения. В отличие от копирования, при перемещении данных исходная ячейка (диапазон ячеек) не сохраняет свое содержимое и форматирование.
Указанные операции можно выполнить, используя буфер обмена или при помощи мыши.
Для копирования данных в буфер обмена, нужно выделить исходную ячейку или группу ячеек, а затем выполнить команду Копировать из меню Правка или из контекстного меню (выводится на экран щелчком правой кнопки мыши по выделенным ячейкам), при этом выделенная область будет заключена в рамку. Затем следует установить указатель мыши в ячейку или левый верхний угол диапазона, куда должны быть скопированы данные, и нажать клавишу Enter или выполнить команду Вставить из меню Правка или из контекстного меню. В результате содержимое выделенной области появится в указанном месте, размещаясь вниз и вправо от ячейки, выделенной курсорной рамкой. При перемещении данных, в отличие от копирования, после выделения исходной ячейки или группы ячеек нужно в меню Правка или в контекстном меню выбрать команду Вырезать.
Перетягивание таблицы, самый простой способ, заключается в том, чтобы выделить всю таблицу и перетянуть ее вниз. Эту операцию можно сравнить с вырезанием диапазона ячеек и вставкой его в новое место. Выделите диапазон ячеек, где расположена таблица. Подведите курсор к краю диапазона и, когда курсор примет форму четырехсторонней стрелки, перетяните таблицу, удерживая нажатой левую кнопку мыши.
В режиме редактирования содержимого ячейки с помощью команд из меню Правка можно копировать или перемещать из ячейки часть информации.
При копировании и перемещении ячеек описанными командами копируется или переносится не только содержимое ячейки (содержащееся в ней значение или формула), но и параметры форматирования, условия на вводимые данные и примечания к ячейкам. В Excel имеется возможность вставки из буфера только части помещенной туда информации. После подачи команды Специальная вставка (рис.3) из меню Правка или из контекстного меню можно выбрать следующие варианты вставки:
Рисунок 3. Специальная вставка – специальные возможности.
все - вставляет содержимое и форматирование исходной области;
формулы - вставляет только формулы из исходной области;
значения - вставляет только результаты расчета по формулам из исходной области;
форматы - вставляет только форматирование исходной области;
примечания - вставляет только примечания к ячейкам исходной области;
условия на значения - вставляет только условие на вводимые значения из исходной области;
без рамки - вставляет всю информацию из копируемой области без ограничивающих рамок;
ширины столбцов;
формулы и форматы чисел;
значения и форматы чисел.
Если при вставке необходимо транспонировать выделенный фрагмент (столбцы станут строками, а строки – столбцами), то следует выбрать вариант специальной вставки транспонировать.
Содержимое выделенной ячейки или группы ячеек можно удалить клавишей Delete, командой Содержимое из меню Правка-Очистить или командой Очистить содержимое контекстного меню (выводится на экран щелчком правой кнопки мыши по выделенным ячейкам). Командами меню Правка-Очистить можно также задать другие варианты очистки:
Все - удаляет содержимое и отменяет форматирование ячейки;
Форматы - отменяет только форматирование ячейки;
Примечания - удаляет вставленное в ячейку примечание.
Excel предоставляет несколько возможностей позволяющих упростить и ускорить процесс ввода данных, к которым кроме копирования относятся также автозаполнение, автозавершение, выбор из списка и т.д.
Одной из возможностей Microsoft Excel является построение последовательностей. В программе предусмотрена возможность построения последовательностей календарных дат, интервалов времени, числовых рядов (арифметических и геометрических прогрессий). В режиме автозаполнения можно построить любую последовательность значений с постоянным приращением, включая комбинации чисел и текста.
Для выполнения операции автозаполнение следует выделить ячейку, содержащую исходное значение и переместить курсор мыши (белый крестик) на маркер заполнения – черный прямоугольник в правом нижнем углу рамки выделения . Когда указатель мыши примет вид черного крестика, нужно выполнить левое (при нажатой левой кнопке мыши) или правое (при нажатой правой кнопке мыши) перетаскивание содержимого ячейки в любом направлении (влево, вправо, вверх или вниз). Результат левого перетаскивания зависит от содержимого ячейки.
Если перетаскиваемая ячейка содержит текстовое значение, то текст будет копироваться в новые ячейки (рис. 4а). Если ячейка содержит число, то левое перетаскивание позволяет скопировать значения (рис. 4,а), а левое перетаскивание при нажатой клавише Ctrl – проиндексировать их в арифметической прогрессии с разностью, равной единице (рис. 4, б). Для увеличения значений в ячейках нужно перетаскивать маркер заполнения вправо или вниз. Для уменьшения значений – влево или вверх. Арифметическую прогрессию в Excel удобнее задавать двумя первыми значениями, тогда шаг приращения вычисляется автоматически, как разность между этими значениями.
а) б)
Рисунок 4. Результаты копирования содержимого ячеек перетаскиванием левым (а) и правым (б).
Если ячейка содержит числовое значение и текст, то при левом перетаскивании текст будет копироваться, а числовое значение изменяться (рис. 5).
Рисунок 5. Копирование буквенно-цифровой информации.
Для ячейки, содержащей текстовые компоненты даты (название месяца или дня недели) или числовые значения даты и времени, то при левом перетаскивании будет построен соответствующий ряд изменяющихся значений (рис. 6).
Рисунок 6. Заполнение ячеек предопределенной информацией.
Если исходная ячейка содержит формулу, то результат операции автозаполнение будет зависеть от типа ссылок в формуле. Абсолютные адреса ячеек (подробнее информация в теме «Способы адресации ячеек») меняться не будут, в относительных адресах изменятся идентификаторы строк или столбцов, в зависимости от направления заполнения.
При правом перетаскивании после отпускания кнопки, на экран выводится меню, в котором можно выбрать варианты заполнения ячеек:
Последние четыре варианта позволяют выбрать шаг изменения значений для даты, который может быть равен одному дню или одному рабочему дню, одному месяцу и одному году.
Автозавершение используется при заполнении столбцов таблицы одинаковыми текстовыми данными. При заполнении очередной ячейки столбца, программа сравнивает вводимое слово с имеющимися в столбце словами и предлагает вариант его написания, который можно подтвердить нажатием клавиши Enter. Например, если в столбце таблицы написано имя Андрей, то при вводе в очередную ячейку этого столбца буквы А остальные буквы указанного слова будут воспроизведены в ячейке автоматически и ввод можно завершить нажатием клавиши Enter. Если набирается другое слово, предложение программы игнорируют, продолжая набор нужного слова.
Excel формирует из имеющихся в столбце записей список (рис. 7), который можно использовать для заполнения других ячеек данного столбца. Для заполнения ячейки путем выбора из списка нужно щелкнуть правой кнопкой мыши по заполняемой ячейке, в контекстном меню выбрать команду Выбрать из списка..., а затем нужную запись в списке щелчком мыши. Список с возможными вариантами заполнения для выбранной ячейки можно вывести на экран также комбинацией клавиш Alt и ↓.
Рисунок 7. Выбор из списка
Excel может работать с различными данными, тип которых (число, текст, дата, время, логическое значение, формула или функция) определяется автоматически при вводе и может быть переопределен путем форматирования ячеек таблицы (например, для того чтобы Excel воспринимал цифры не как числовые значения, а как текст). Форматирование используется также для визуального оформления ячеек и их содержимого. Форматирование выделенной области электронной таблицы можно выполнить при помощи вкладок диалогового окна Формат ячеек или кнопками панели инструментов Форматирование. Окно (рис. 8) выводится на экран командой Ячейки... из меню Формат или соответствующей командой контекстного меню (вызывается на экран щелчком правой кнопкой мыши по форматируемой ячейке).
Рисунок 8. Содержание меню Формат ячеек.
Вкладка Число позволяет присвоить ячейкам требуемый числовой формат. Вкладка Выравнивание предназначена для изменения ориентации и выравнивания текста в ячейке, а также позволяет установить параметры отображения данных в ячейке, например, объединить ячейки. Используя вкладку Шрифт можно задать тип и стиль шрифта, размер и цвет символов. Обрамление (границы) выделенной ячейки или группы ячеек, например таблицы, может быть задано при помощи вкладки Граница, а вкладка Вид позволяет установить для них цвет фона. Вкладка Защита позволяет разблокировать отдельные ячейки перед установкой защиты на рабочий лист или рабочую книгу. Копировать форматы ячеек можно и с помощью кнопки Формат по образцу на панели инструментов Стандартная.
Объединение используется для получения ячеек с нестандартной высотой (две и более строки) и шириной (два и более столбца). Для объединения выделенных ячеек можно использовать кнопку Объединить и поместить в центре панели инструментов Форматирование или установкой переключателя Объединение ячеек на вкладке Выравнивание диалогового окна Формат ячеек. Отменить объединение в выделенной ячейке можно путем сброса указанного выше переключателя Объединение ячеек или повторным нажатием кнопки .
Линию обрамления выделенной ячейки, внешнюю границу диапазона ячеек, а также границы между ячейками диапазона можно создать средствами вкладки Граница (рис. 9) диалогового окна Формат ячеек. Вкладка Граница содержит меню для выбора типа линии (справа) и макет таблицы (условное изображение выделенного на листе диапазона ячеек в центре вкладки), который служит для конструирования границ. На макете все внутренние горизонтальные и вертикальные линии представлены соответственно одной горизонтальной и одной вертикальной линией. Чтобы, например, провести сразу все горизонтальные линии внутри выделенной на листе области, нужно щелчком левой кнопки мыши выбрать в меню тип линии, а затем щелкнуть мышью в нужном месте внутри макета или по центральной кнопке слева от него. Создать границы можно также при помощи кнопок Границы панели инструментов Форматирование. Правая кнопка обеспечивает доступ к меню границ, левая – вставку выбранной границы в выделенный диапазон ячеек.
Рисунок 9. Установление границ ячеек.
Для создания фона в выделенных ячейках служат средства вкладки Вид диалогового окна Формат ячеек, а также кнопки Цвет заливки панели инструментов Форматирование. Правая кнопка обеспечивает доступ к палитре цветов, левая - заливает выделенный диапазон ячеек выбранным цветом.
При помощи команды Условное форматирование... из меню Формат производят форматирование диапазонов заполненных ячеек таблицы по их содержимому, а также вновь заполняемых ячеек в зависимости от вводимых данных, например, выделять цветом ячейки, значение которых превышает допустимое. Диалоговое окно Условное форматирование (рис. 10) позволяет задать до трех условий форматирования для ячеек выделенной области таблицы. Например, если необходимо выделить цветом в таблице ячейки содержащие различные фамилии нужно в первом списке области Условие1 выбрать «значение», во втором списке условие «равно», в поле ввода ввести первую фамилию, при помощи кнопки Формат... вывести на экран диалоговое окно Формат ячеек и установить там требуемые параметры форматирования. Области для ввода второго и третьего условий выдаются на экран после щелчка по кнопке А также >>.
Рисунок 10. Содержание меню Условное форматирование.
Если ширина ячейки не соответствует ее содержимому (текст или число не помещаются в ячейку), то в ней будет видна только часть текста или последовательность значков # вместо числа. В этом случае ширину ячейки следует увеличить. Высоту ячейки увеличивают для улучшения восприятия содержащейся в ней информации, кроме того, высота ячейки автоматически корректируется при изменении размера шрифта в ней.
Изменение высоты строк и ширины столбцов можно выполнять только после их выделения. Одна строка или один столбец выделяются щелчком левой кнопки мыши по номеру строки или названию столбца соответственно. Смежные строки и столбцы выделяют протягиванием мыши по заголовкам выделяемых строк или столбцов, а несмежные - щелчками мыши по заголовкам выделяемых объектов при нажатой клавише Ctrl. Задать высоту выделенных строк и ширину выделенных столбцов можно в диалоговых окнах, которые выводятся на экран по командам Высота и Ширина из меню Формат-Строка и Формат-Столбец соответственно. Команды Автоподбор высоты и Автоподбор ширины из этих меню автоматически корректируют высоту строки и ширину столбца в соответствии с их содержимым.
Коррекцию можно выполнить также с помощью мыши. Для этого указатель мыши следует установить на границу между номерами выделенных строк или названиями выделенных столбцов. Когда указатель мыши приобретет вид двунаправленной стрелки, нужно нажать левую кнопку мыши и, не отпуская ее, переместить указатель в нужном направлении. При этом контролировать устанавливаемый размер можно визуально по пунктирной линии границы, появляющейся на рабочем поле, и по выводимой на экран подсказке.
Задание 1
Цель. Получение навыков создания, заполнения и форматирования таблиц, редактирования содержимого ячеек, копирования отдельных ячеек и диапазонов ячеек.
Порядок выполнения.
диапазоны ячеек В3:В8, D3:D8 и F3:F8 бледно-зеленым цветом;
диапазоны ячеек С3:С8, Е3:Е8 и G3:G8 голубым цветом;
диапазоны ячеек B5:G5 и B7:G7 светло-желтым цветом.
Вычисления в табличных процессорах выполняются по формулам, с помощью которых можно задать способ получения значений одних переменных по значениям других переменных. Любая ячейка рабочего листа может играть такую же роль, как переменная в математике: она имеет обозначение (имя или адрес) и ей может быть присвоено значение, которое можно менять. В ячейке содержащей формулу хранится результат расчета и его также можно использовать в качестве переменной в других формулах. Ячейки электронной таблицы, которые содержат независимые переменные (значения в них вводятся извне или вычисляются по формуле без ссылок) называются влияющими ячейками. Ячейки, значение которых вычисляется по формулам со ссылками на другие ячейки, называются зависимыми ячейками.
Формула – это выражение, состоящее из операндов и математических операторов. В качестве операндов в формулах обычно используют адреса ячеек (ссылки), числовые значения и функции (Excel имеет готовый набор формул, называемых встроенными функциями).
Операторы в формулах обозначают действия, производимые с операндами. В зависимости от используемых операторов различают арифметические и логические формулы.
В арифметических формулах используются следующие операторы: + (сложение), - (вычитание), * (умножение), / (деление), ^ (возведение в степень). Результатом вычисления арифметической формулы является число.
При вычислениях с помощью формул соблюдается принятый в математике порядок выполнения арифметических операций. Сначала выполняется возведение в степень, затем – умножение и деление и только после этого – вычитание и сложение. Операции одного уровня выполняются слева направо. Формулы могут содержать круглые скобки, тогда операции в них выполняются первыми.
Логические формулы могут содержать операторы сравнения: = (равно), <> (не равно), > (больше), < (меньше), <= (не более), >= (не менее), а также специальные логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ и НЕ.
Логические формулы определяют истинно выражение или ложно. Истинным выражениям присваивается логическое значение ИСТИНА (логическая единица), а ложным - ЛОЖЬ (логический ноль).
Запись формулы в Excel должна начинаться знаком =, который можно ввести с клавиатуры или щелчком мыши по соответствующей кнопке в строке формул. Допускается ввод формулы начинать знаками + или -, в этом случае знак равенства вставляется перед формулой автоматически.
Формула может содержать ссылки на ячейки, расположенные на листах текущей рабочей книги или на листах других рабочих книг. В ссылке адрес листа (имя листа) отделяется от адреса ячейки или диапазона восклицательным знаком, например, Лист5!АЗ, а адрес рабочей книги (имя книги) записывают перед именем листа в квадратных скобках, например, [Книга2]Лист5!АЗ.
Ссылка в формулу может быть введена путем набора адреса ячейки на клавиатуре (необходимо помнить, что буквы в адресе ячейки должны быть английскими), либо щелчком мыши по нужной ячейке. В последнем случае адрес выбранной ячейки вводится в формулу автоматически. При вводе ссылок на ячейки расположенные на другом листе можно перейти на него, используя ярлык листа, а затем щелкнуть мышью по ячейке (если вводится ссылка на отдельную ячейку) или выделить нужный диапазон. В ссылку на другой лист автоматически будет вставлен его адрес.
Формулы табличных процессоров могут содержать относительные, абсолютные и смешанные ссылки. Тип используемых в формуле ссылок определяет, как будет модифицироваться формула при изменении ее местоположения в таблице.
Формулы можно копировать и перемещать в другие ячейки. При перемещении формулы ссылки, расположенные внутри формулы, не изменяются. При копировании формулы абсолютные ссылки не изменяются, а относительные или смешанные ссылки автоматически модифицируются таким образом, чтобы сохранялось относительное расположение ячейки содержащей формулу и ячеек с входящими в нее операндами. По умолчанию, при вводе адреса ячейки в формулу вводится относительная ссылка на эту ячейку. Например копируется формула =В1+С6 с относительными ссылками из ячейки СЗ в ячейку F5. После копирования формула принимает вид =E3+F8, поскольку ячейки ЕЗ и F8 расположены относительно ячейки F5, как ячейки В1 и С6 относительно ячейки СЗ.
Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, относительные ссылки заменяют абсолютными или смешанными ссылками. Для создания абсолютной ссылки на ячейку нужно поставить знак доллара $ перед идентификатором строки и номером столбца в адресе ячейки, например, $С$7. При копировании такой формулы, как по строкам, так и по столбцам, ссылка на ячейку не будет меняться. Поставив знак доллара только перед одной частью адреса можно зафиксировать либо строку, либо столбец в адресе ячейки (например, С$7, $С7), такие ссылки называются смешанными.
Например, копируется формула =B$1+C6 со смешанной и относительной ссылками в ячейку F5. В отличие от предыдущего примера, ссылка на ячейку B1 содержит знак $ перед единицей. Поэтому при копировании в ячейку F5 имя столбца в этой ссылке изменится с В на Е, а номер строки останется прежним и формула примет следующий вид: =E$1+F8.
Для создания абсолютных и смешанных ссылок можно использовать клавишу F4. Если поставить курсор ввода у адреса ячейки и нажать один раз F4, то будет зафиксирована вся ячейка ($D$2 - абсолютная ссылка), два раза -только номер строки (D$2 - абсолютная ссылка по строке), три раза - только название столбца ($D2 - абсолютная ссылка по столбцу). При четвертом нажатии на F4 ссылка вновь становится относительной (D2).
Фиксировать адреса ячеек или диапазонов можно, присвоив им имена (имя является средством абсолютной адресации ячейки). Для этого именуемый объект выделяют и либо заменяют ссылку на имя в левой части строки формул, либо вводят имя в одноименное поле диалогового окна, которое выводится на экран командой Имя-Присвоить из меню Вставка. Имена ячеек удобно использовать в формулах, они позволяют лучше ориентироваться в представленных на листах данных и быстрей находить нужные ячейки (команда Правка-Перейти). Именованные объекты можно перемещать в пределах рабочей книги, при этом они сохраняют свои свойства, так как имя связывается не с расположением объекта, а с его содержимым.
Excel имеет более восьмисот встроенных функций – специальных, заранее созданных формул, имеющих один или несколько аргументов. Запись каждой из функций начинается знаком = и состоит из имени и списка аргументов, заключенного в скобки (например =СРЗНАЧ(А1:А4)). Аргументы в списке отделяются друг от друга точкой с запятой. Тип аргумента зависит от функции. Аргументами могут быть ссылки, числа, текст, логические величины, массивы, формулы (по формуле, например, может проверяться условие в логической функции ЕСЛИ), функции и т.д.
Для ввода функций и задания их аргументов в Excel обычно используется Мастер функций, который хранит правила составления всех функций и обеспечивает их правильный ввод.
Функция может быть вставлена в ячейку непосредственно, либо в составе формулы или другой функции. Перед запуском Мастера функций необходимо указать место вставки функции. Причем, если функция вставляется в пустую ячейку, то перед вставкой можно просто выделить ячейку. Если функция вставляется в имеющуюся в ячейке формулу или функцию, то после выбора ячейки следует указать место вставки курсором в строке формул (или в ячейке, при ее выделении двойным щелчком мыши). Мастер функций обычно активизируют командой Вставка-Функция или кнопкой на стандартной панели инструментов. Его можно также вызвать, путем выбора функции из списка функций в левой части строки формул.
После запуска Мастера функций на экран выводится диалоговое окно с двумя полями (рис. 11). Для быстрого поиска нужной функции все встроенные функции разделены по типам или категориям, список которых содержится в поле Категория диалогового окна. В поле Выберите функцию выводится список функций выбранной категории.
Рисунок 11. Выбор вида функции с помощью мастера функций.
При вводе функции сначала выбирают ее тип в поле Категория (например, математические, статистические, логические, текстовые и т.д.), а затем в поле Выберите функцию выбирают имя конкретной функции данного типа (например, ПРОИЗВЕД, СУММ, СРЗНАЧ, ЕСЛИ, и т.д.). После подтверждения выбора (кнопка ОК) на экран выводится окно для указания аргументов функции (рис. 12), содержащее для этого специальные строки с кнопками сворачивания окна. Названия строк для ввода обязательных параметров выделены в этом окне жирным шрифтом. В нижней части окна приводится краткое описание выбранной функции и формат ее записи, в котором список аргументов может заканчиваться многоточием, если функция использует несколько однотипных аргументов.
Рисунок 12. Расчет среднего значения данных с помощью мастера функций.
Однотипные аргументы можно вводить вручную с клавиатуры в любую строку ввода в виде последовательности значений, ссылок на ячейки и диапазоны ячеек (через точку с запятой), такие аргументы можно также распределить по нескольким строкам ввода. Удобно вводить аргументы выделением области рабочего листа, содержащей эти аргументы. Чтобы окно мастера не закрывало область выделения его можно свернуть в строку (щелчком по кнопке в правой части строки ввода) или переместить в сторону (нужно нажать левую кнопку мыши в любом свободном месте окна и, не отпуская ее, передвинуть мышь в нужном направлении). Адрес выделенного диапазона автоматически вставляется в строку ввода окна. Свернутое окно затем можно восстановить повторным щелчком по кнопке в этой строке.
Ссылки на отдельные ячейки можно вводить, щелкая мышью по нужным ячейкам при нажатой клавише Ctrl, аналогично можно вводить ссылки на несвязанные диапазоны.
Чтобы вставить функцию в поле аргумента другой функции (вызвать Мастер функций из Мастера функций) нужно, установить курсор в это поле, шелкнуть мышью по кнопке в строке формул, а затем выбрать требуемую функцию в списке этой строки.
Суммирование является самой распространенной математической операцией. Поэтому, для удобства пользователей, кнопка для вставки этой функции вынесена на панель инструментов Стандартная. Чтобы вставить функцию суммирования в ячейку достаточно выделить ее и щелкнуть мышью по кнопке Автосумма . В ячейке появится знак равенства, имя функции (СУММ) и адрес диапазона суммирования (в скобках), который программа предлагает после анализа близлежащих ячеек. Если диапазон определен правильно, далее нужно нажать клавишу Enter, в противном случае – выделить нужный диапазон (диапазоны) ячеек (несмежные диапазоны выделяются при нажатой клавише Ctrl), а затем нажать клавишу Enter.
Формулу можно редактировать в содержащей ее ячейке и в строке формул. По умолчанию, в ячейке с формулой отображается результат вычисления. После выделения ячейки записанная в нее формула выводится в строке формул, а чтобы отобразить формулу в ячейке нужно дважды щелкнуть по этой ячейке мышью или нажать клавишу F2. Установив курсор ввода в нужное место формулы, можно править ее обычными средствами (используя клавиатуру, команды меню и кнопки на панелях инструментов). Изменения фиксируются нажатием кнопки в строке формул или клавиши Enter.
В отличие от формул, функции можно редактировать также в Мастере функций. Для этого нужно сначала щелкнуть мышью по ячейке, где находится функция, а затем вывести на экран окно Мастера функций, например щелчком мыши по пиктограмме на панели инструментов Стандартная. В этом окне функцию редактируют, изменяя содержимое строк ввода.
Логические функции представляют собой особую категорию функций, в частности они позволяют производить определенные действия над операндами, при выполнении или невыполнении заданного условия.
С помощью формул и функций в Excel можно задать (запрограммировать) и автоматически выполнить практически любую последовательность операций над данными. Для программной обработки информации большое значение имеют условные команды, с их помощью организуют циклы и ветвления в программах. Среди логических функций Excel имеется функция ЕСЛИ, которую можно использовать в указанных целях. Эта функция имеет следующий формат:
ЕСЛИ (<логическое выражение>;<выражение1>;< выражение2>).
Первый аргумент функции – логическое выражение (в частном случае, условное выражение), которое принимает одно из двух значений: Истина или Ложь. В первом случае функция ЕСЛИ принимает значение выражения1, а во втором – значение выражения2. В качестве выражения1 и выражения2 можно использовать формулы, функции, ссылки и константы. Например, функция, сравнивающая две ячейки (В1 и В2) и выводящая в результате сравнения на экран сообщение «больше» или «меньше», имеет вид: =ЕСЛИ(В1>В2;"больше";"меньше").
Примечание представляет собой какие-либо замечания, относящиеся к ячейке и хранящиеся независимо от содержимого этой ячейки. Примечания удобны для напоминания о чем-либо, например, о принципе работы сложной формулы.
Для ввода примечания необходимо выбрать Вставка/Примечание или вызвать правой клавишей мыши контекстное меню и выбрать функцию Добавить примечание. В появившееся окно вставить текст примечания (рис. 13). Ячейки, содержащие примечание имеют индикатор – красный треугольник в правом верхнем углу ячейки. Изменить формат шрифта, цвет заливки, цвет, толщину или фигуру рамки примечания можно вызвав функцию Формат/Формат примечания. Чтобы показать или скрыть примечание, выберите команду Отобразить примечание или Скрыть примечание.
Рисунок 13. Вставка примечания.
Чтобы скрыть в книге примечания и (или) их индикаторы, необходимо в меню Сервис / Параметры открыть вкладку Вид и в группе Примечание выбрать вариант не отображать; только индикатор; примечание и индикатор.
Ответы на все вопросы, касающиеся работы табличного процессора можно найти в справочной системе Microsoft Excel. Запуск справки осуществляется клавишей F1 или функцией справка Microsoft Excel меню справка. При этом появляется помощник, выводящий окно для ввода вопроса или открывается дополнительное окно справки.
Рисунок 14. Пиктографическое меню справки Microsoft Excel.
В окне справки находится пиктографическое меню, содержащее шесть клавиш: автомозаика, показать/скрыть, назад, вперед, печать, параметры (рис. 14).
Клавиша показать/скрыть открывает меню справки. Поиск можно осуществлять тремя методами: по указателю, по оглавлению и с помощью мастера ответов. В случае поиска по содержанию в левой части окна отражается структура содержания, а в правой содержание выбранного раздела. Мастер ответов и указатель требуют ввода ключевых фраз или слов соответственно, в правой части окна отражается ответ на заданное условие.
Задание 2
Цель. Получение навыков ввода, копирования и редактирования формул и функций, а также использования относительных и абсолютных ссылок.
Порядок выполнения.
Графическое представления данных - это важный этап обработки и анализа информации, а также оформления документов. Диаграммы и графики позволяют повысить наглядность представления табличных данных определить связи между ними, соотношение различных значений и тенденции изменения. Данные в графическом виде удобнее для восприятия и анализа, чем массивы соответствующих чисел, что позволяет принимать более быстрые и обоснованные решения.
Диаграмма - это условное графическое изображение числовых величин или их соотношений. Диаграммы используются, например, для того чтобы показать значения какой либо величины в различные моменты времени или ее изменение с течением времени. График, в отличие от диаграммы, изображает зависимость одной величины от другой.
Для выделенной области данных в Excel диаграмму можно создать автоматически за один шаг при помощи функциональной клавиши F11. При нажатии на эту клавишу создается обычная плоская гистограмма на отдельном листе. Но обычно для представления в графической форме табличных данных используют программный модуль - Мастер диаграмм. Этот модуль можно активизировать командой Диаграмма из меню Вставка или кнопкой Мастер диаграмм на панели инструментов Стандартная.
Мастер диаграмм строит диаграмму за четыре шага:
На первом шаге (рис. 15) выбирается тип диаграммы. Разные диаграммы по-разному представляют одни и те же данные, поэтому выбор правильного типа сказывается на ясности и информативности отображения данных. Например, с помощью гистограммы можно показать изменение данных за определенный период времени и соотношение отдельных значений. Мастер диаграмм позволяет строить плоские и объемные диаграммы (14 стандартных типов и 22 нестандартного типа).
Рисунок 15. Первый шаг. Выбор типа диаграммы
На втором шаге (рис. 16) нужно задать диапазон данных, для которого будет построена диаграмма. Кроме числовых данных диапазон может включать их названия (названия столбцов и строк таблицы), которые используются в качестве меток на оси Х и для расшифровки условных обозначений в легендах. При этом не обязательно вручную вводить адреса нужных ячеек в поле Диапазон. Для автоматического ввода указанных адресов следует выделить нужные ячейки на рабочем листе мышкой (несмежные диапазоны данных нужно выделять при нажатой клавише Ctrl).
Если окно Мастера диаграмм перекрывает область выделения, то его можно либо переместить в сторону (перетащить мышкой за строку с названием окна), либо свернуть при помощи кнопки в поле Диапазон. Диаграмма может быть построена по строкам или по столбцам выделенного диапазона, указанная возможность реализуется переключателем Ряды второго окна Мастера диаграмм. Результат можно оценить по примеру диаграммы, отображаемому в этом же окне.
Рисунок 16. Второй шаг. Ввод области данных.
На третьем шаге (рис. 17) выбираются различные параметры оформления диаграммы, например, название диаграмм и её осей, подписи данных на диаграмме, расположение легенды и т.д. Результаты производимых настроек на этом шаге также можно контролировать по примеру диаграммы в окне.
Рисунок 17. Третий шаг. Оформление диаграммы.
Рисунок 18. Четвертый шаг. Размещение диаграммы.
На четвертом шаге (рис. 18) определяется место размещения диаграммы (на имеющемся или отдельном листе). В списке этого окна можно выбрать любой из имеющихся в рабочей книге листов для размещения диаграммы или создать новый лист, отметив флажком функцию «на отдельном» и введя новое имя в строку, размещенную рядом с флажком.
Диаграмма связана с данными, на основе которых она создана, и обновляется автоматически при изменении данных
Размещение, масштаб и параметры форматирования диаграмм построенных Мастером диаграмм обычно требуют корректировки, которую выполняют вручную.
Редактирование используется для изменения размера, типа, параметров и исходных данных диаграммы, а также места ее расположения.
Расположение выделенной диаграммы можно изменить перетаскиванием мышью, а её размеры - путем перемещения маркеров выделения. Диаграмму выделяют щелчком мыши по области диаграммы, рядом с внешней границей, а маркеры выделения - это черные квадратики, которые появляются на этой границе у выделенной диаграммы.
Редактирование можно выполнить командами меню Диаграммы, которое появляется в главном меню после выделения диаграммы, а также с помощью контекстных меню. На рисунке 19 представлены контекстные меню области диаграммы (верхнее меню) или области построения диаграммы (нижнее меню). Жирными точками на диаграмме показаны места, щелкнув по которым правой кнопкой мыши можно вывести на экран указанные меню. Четыре верхних пункта меню, отмеченные жирной линией, обеспечивают возврат к любому из четырех этапов создания диаграммы.
Рисунок 19. Редактирование диаграммы.
После редактирования диаграммы можно выполнить форматирование объектов и элементов диаграммы. К объектам диаграммы можно отнести область диаграммы, область построения диаграммы, области с названиями диаграммы и ее осей, а также легенду. Элементы - это отдельные части объектов. Любой выделенный объект диаграммы можно перемещать мышью внутри области диаграммы. Размеры области построения диаграммы и легенды изменяют путем перетаскивания мышью маркеров выделения.
Областью диаграммы называется прямоугольное поле, на котором располагаются остальные объекты диаграммы. Область построения - это прямоугольное поле, на котором в графическом виде отображаются числовые данные из электронной таблицы. Легендой называется специальное окно со списком условных обозначений переменных или рядов данных из таблицы, представленных на диаграмме (каждый ряд данных выделяется на диаграмме индивидуальным цветом или штриховкой).
Форматирование позволяет изменить вид объектов и элементов диаграммы (цвет фона, способ заливки, параметры шрифта, параметры линий и т.д.).
Форматировать объект, отдельный элемент или ряд элементов диаграммы можно средствами диалоговых окон форматирования для различных частей диаграммы. Нужное окно форматирования можно вывести на экран:
Задание 3
Цель. Получение навыков создания и редактирования графиков и диаграмм.
Порядок выполнения.
Преступления в РФ
|
1995г. |
1996г. |
1997г. |
1998г. |
1999г. |
2000г. |
2001г. |
2002г. |
2003г. |
2004г. |
|
|
Всего |
2167964 |
2760652 |
2799614 |
2632708 |
2755669 |
2625081 |
2397311 |
2581940 |
3001748 |
2952367 |
|
Краж |
1222529 |
1630060 |
1579600 |
1314788 |
1367866 |
1207478 |
1053972 |
1143364 |
1413810 |
1310079 |
8. Построить ниже таблицы в диапазоне В6:М22 график с маркерами отражающий динамику преступлений в РФ с 1995 по 2004 годы. Для диаграммы и оси Y использовать соответственно следующие заголовки «Зарегистрировано преступлений в РФ» и «Количество». Для области диаграммы задать шрифт размером 8 пунктов. Затем изменить форматирование заголовка диаграммы (установить шрифт размером 12 пунктов голубого цвета), заголовка и подписей оси Y (установить шрифт красного цвета). Подписи оси X повернуть на угол 90 градусов. Область диаграммы залить светло-желтым цветом, для заливки области построения диаграммы использовать текстуру «Водяные капли». Установить размер маркеров на графике - 8 пунктов, для верхнего и нижнего рядов графика использовать тонкие линии, соответственно синего и фиолетового цвета.
9.Сохранить рабочую книгу в своей папке под именем ЗаданиеЗ.
Одной из задач, решаемых с помощью электронных таблиц, является создание и ведение списков. Списками в табличных процессорах называются простейшие базы данных, состоящие из строк - записей и столбцов - полей. Классическими примерами списков являются телефонные книги и списки адресов. Для работы со списками в Excel служат специальные функции и средства - сортировка, фильтрация, итоги и сводные таблицы.
При просмотре данных таблиц, содержащих большое количество строк и (или) столбцов, заголовки таблиц и сами данные могут не помещаться в область экрана. Для устранения этого недостатка области таблицы следует закрепить, что позволяет при просмотре областей списка одновременно видеть на экране часть его заголовка и расположенные слева столбцы. С целью закрепления областей вызовите команду Окно/Закрепить области. При этом закрепляются ячейки выше и левее ячейки, в которой стояла курсорная рамка. Пример одновременного отображения различных областей таблицы приведен на рисунке 20. Курсорная рамка стояла в ячейке D1.
Для отмены закрепления областей следует выполнить команду Окно/Снять закрепление областей.
Рисунок 20. Лист с закрепленными областями
Сортировка позволяет представить списочные данные в требуемой последовательности для облегчения поиска и анализа информации. Текстовые данные можно располагать в алфавитном или в обратном алфавитном порядке, а числовые в порядке возрастания или убывания. Одновременно можно отсортировать данные по трем различным полям списка.
Для сортировки данных следует установить курсорную рамку в любую ячейку сортируемого поля таблицы и выполнить команду Данные - Сортировка. При этом в строке ввода области Сортировать по появившегося диалогового окна будет вставлено имя сортируемого поля таблицы (другое поле можно выбрать в списке, который открывается здесь же кнопкой ). Тип сортировки задается переключателями области Сортировать по. Строки ввода областей Затем по и В последнюю очередь по служат для указания полей сортируемых во вторую и в третью очередь.
Для сортировки данных в выделенном поле можно также использовать кнопки панели инструментов Стандартная.
Excel позволяет сортировать данные не только во всей таблице, но и в выделенном диапазоне смежных ячеек, например, в выделенных строках или в выбранной части поля.
Фильтрация используется для быстрого поиска информации в больших списках, фильтр позволяет выводить на просмотр только информацию, отмечающую установленному условию (условиям).
Фильтр можно установить командами Автофильтр и Расширенный фильтр из меню Данные – Фильтр (рис. 21), кроме того, критерии фильтрации могут быть заданы в специальной экранной форме, предназначенной для работы с отдельными записями списка (форма для списка выводится на экран по команде Данные - Форма).
Рисунок 21. Выбор условия для фильтрации.
Для установки автофильтра нужно поместить курсорную рамку внутри списка и выполнить команду Данные – Фильтр - Автофильтр (повторный выбор этой команды отменяет автофильтр). После этого для каждого столбца будет построен список возможных критериев вывода информации и в заголовках каждого столбца появятся кнопки автофильтра , с помощью которых эти списки открываются. Каждый из указанных списков содержит перечень неповторяющихся значений поля (для фильтрации по содержимому поля) и следующие команды:
Все - вывод всех записей. Используется для отмены условий автофильтра в выбранном поле;
Первые десять... - вывод на экран диалогового окна Наложение условия по списку, при помощи которого можно выбрать произвольное количество записей с наибольшими или наименьшими значениями выбранного поля;
Условие... - вывод на экран диалогового окна Пользовательский автофильтр (рис. 22) для задания сложных условий выборки с использованием 12 различных операторов («больше», «меньше или равно», «начинается с», «содержит» и т.д.). Пользовательский автофильтр позволяет установить один или два критерия выборки объединенных логическими операциями И или ИЛИ.
Рисунок 22. Пользовательский автофильтр.
С помощью автофильтра можно задать условия выборки для нескольких полей списка, в этом случае фильтрация производится по объединенному критерию (условия соединяются оператором И), т.е. выводимая информация будет удовлетворять всем установленным условиям.
Расширенный фильтр позволяет устанавливать более сложные критерии фильтрации, например, задавать условия, соединенные оператором ИЛИ, сразу для нескольких полей, более двух условий для одного поля и вычисляемые условия.
Для команды Расширенный фильтр условия отбора записей (строк) из списка задаются в отдельном диапазоне рабочего листа. Диапазон условий может состоять минимум из двух строк (рис. 23). Первая строка должна содержать имена полей списка, для которых задаются условия, а вторая и последующие - сами условия отбора. При отборе записей для отдельных условных строк строятся результирующие списки, для каждого из которых одновременно выполняются все условия в соответствующей условной строке.
Рисунок 23. Расширенный автофильтр.
Чтобы снова вывести все записи следует в меню Данные - Фильтр выбрать пункт Отобразить все.
При просмотре, изменении, добавлении и удалении записей в списке данных, а также при поиске записей по определенному критерию удобно использовать форму данных, которая позволяет выводить на экран и корректировать любую запись списка. При модификации полей записи в форме, изменяется содержимое соответствующих ячеек списка. Форму для работы со списком можно сформировать командой Данные – Форма, предварительно установив курсорную рамку на любую ячейку списка. После выбора этой команды в меню на экран выводится окно формы (рис. 24), в котором поля списка представлены одноименными строками с атрибутами одной записи. Для перехода к другим записям списка в форме используются кнопки перехода и полоса прокрутки, а для перемещения по полям формы - мышь и клавиши Tab (вниз), Shift+Tab (вверх).
Рисунок 24. Форма данных.
Работа со списком выполняется с помощью следующих кнопок формы:
Добавить - используется для очищения полей формы перед вводом новой записи и добавления новой записи в конец списка (после первого щелчка по этой кнопке производят ввод данных в очищенные поля формы, после второго - введенная запись добавляется в конец списка).
Удалить - удаляет отображаемую в форме запись (при этом остальные записи списка сдвигаются, заполняя освободившееся место).
Вернуть - отменяет все изменения, произведенные в отображаемой записи до завершения редактирования, т.е. перед нажатием клавиши Enter.
Назад - выводит предыдущую запись списка, а если задан критерий отбора (кнопка Критерии), то - предыдущую запись из тех, которые удовлетворяют заданному критерию.
Далее - выводит следующую запись списка или следующую запись, удовлетворяющую заданному критерию отбора (кнопка Критерии).
Критерии - очищает поля формы и переводит ее в режим ввода критериев отбора записей.
Правка - служит для выхода из режима ввода критериев, эта кнопка появляется после нажатия кнопки Критерии.
Очистить - удаляет существующие критерии, эта кнопка появляется после нажатия кнопки Критерии.
Закрыть - закрывает форму данных.
Критериями отбора для текстовых полей формы могут быть символьные константы, например, имя Иван для поля ИМЯ или название города для поля ГОРОД. В Excel критерий отбора для текстового поля можно вводить не только целым словом, но и частью слова с символами подстановки (* - заменяет любое число символов в слове, а ? - один символ в слове). Если поиск проводится по первым буквам слова, символы подстановки можно не использовать. Например, одна буква, введенная в качестве критерия в текстовое поле формы, рассматривается как начальная буква слова в соответствующем поле (без учета регистра).
При установке критериев для числовых полей можно использовать соответствующие константы (например, числа или даты) и выражения со следующими операторами сравнения:
= - равно;
<> - не равно;
< - меньше;
> - больше;
<= - меньше или равно;
>= - больше или равно.
В Excel имеется несколько возможностей для вычисления итогов. Например, для выделенного диапазона ячеек производится автоматическое вычисление итогов в строке состояния. По умолчанию в области автоитогов строки состояния формируется сумма значений в выделенных ячейках, но итоговую функцию можно изменить путем выбора в контекстном меню (рис. 25), которое выводится на экран щелчком правой кнопки мыши в области автоитогов (на рисунке это область с суммой выделенных ячеек).
При анализе списков вычисление итогов можно производить путем записи соответствующих функций в итоговые ячейки, но удобнее вычислять итоги путем построения сводных таблиц или с помощью команды Данные-Итоги. В первом случае подготовительная обработка списка не требуется, во втором - список предварительно нужно отсортировать по полю, которое будет использовано для группировки записей списка.
Команда Данные-Итоги вставляет в список строки промежуточных (для групп) и общих (для всего списка) итогов, причем для подведения итогов можно использовать различные итоговые функции:
Сумма - суммирует числа в группе и в столбце;
Кол-во значений - определяет количество непустых ячеек в группе;
Среднее - вычисляет среднее арифметическое значение группы;
Максимум - определяет наибольшее значение в группе и в столбце;
Минимум - определяет наименьшее значение в группе и в столбце;
Произведение - вычисляет произведение всех значений в группе и в столбце;
Количество чисел - вычисляет количество ячеек с числами в группе ив столбце;
Смещенное отклонение - формирует смещенную оценку среднего квадратического отклонения генеральной совокупности по выборке данных;
Несмещенное отклонение - формирует несмещенную оценку среднего квадратического отклонения генеральной совокупности по выборке данных;
Смещенная дисперсия - формирует смещенную оценку дисперсии генеральной совокупности по выборке данных;
Несмещенная дисперсия - формирует несмещенную оценку дисперсии генеральной совокупности по выборке данных.
Для подведения итогов по группам список сортируют с помощью команды Данные-Сортировка, а затем в меню Данные выбирают команду Итоги. В результате на экран будет выведено диалоговое окно Промежуточные итоги (рис. 26). В списке При каждом изменении в этого окна следует выбрать имя поля по которому нужно сгруппировать данные (на рисунке это поле Районы), затем в списке Операция выбрать функцию, которая будет использована для вычисления итогов в каждой группе. Поля списка, по которым указываются в списке Добавить итоги по. Последовательно подавая команду Данные-Итоги, можно сформировать (используя разные итоговые функции) и отобразить в списке одновременно несколько различных итогов. Для добавления новых итогов к имеющимся нужно в диалоговом окне Промежуточные итоги сбросить переключатель Заменить текущие итоги.
Средством для работы со списком данных является сводная таблица (таблица глобальных итогов). Сводная таблица - это специальный тип итоговой таблицы, при создании которой можно задавать ее организацию, выбирать поля для вычисления итогов и тип итоговых функций. После создания сводной таблицы ее можно модифицировать для удобства анализа данных, например, транспонировать заголовки столбцов в заголовки строк и наоборот.
Сводная таблица для списка данных формируется с помощью Мастера сводных таблиц, который запускается командой Сводная таблица из меню Данные. При создании сводной таблицы для списка Excel, перед запуском Мастера сводных таблиц следует выделить любую ячейку этого списка (одну).
Мастер сводных таблиц создает таблицу за четыре шага:
На первом шаге, после запуска Мастера сводных таблиц на экран выводится следующее диалоговое окно (рис. 27).
Рисунок 27. Создание сводной таблицы. Задание типа источника данных.
Если сводная таблица создается для текущего списка Excel, то установки данного окна менять не нужно и следует сразу перейти ко второму шагу, щелкнув мышью по кнопке Далее.
Рисунок 28. Указание местонахождения исходных данных.
На втором шаге нужно указать, где расположены исходные данные для сводной таблицы. Если источником данных является список Excel, то на этом шаге на экран выводится диалоговое окно, представленное на рисунке 28. Причем в этом окне уже будет указан адрес списка данных, если перед запуском мастера была выделена одна ячейка этого списка. Для указания другого диапазона нужно щелкнуть мышью по кнопке в строке Диапазон (при этом окно свернется в строку) и выделить требуемый диапазон, обязательно включив в него заголовки полей списка. Для перехода к следующему шагу создания сводной таблицы нужно восстановить окно, повторно щелкнув мышью по кнопке в строке Диапазон, и нажать в нем кнопку Далее.
Рисунок 29. Задание макета таблицы и выбор итоговой функции
На третьем шаге на экран выводится диалоговое окно для задания макета сводной таблицы (работа с макетом выводится на экран кнопкой Макет). Условное изображение макета сводной таблицы (рис. 29) расположено в центре этого окна и содержит три основных области Столбец, Строка и Данные. Область Страница используется для создания многомерной сводной таблицы, все страницы которой размещаются на одном листе. Справа от макета таблицы расположены кнопки с названиями полей списка данных (диапазон данных с этими полями был задан на втором шаге). Кнопки используются для формирования структуры сводной таблицы путем их перетаскивания мышью в различные области макета таблицы (в одну область можно перетащить сразу несколько кнопок). По умолчанию Мастер сводных таблиц суммирует числовые значения в области данных, поэтому там отображается надпись «Сумма по полю Всего» (для нечисловых значений мастер вычисляет их количество). Для смены итоговой функции нужно дважды щелкнуть мышью по новому имени поля в области данных, в появившемся на экране окне (рис. 30) выбрать необходимую операцию (на рисунке выбрана операция Среднее) и нажать на кнопку ОК.
Для перехода к последнему шагу, после формирования структуры сводной таблицы, нужно в окне с макетом таблицы нажать кнопку Далее.
На четвертом шаге работы Мастера сводных таблиц на экран выводится окно (рис. 31), в котором нужно указать место размещения сводной таблицы. Предпочтительным вариантом является выбор нового листа для сводной таблицы.
Рисунок 30. Смена итоговой функции.
Рисунок 31. Указание места для размещения таблицы.
Чтобы поместить сводную таблицу на существующий лист в последнем окне Мастера сводных таблиц, кроме установки соответствующего переключателя, требуется указать диапазон для размещения таблицы в строке ввода.
Результат работы Мастера сводных таблиц (первый вариант сводной таблицы) выводится на экран после нажатия кнопки Готово. Пример сводной таблицы представлен на рис. 32.
Рисунок 32. Пример сводной таблицы.
Далее сводную таблицу можно модифицировать путем перетаскивания полей из одних областей в другие при помощи мыши. Таким же способом можно изменять порядок следования и группировку полей в области строк или столбцов.
Реорганизация сводной таблицы выполняется для упрощения работы с данными, для получения более детальной информации или конкретного среза данных для просмотра и анализа. Для фильтрации информации удобно использовать значения полей в области страниц. Например, если в таблице на рисунке 32 перетащить поле район в область страниц (левый верхний угол листа), то сводная таблица примет вид, показанный на рисунке 33. В такой компактной таблице можно просматривать данные списка по отдельным районам города (верхнее ниспадающее меню, открывается кнопкой ).
Рисунок 33. Реорганизованная сводная таблица.
Консолидация - это получение итоговой информации путем объединения данных из нескольких исходных таблиц в одной итоговой таблице. Консолидированная таблица создается путем определенной функциональной обработки данных из исходных таблиц, например, путем их суммирования. Консолидация используется, например, для подведения итогов деятельности организации за определенный период.
Таблицы с исходными данными для консолидации могут находиться на одном листе рабочей книги, на различных листах или в разных рабочих книгах (одновременно можно консолидировать до 255 таблиц).
Наиболее распространены следующие способы консолидации данных:
с помощью формул со ссылками на исходные диапазоны;
по расположению (для данных одинаковым расположением и структурой);
• по категориям (для одинаковых данных, отличающихся по своей структуре).
При консолидации данных с помощью формул исходные диапазоны указываются в ячейках с формулами консолидированной таблицы в виде ссылок. При этом исходные диапазоны могут быть расположены произвольно, например, в разных местах и на разных листах нескольких рабочих книг.
При консолидации по расположению, однотипные данные во всех исходных диапазонах должны иметь одинаковую структуру. Для консолидации таких данных курсор устанавливается в левый верхний угол области данных результирующей таблицы и выполняется команда Данные - Консолидация. В появившемся на экране диалоговом окне (рис. 34) сначала нужно выбрать функцию консолидации в списке Функция (на рисунке выбрана функция Сумма), а затем указать, какие данные нужно консолидировать. Для этого следует щелкнуть мышью по кнопке в строке Ссылка (при этом окно сожмется до размеров строки), а затем по ярлычку листа с данными и выделить нужный диапазон ячеек. После этого следует восстановить диалоговое окно (щелкнуть мышью по кнопке в строке Ссылка) и добавить выделенный диапазон в Список диапазонов, щелкнув мышью по кнопке Добавить. Другие диапазоны добавляются в Список диапазонов таким же способом. Переключателем Создавать связи с исходными данными можно установить динамическую связь результирующей таблицы с исходными данными, при изменении которых будут автоматически обновляться результаты консолидации. Для завершения формирования результирующей консолидированной таблицы в диалоговом окне нужно щелкнуть по кнопке ОК.
Рисунок 34. Диалоговое окно Консолидация данных.
При консолидации по категориям области-источники содержат однотипные данные с разной структурой. Например, на одном листе рабочей книги имеется таблица заработной платы сотрудников отдела (рис. 35а) до его расширения (3 человека), а на другом листе - аналогичная таблица без премии (рис. 35б) после приема на работу еще двух человек.
|
ФИО |
Зарплата |
Налог |
Премия |
К выдаче |
ФИО |
Зарплата |
Налог |
К выдаче |
|
|
Курочкин |
3000 |
390 |
1000 |
3610 |
Курочкин |
3000 |
390 |
2610 |
|
|
Уточкин |
4000 |
520 |
1000 |
4480 |
Уточкин |
4000 |
520 |
3480 |
|
|
Гуськов |
5000 |
650 |
1000 |
5350 |
Гуськов |
5000 |
650 |
4350 |
|
|
Петушков |
2000 |
260 |
1740 |
||||||
|
Цыплаков |
1500 |
195 |
1305 |
Рисунок 35. Исходные таблицы для консолидации.
|
ФИО |
Зарплата |
Налог |
Премия |
К выдаче |
|
Цыплаков |
1500 |
195 |
1305 |
|
|
Уточкин |
8000 |
1040 |
1000 |
7960 |
|
Петушков |
2000 |
260 |
1740 |
|
|
Курочкин |
6000 |
780 |
1000 |
6220 |
|
Гуськов |
10000 |
1300 |
1000 |
9700 |
|
Рисунок 36. Результат консолидации. |
Таблицы имеют однотипные данные, но разное число строк и столбцов. Для консолидации таких данных по категориям, в отличие от консолидации по расположению, при выделении исходных данных нужно выделять также заголовки строк и столбцов с этими данными, а в области Использовать в качестве имен диалогового окна Консолидация (рис. 34) установить оба переключателя. Таблица консолидации данных приведена на рисунке 36. При этом ячейки для заполнения которых не хватает данных остаются не заполненными.
Выводимые на печать отчеты часто требуется снабдить дополнительной информацией, например, пронумеровать страницы, указать дату вывода документа и имя его автора. Такую информацию можно внести в колонтитулы. Управление их содержимым и параметрами осуществляется при помощи вкладки Вид/Колонтитулы (рис. 37).
По умолчанию отведенные для колонтитулов поля являются пустыми. Однако Excel предоставляет в распоряжение пользователя большой набор встроенных колонтитулов, перечни которых находятся в полях Верхний колонтитул и Нижний колонтитул. Вкладка Колонтитулы имеет специальные поля, позволяющие увидеть, как будет выглядеть колонтитул после выполнения установок.
Рисунок 37. Диалоговое окно Параметры страницы, вкладка Колонтитулы.
Рисунок 38. Диалоговое окно Bерхний колонтитул
Окно Верхний колонтитул (рис. 38) состоит из трех полей, предназначенных для ввода текста колонтитулов: Слева, В центре и Справа. Данные поля обеспечивают выравнивание текста, соответствующее их названию. В средней части диалогового окна располагаются кнопки, предназначенные для задания параметров шрифта для колонтитула и вставки в него традиционных частей (номер страницы, количество страниц, дата и время вывода документа на печать, имя файла и печатаемого листа ).
Задание 4
Цель. Получение навыков работы со списками.
Порядок выполнения.
Задание для контрольной работы
*в г. Омске за 1991 -1995 годы
|
Районы |
1991 г. |
1992 г. |
1993 г. |
1994 г. |
1995 г. |
|
Кировский |
|||||
|
Куйбышевский |
|||||
|
Ленинский |
|||||
|
Октябрьский |
|||||
|
Первомайский |
|||||
|
Советский |
|||||
|
Центральный |
|||||
|
Итого: |
(вместо звездочки вставить в название вид преступления для вашего варианта задания, например, «Грабежи в г. Омске за 1991-1995 годы»). 4. Вид преступления выбрать из следующей таблицы по номеру варианта задания (получить у преподавателя).
|
Районы |
Срзнач |
Мин |
Макс |
|
Кировский |
|||
|
Куйбышевский |
|||
|
Ленинский |
|||
|
Октябрьский |
|||
|
Первомайский |
|||
|
Советский |
|||
|
Центральный |
|
Районы |
1991-1995 |
|
Кировский |
|
|
Куйбышевский |
|
|
Ленинский |
|
|
Октябрьский |
|
|
Первомайский |
|
|
Советский |
|
|
Центральный |
9. На листе «Статистика» построить таблицу с названием «Расчет статистических показателей» по приведенному выше образцу.
Литература
Содержание
|
[1] [2] Запуск программы. Окно табличного процессора [3] Создание, сохранение и загрузка с диска рабочих книг [4] Создание нового документа [5] Сохранение документа [6] Загрузка рабочей книги [7] Операции с рабочими листами [8] Вставка и удаление рабочих листов [9] Перемещение и копирование рабочих листов [10] Переименование рабочих листов [11] Защита информации [12] Операции с ячейками таблицы [13] Выделение ячеек [14] Ввод и редактирование данных [15] Копирование и перемещение данных [16] Удаление данных [17] Автоматизация ввода данных [18] Автозаполнение [19] Автозавершение [20] Выбор из списка [21] Форматирование ячеек [22] Объединение ячеек [23] Установка границ и фона ячеек [24] Условное форматирование ячеек [25] Коррекция высоты строк и ширины столбцов [26] Использование формул и функций [27] Формулы [28] Способы адресации ячеек [29] Функции [30] Автосуммирование [31] Редактирование формул и функций [32] Выполнение логических функций [33] Создание примечаний [34] Работа со справочной системой Microsoft Excel [35] Диаграммы и графики [36] Создание диаграмм [37] Редактирование диаграмм [38] Форматирование диаграмм [39] Обработка данных [40] Закрепление областей [41] Сортировка данных [42] Применение автофильтра [43] Использование расширенного фильтра [44] Формы данных [45] Итоги [46] Сводные таблицы [47] Консолидация данных [48] Создание колонтитулов |
125,00р.
примечание
формат
значение
формула
наименьшие
затраты
125
чейка
наименьшие
затраты
125,00р.
Специальная вставка
Рисунок 26. Диалоговое окно Промежуточные итоги
Рисунок 25. Автоитоги.