БЕЛОРУССКИЙ ИНСТИТУТ ПРАВОВЕДЕНИЯ

Дистанционное образование

А.И.Бородина, Л.И. Крошинская, О.Л.Сапун

ОСНОВЫ ИНФОРМАТИКИ

И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

Прикладное программное обеспечение.

Табличные процессоры

Минск

2004

СКИЙ ИНСТИТУТ ПРАВОВЕДЕНИЯ

Дистанционное образование

А.И.Бородина, Л.И. Крошинская, О.Л.Сапун

ОСНОВЫ ИНФОРМАТИКИ

И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

Прикладное программное обеспечение.

Табличные процессоры

Минск

ООО «БИП-С Плюс»

2004

Прикладное программное обеспечение.

Табличные процессоры

ТЕМАТИЧЕСКИЙ ПЛАН

1. Назначение и классификация табличных процессоров.
   
2. Интерфейс табличного процессора Microsoft Excel.
   
3. Технология создания рабочей электронной таблицы.
   
4. Технология вычислений в электронной таблице.
   
5. Технология построения диаграмм.
   
6. Специальные средства обработки данных.
   

Литература.

Глоссарий.

Тесты.

Тренинг умений.

Краткая аннотация

Рассматриваются табличные процессоры и работа с использованием табличного процессора Microsoft Excel. Описана технология создания рабочей таблицы, специальные приемы редактирования и форматирования. Приводятся специальные средства обработки данных.

1. НАЗНАЧЕНИЕ И КЛАССИФИКАЦИЯ
ТАБЛИЧНЫХ ПРОЦЕССОРОВ

Табличные процессоры представляют собой комплексные средства для хранения различных типов данных и их обработки. Основное свойство электронных таблиц заключается в том, что при изменении содержания любых ячеек таблицы может происходить автоматическое изменение содержания во всех прочих ячейках, связанных с измененным соотношением, заданным математическим или логическими выражениями (формулами). Простота и удобство работы с электронными таблицами снискали им широкое применение в сфере бухгалтерского учета, в качестве универсальных инструментов анализа финансовых, сырьевых и товарных рынков, т. е. всюду, где необходимо автоматизировать регулярно повторяющиеся вычисления достаточно больших объемов числовых данных.

Особенность табличных процессоров состоит в том, что с их помощью можно не только вводить данные в ячейки таблиц, редактировать и форматировать их, но и применять формулы для описания связи между значениями, хранящимися в различных ячейках.

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

• автоматической обработки зависящих друг от друга данных;
  
• автоматизации итоговых вычислений;
  
• создания сводных таблиц;
  
• ведения простых баз данных;
  
• обработки записей баз данных;
  
• совместной работы с экономическими или бухгалтерскими документами;
  
• подготовки табличных документов;
  
  • построения диаграмм и графиков по имеющимся данным.
    

Современные табличные процессоры практически являются интегрированными системами, так как они содержат средства для работы с текстами, таблицами, графикой, а также различные дополнения для моделирования, анализа и прогнозирования. Все распространенные табличные процессоры позволяют:

• создавать таблицы одноразового и многоразового пользования;
  
• выполнять специальные расчеты (математические, статистические, бухгалтерские и др.);
  
• автоматизировать расчеты, причем при изменении исходных значений можно наблюдать, как изменяются расчетные и анализировать получаемые результаты;
  
• строить по данным из таблицы разнообразные графики и диаграммы;
  
• взаимодействовать с системами управления базами данных;
  
• при необходимости создавать текстовые документы;
  
• автоматизировать работу с таблицами с помощью макрокоманд.
  

На рынке программных продуктов наиболее популярными представителями этого класса являются табличные процессоры различных версий Lotus 1-2-3 фирмы Lotus Development Inc., Quattro Pro фирмы Novell и Excel корпорации Microsoft. Все они представляют собой компоненты соответствующих офисных пакетов – Lotus SmartSuit, Perfect Office, Microsoft Office.

В интегрированных пакетах Lotus 1-2-3 объединены электронные таблицы, деловая графика, база данных для экономиста, менеджера, бухгалтера. Они дают возможность формировать разнообразные отчеты, вести расчетные и учетные операции, осуществлять моделирование коммерческих операций. Кроме того, имеется возможность использовать средства высшей математики.

Другим популярным интегрированным пакетом на рынке программных продуктов является пакет Quattro Pro фирмы Novell. Он объединяет в себе электронную таблицу, систему управления базами данных, подсистему машинной графики, возможности работы с Excel, Paradox, Lotus 1-2-3.

Корпорацией Microsoft разработан табличный процессор Excel. Среди прочих подобных программных продуктов этот пакет выделяет графичеcкий интерфейс и возможность взаимодействовать с другими продуктами Microsoft Office. Функциональные возможности этого пакета позволяют широко его использовать для финансовой обработки данных, научных расчетов, инженерно-технических расчетов, автоматизации учетно-контрольной деятельности, эффективной обработки больших объемов информации, заданных в табличном виде. Он обеспечивает:

• быстрое построение таблиц любой формы одноразового и многоразового пользования;
  
• возможность обработки типов данных, таких как числа, даты, формулы; возможен ввод текстовой и вставка графической информации.
  
• поддержку форматов файлов самых разнообразных программных продуктов;
  
• импорт и экспорт табличных данных из внешних баз данных;
  
  • возможность корректировки уже созданной таблицы;
    
• использование большой библиотеки стандартных функций (математических, тригонометрических, статистических, бухгалтерских и др.);
  
• возможность выбора цветового оформления таблицы, а также выбора различных шрифтов и стилей, включая автоформатирование;
  
• импорт графических объектов в таблицу (поддержка OLE-технологии);
  
• защиту таблицы от несанкционированного доступа;
  
• средства формирования профессиональных отчетов, а также возможность использования форм Microsoft Access для этих целей;
  
• наличие механизма мастеров, которые позволяют автоматизировать выполнение операций (мастер диаграмм, мастер функций);
  
• представление таблицы в виде двух- и трехмерных графиков и диаграмм;
  
• упорядочивание, удаление, копирование, нахождение по условию данных в таблице;
  
• одновременное открытие нескольких рабочих книг, в каждой из которых можно создавать и работать с различными таблицами;
  
• связку таблиц, т.е. автоматический перенос информации из одной таблицы в другую;
  
• автоматизированную обработку таблиц с помощью макрокоманд;
  
• анализ структуры таблицы;
  
• прогнозирование различных экономических процессов;
  
• реализацию матричных и оптимизационных вычислений;
  
• поддержку работы в сети;
  
• интеллектуальное реагирование на действия пользователя – система предугадывает действия пользователя и помогает выполнить их;
  
• поддержку технологии drug-and-drop (перетащи и отпусти).
  

Вопросы для самоконтроля

1. Что представляют собой табличные процессоры?
   
2. Для чего используются табличные процессоры?
   
3. Возможен ли ввод текстовой информации в табличном процессоре?
   
4. Приведите примеры табличных процессоров.
   

2. ИНТЕРФЕЙС ТАБЛИЧНОГО ПРОЦЕССОРА

MICROSOFT EXCEL

Microsoft Excel входит в состав пакета прикладных программ Microsoft Office. Для запуска текстового процессора на выполнение необходимо выбрать ПускàПрограммыàMicrosoft Excel. Если на Рабочем столе вынесена панель ППП Microsoft Office, то для запуска процессора на выполнение можно щелкнуть мышкой по кнопке X, расположенной на данной панели. После запуска программы на экране будет представлено рабочее окно Microsoft Excel (рис. 1):

Рис. 1. Интерфейс табличного процессора Microsoft Excel

Под строкой заголовка располагается строка меню. Каждый пункт меню (Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно, ?) объединяет группу команд, реализующих те или иные операции Excel. Щелчком мыши по пункту меню вызывается ниспадающее меню.

Под строкой основного меню располагается пиктографическое меню или так называемые панели инструментов. На экране чаще всего видно две панели инструментов – Стандартная и Форматирование, но может быть гораздо больше. Серая полоса панели заполнена инструментами с изображениями, указывающими на их значение. Подведенный к инструменту указатель мыши вызывает отображение подсказки с названием инструмента в желтой рамке. При необходимости вывода дополнительных панелей инструментов используется команда ВидàПанели инструментов.

Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. В окне документа в программе Excel отображается только текущий рабочий лист рабочей книги, с которым ведется работа. Каждый рабочий лист имеет название (по умолчанию – это Лист1, Лист2 и т.д.), которое отображается на ярлычке листа, расположенном в его нижней части. В новой рабочей книге, по умолчанию, имеется 3 рабочих листа. С помощью ярлычков можно переключаться между рабочими листами, входящими в ту же рабочую книгу. Чтобы переименовать лист, надо дважды щелкнуть по его ярлычку.

Табличное пространство рабочего листа состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и далее двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются числами от 1 до 65536 (максимально допустимый номер строки).

На пересечении строк и столбцов образуются ячейки таблицы. Они являются минимальными элементами хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбцов и строки (в этом порядке, например, А4, Е7). Обозначение ячейки (ее номер) выполняет адресную функцию. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel играет роль табличного курсора. Операции входа и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши.

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

Если требуется выделить прямоугольный диапазон ячеек, то это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при этом расширяется, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, достаточно щелкнуть по заголовку столбца (строки). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.

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

Справа и снизу рабочая область обрамлена полосами прокрутки, предназначенными для перемещения по строкам и столбцам рабочего листа. Внизу экрана расположены ярлыки листов и кнопки прокрутки ярлыков. На экране всегда виден один рабочий лист – текущий.

Самая нижняя строка электронной таблицы – это строка состояния. В ней всегда содержится информация о текущем состоянии программы.

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

Рис.2. Настройка элементов интерфейса

В электронной таблице Excel существуют два режима отображения: Обычный и Разметка страницы. Обычный режим позволяет увидеть электронную таблицу в наиболее удобном для ввода и восприятия введенной информации виде. В режиме Разметка страницы можно увидеть отдельную часть электронной таблицы, которая будет выводиться на печать с автоматической разбивкой на печатные листы. Переключение между режимами осуществляется выполнением команд ВидàОбычный и ВидàРазметка
страницы соответственно.

С помощью команды ВидàМасштаб можно изменить масштаб отображения электронной таблицы, выбрав в окне диалога один из предложенных масштабов или задать свой в поле Произвольный.

Для завершения сеанса работы с Excel существует несколько способов:

• щелчок мыши по кнопке Закрыть в строке заголовка;
  
• комбинация клавиш Alt+F4;
  
• команда меню Файл/Выход;
  
• двойной щелчок мыши по кнопке системного меню;
  
• команда Закрыть системного меню.
  

Вопросы для самоконтроля

1. Как запустить табличный процессор Microsoft Excel?
   
2. Какие пункты меню содержит табличный процессор Microsoft Excel?
   
3. Что собой представляет рабочая книга?
   
4. Как обозначаются в таблице строки и столбцы?
   
5. Как обозначается диапазон ячеек в таблице?
   
6. Какие режимы отображения поддерживает табличный процессор Microsoft Excel?
   
7. Как можно изменить масштаб отображения электронной таблицы?
   

3. ТЕХНОЛОГИЯ СОЗДАНИЯ
РАБОЧЕЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

3.1. Ввод, редактирование и форматирование данных

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: тексту, числу или формуле, – а также оставаться пустой. Excel при сохранении рабочей книги записывает в файл только прямоугольную область рабочих листов, примыкающую к левому верхнему углу (ячейка А1) и содержащую все заполненные ячейки.

Тип, данных, размещаемых в ячейке, определяется автоматически при вводе. Если эти данные можно интерпретировать как число, программа Excel так и делает. В противном случае данные рассматриваются как текст. Ввод формул всегда начинается с символа «=» (знака равенства).

Ввод данных осуществляют непосредственно в текущую ячейку или в строку формул, расположенную в верхней части окна программы под панелями инструментов. Содержимое строки формул всегда соответствует содержимому текущей ячейки, так что оба метода ввода равноправны. Место ввода отмечается текстовым курсором. Если начать ввод нажатием алфавитно-цифровых клавиш, данные из текущей ячейки заменяются вводимым текстом. Если щелкнуть по строке формул или дважды по текущей ячейке, старое содержимое ячейки не удаляется и появляется возможность его редактирования. Вводимые данные в любом случае отображаются как в ячейке, так и в строке формул.

Чтобы завершить ввод, сохранив введенные данные, используют кнопку [Enter] в строке формул или клавишу Enter на клавиатуре. Чтобы отменить внесенные изменения и восстановить прежнее значение ячейки, используют кнопку [Отмена] в строке формул или клавишу Esc. Для очистки текущей ячейки или выделенного диапазона проще всего использовать клавишу [Delete].

3.2. Форматирование содержимого ячеек

Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа – по правому. Способ выполнения вычислений не зависит от того, каким способом отформатированы данные, но для человека внешний вид данных позволяет быстро определить их назначение.

Числовые форматы. Для экономических расчетов применяют три принципиально разных формата записи чисел: обычный числовой, финансовый и формат даты.

Обычный числовой формат используют для записи различных отвлеченных чисел, например: количества какой-либо продукции, процентной ставки, возраста и т.п.

Для записи денежных сумм используют финансовые форматы, отличающиеся указанием денежной единицы (например, р.) и фиксированным числом знаков после десятичного разделителя (обычно две цифры, обозначающие копейки). Денежные суммы можно записывать и обычными числами, но при работе с финансовым форматом программа иначе подходит к округлению чисел и контролирует, чтобы в результате многочисленных операций с очень большими суммами не потерялось ни единого рубля.

Для записи даты можно применять разные варианты форматирования. Но следует учитывать, что хотя даты – это тоже числа, с ними возможны не все числовые операции. Так, разность дат имеет физический смысл, а сумма дат никакого смысла не имеет.

Текстовые форматы. Текстовые форматы используют для записи текстовых строк и цифровых нечисловых данных. К таким данным относятся, например, почтовые индексы, номера телефонов и т.д. Несмотря на то, что номера телефонов записывают цифрами, арифметические операции с ними не имеют никакого смысла.

Изменение формата данных в ячейке. Чтобы изменить формат отображения данных в текущей ячейке или в выбранном диапазоне ячеек, используют команду ФорматàЯчейки. В открывшемся диалоговом окне можно выбирать формат записи данных (количество знаков после запятой, указание денежной единицы, способ записи даты и прочее), задавать направление текста и метод его выравнивания, определять шрифт и начертание символов, управлять отображением и видом рамок, задавать фоновый цвет.

ание строк и столбцов

При изучении приемов ввода и редактирования данных может возникнуть, возникает вопрос: что делать в том случае, если ширина вводимого в ячейку текста гораздо больше ширины самой ячейки? Это зависит от того, будет ли использована соседняя ячейка. Если она пуста, то текст на экране расположится поверх нее. При вводе данных в соседнюю ячейку текст в предыдущей ячейке будет обрезан. При вводе чисел, ширина столбца которых превышает ширину ячейки на экране, Excel будет отображать их в виде решетки (#######) или в экспоненциальной форме (например, вместо числа 10100000000 увидите 1,01Е+11, что означает 1,01*10¹¹, а число 0,000000000075 будет представлено в виде 7,5Е-11, что означает 7,5*10^-11).

Ширину столбца можно регулировать. Для этого нужно поместить указатель мыши на разделительную линию между заголовками столбцов, например А и В. Когда указатель преобразуется в двунаправленную стрелку, выполните двойной щелчок мыши. В ячейке текущего столбца автоматически будет установлена ширина по самой длинной записи. Те же возможности предоставляет нам команда ФорматàСтолбецàАвтободбор ширины. Для регулирования ширины столбца нажмите левую клавишу мыши на границе заголовков столбцов и, удерживая ее, переместите разделительную линию столбцов в нужное положение. Можно воспользоваться командой ФорматàСтолбецàШирина, предварительно выделив нужный столбец или хотя бы одну ячейку этого столбца. Ширина столбца задается числом в диапазоне от 0 до 255. Это число приблизительно равно количеству символов стандартного шрифта, которые поместятся в ячейку указанной ширины. Аналогично выполняется и процедура изменения высоты строки, причем высота строки изменяется в диапазоне от 0 до 409 пунктов. Размер одного пункта составляет 1/72 дюйма, или приблизительно 0,4 мм.

Если текст, вводимый в ячейку, слишком длинный, не всегда следует менять ширину столбца. Лучше расположить его в несколько строк. Это можно сделать, выполнив команду меню ФорматàЯчейки для нужной ячейки и установить флажок Переносить по словам на вкладке Выравнивание.

Процесс форматирования сводится к установке требуемых параметров для нужных ячеек или областей ячеек рабочего листа. Начнем с выбора параметров шрифта. В это понятие, как правило, входят тип шрифта (гарнитура), размер (кегль), начертание, цвет и дополнительные эффекты. Для оформления электронных таблиц используются различные типы шрифтов, среди которых встречаются русифицированные, как, например Times New Roman Cyr, и нерусифицированные, как Symbol; пропорциональные, каждый символ которых занимает в строке разный промежуток (Times New Roman Cyr), и непропорциональные, для которых все символы шрифта имеют одинаковую ширину (Courier Cyr); с засечками (Times New Roman Cyr) или без них (Arial Cyr). Выбор типа, размера начертания шрифта осуществляется через меню ФорматàЯчейкиàШрифт.

Возможно выравнивание информации в ячейках рабочего листа По левому
краю, По центру, По правому краю. Если есть необходимость изменить не только выравнивание по горизонтали, но и по вертикали, а также ориентацию текста внутри ячейки (например, расположить его сверху вниз, снизу вверх или под некоторым углом), воспользуйтесь меню ФорматàЯчейкиàВыравнивание и выберите нужные параметры в диалоговом окне (рис.3).

Для оформления заголовка таблицы воспользуйтесь кнопкой Объединить и поместить в центре на панели Форматирование, или
командой ФорматàЯчейкиàОбъединить ячейки.

Рис.3. Форматирование ячеек

Разделительные линии сетки могут быть удалены с экрана или при выводе на печать. В первом случае необходимо воспользоваться командой СервисàПараметры и снять флажок Сетка на вкладке Вид, что приведет к изменению вида рабочей области. Во втором случае – команду ФайлàПараметры
страницы и снять флажок Сетка на вкладке Лист. При этом линии раздела ячеек не будут выводиться на печать даже в случае присутствия их на экране.

Функцию установки рамок (границ) для одной ячейки или областей ячеек выполнит кнопка Границы на панели Форматирование или с помощью команды ФорматàЯчейкиàГраница.

3.5. Сохранение таблицы на диске

Для сохранения файла на диске можно воспользоваться командой ФайлàСохранить. При первом выборе данной команды для новой рабочей книги откроется окно диалога Сохранить как, в котором в поле Папка необходимо выбрать диск и место на диск (т.е. папку) для сохранения. Удалите стандартное имя Книга1 в поле Имя файла и введите свое имя. Обратите внимание, что все таблицы Excel имеют расширение .xls, которое присваивается автоматически, если в поле Тип файла по умолчанию содержится запись Книга Microsoft Excel.

Вопросы для самоконтроля

1. Как сохранить электронную таблицу в памяти ПЭВМ?
   
2. Какие форматы данных можно ввести в электронную таблицу?
   
3. Как можно изменить ширину строки и столбца?
   
4. Как можно объединить строки и столбцы?
   
5. В чем состоит особенность ввода формул в электронной таблице?
   
6. Какое расширение имеют файлы, созданные в табличном процессоре MS Excel?
   
7. Как можно установить рамки (границы) в таблице?
   

4. ТЕХНОЛОГИЯ ВЫЧИСЛЕНИЙ
В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

4.1. Редактирование формул

Вычисления в таблицах осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменять стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается только текущий результат вычисления этой формулы. Чтобы увидеть формулу, а не результат ее работы, надо выделить ячейку (сделать ее текущей) и посмотреть на запись, которая отображается в строке формул.

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

Вместо чисел в формуле используются имена ячеек, т.е. ссылки на ячейки. Ссылки выполняют роль адресов ячеек, содержимое которых используется в вычислениях. Это означает, что результат расчета зависит от того, какие числа находятся в ячейках, участвующих в вычислении. Таким образом, ячейка, содержащая формулу, является зависимой. Значение в зависимой ячейке подлежит пересчету всякий раз, когда изменяются значения в ячейках, на которые указывают ссылки, входящие в формулу.

Ссылку на ячейку можно задать разными способами:

• адрес ячейки ввести вручную;
  
• щелчок на нужной ячейке или выбор диапазона, адрес которого требуется ввести; ячейка или диапазон при этом выделяются пунктирной рамкой.
  

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

Приступая к созданию формулы, следует помнить следующие правила:

• формуле всегда должен предшествовать знак равно «=»;
  
• в формуле нельзя использовать символы русского алфавита;
  
• необходимо учитывать последовательность выполнения математических операций.
  

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

Таблица 1

Порядок выполнения операторов

Ввод формулы в ячейку можно произвести при помощи клавиатуры или мыши. В первом случае формулу следует напечатать в нужную ячейку рабочего листа и подтвердить ввод нажатием клавиши Enter. Однако может случиться, что вместо привычного результата вычислений можно увидеть формулу. В этом случае необходимо выполнить команду СервисàПараметры и на вкладке Вид снять флажок Формулы (рис. 2)

4.2. Ссылки абсолютные и относительные

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

Пусть, например, в ячейке I2 имеется ссылка на ячейку А3, которая располагается на один столбец левее и на одну строку ниже. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку D5 ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку С6.

Абсолютная адресация. При абсолютной адресации адреса ссылок при копировании формулы не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как постоянная (нетабличная). Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1.

4.3. Копирование содержимого ячеек

Копирование и перемещение содержимого ячеек в Excel можно осуществлять методом перетаскивания или через буфер обмена. При работе с небольшим числом ячеек удобно использовать метод перетаскивания, при работе с большими диапазонами удобнее пользоваться буфером обмена.

Метод перетаскивания. Чтобы методом перетаскивания скопировать или переместить текущую ячейку (выделенный диапазон) вместе с содержимым, следует установить указатель мыши на рамку текущей ячейки (он примет вид стрелки). Ячейку можно перетаскивать в любое место рабочего листа.

Для выбора способа выполнения этой операции, а также для более надежного контроля над ней рекомендуется использовать специальное перетаскивание с помощью правой кнопки мыши. В этом случае при отпускании кнопки появляется специальное меню, в котором можно выбрать конкретную выполняемую операцию.

Копирование и перемещение данных через буфер обмена. Передача информации через буфер обмена имеет в программе Excel определенные особенности, связанные со сложностью контроля над этой операцией. Сначала необходимо выделить копируемый (вырезаемый) диапазон и дать команду на его помещение в буфер обмена: ПравкаàКопировать или ПравкаàВырезать. Вставка данных в рабочий лист возможна лишь немедленно после их помещения в буфер обмена. Попытка выполнить любую другую операцию приводит к отмене начатого процесса копирования или перемещения. Однако утраты данных не происходит, поскольку «вырезанные» данные удаляются из места их исходного размещения только тогда, когда вставка будет успешно завершена.

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

Автоматизация ввода

Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel предоставляет средства для автоматизации ввода. К числу предоставляемых средств относятся: автозавершение, автозаполнение числами и автозаполнение формулами.

Автозавершение. Для автоматизации ввода текстовых данных используется метод автозавершения. Его применяют при вводе в ячейки одного столбца рабочего листа текстовых строк, среди которых есть повторяющиеся. Такая ситуация часто возникает при использовании рабочей книги Excel в качестве базы данных.

В ходе ввода текста в очередную ячейку программа проверяет соответствие введенных символов строкам, имеющимся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши [Enter] подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку. И наоборот, чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними.

Автозаполнение числами. При работе с числами используется метод автозаполнения. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – это маркер заполнения. При наведении на него указатель мыши приобретает вид тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном или вертикальном направлении.

Если ячейка содержит число (в том числе дату или денежную сумму), то при перетаскивании маркера происходит либо ее заполнение данными по закону арифметической прогрессии. Для выбора способа автозаполнения следует производить специальное перетаскивание с использованием правой кнопки мыши.

Например, ячейка А1 содержит число 1. Необходимо перетащить маркер заполнения правой кнопкой мыши так, чтобы рамка охватила ячейки А1, В1 и С1 и отпустить кнопку мыши. Если в открывшемся меню выбрать пункт Копировать ячейки, то все ячейки будут содержать число 1. Если выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду ПравкаàЗаполнитьàПрогрессия. В открывшемся диалоговом окне Прогрессия выбираются тип прогрессии (арифметическая, геометрическая, даты, автозаполнение), величина шага и предельное значение. После щелчка на кнопке [Ок] Excel автоматически заполняет ячейки в соответствии с заданными правилами.

Автозаполнение формулами. Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения формулами принимается во внимание характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, а абсолютные ссылки остаются без изменений.

Таблица 2

Примеры оформления ячеек со ссылкой

4.5. Стандартные функции

Стандартные функции используются в программе Excel только в формулах. Вызов функции состоит в указании в формуле имени функции, после которого в скобках указывается список параметров. Отдельные параметры разделяются в списке точкой с запятой. В качестве параметра можгут использоваться число, адрес ячейки или произвольное выражение, для вычисления которого также могут применяться функции.

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

Мастер функций. При выборе пункта Другие функции (или кнопка Мастер функций на панели инструментов Стандартная) запускается Мастер функций, облегчающий выбор нужной функции (рис. 4).

Рис. 4. Мастер функций

В списке Категория выбирается категория, к которой относится функция (если определить категорию затруднительно, используют пункт Полный
алфавитный перечень), а в списке Функция – конкретная функция данной категории. После щелчка по кнопке [Ок] имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров.

Параметры можно вводить непосредственно в строку формул или в поля палитры формул, а если они являются ссылками – выбирать на рабочем листе.

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

Итоговые функции.
В экономических и бухгалтерских расчетах применяют главным образом итоговые функции. Общим свойством этих функций является то, что их список параметров может содержать заранее не известное количество параметров (или диапазон неопределенного размера), а в результате вычисления получается одно число, каким-то образом характеризующее весь набор параметров. Большая часть таких функций находится в категориях Математические и Статистические.

Итоговые вычисления предполагают получение числовых характеристик, описывающих определенный набор данных в целом. Например, возможно вычисление:

• суммы значений элементов, входящих в набор;
  
• среднего значения и других статистических характеристик;
  
• количества или доли элементов набора, удовлетворяющих определенным условиям.
  

Итоговые вычисления в Excel выполняются при помощи стандартных функций. Особенность итоговых функций состоит в том, что при их задании программа пытается «угадать», в каких ячейках заключен обрабатываемый набор данных, и задать параметры функции автоматически.

В качестве параметра итоговой функции чаще всего задается прямоугольный диапазон ячеек, размер которого определяется автоматически. Выбранный диапазон рассматривается как отдельный параметр «массив», и в вычислениях используются все ячейки, составляющие его.

Суммирование. Наиболее типичная из функций, предназначенных для итоговых вычислений – это функция суммирования (СУММ). Это единственная функция, для применения которой есть отдельная кнопка на стандартной панели инструментов (кнопка Автосумма). Диапазон суммирования, выбираемый автоматически, включает ячейки с данными, расположенными над текущей ячейкой (предпочтительнее) или слева от нее и образующими непрерывный блок. При неоднозначности выбора используется диапазон, непосредственно примыкающий к текущей ячейке.

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

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

Контроль логических ошибок. При наличии ошибок в формуле нарушается логика работы. В этом случае программа Excel сама способна помочь в их поиске и исправлении. Эти ошибки можно разделить на две группы: неверные
формулы и циклические
ссылки.

Неверные формулы. Если получить значение в результате вычисления формулы по каким-то причинам невозможно, программа Excel выдает вместо значения ячейки код ошибки. Возможные коды ошибок приведены в табл. 3.

Таблица 3

Стандартные сообщения программы об ошибках

параметра#ПУСТО!В формуле использовано пересечение диапазонов, не

включающее ни одной ячейки#ЧИСЛО!При вычислении получилось число, слишком большое

или слишком маленькое для программы Excel. Как правило это следствие неправильности формулы#ССЫЛКА!Формула указывает на ячейку, которая отсутствует на

рабочем листе. Чаще всего такая ошибка возникает

вследствие копирования формулы с относительной адресацией влево или вверх, из-за чего возникают ссылки на столбцы, предшествующие столбцу А, и строки, предшествующие первой

Циклические ссылки. При возникновении циклических ссылок Excel выдает сообщение об ошибке немедленно. При появлении новых формул их значения вычисляются сразу же, а циклическая ссылка делает невозможным вычисление данных в одной или нескольких ячейках.

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

Если программа обнаруживает в электронной таблице циклические ссылки, она немедленно выдает предупреждающее сообщение и открывает панель инструментов Циклические ссылки. Все ячейки, содержащие циклические ссылки, помечаются голубым кружком, а в строке состояния появляются слово Цикл и список таких ячеек.

Для поиска цикла, который всегда связан с ошибкой в логике работы формул рабочего листа, удобно использовать панель инструментов Циклические ссылки. Все ячейки с циклическими ссылками приведены в раскрывающемся списке на этой панели. Если цикл взаимных ссылок включает несколько ячеек (так называемая косвенная циклическая ссылка), то проследить его можно с помощью кнопок Влияющие ячейки и Зависимые ячейки. Если щелкнуть по кнопке [Влияющие ячейки], то программа автоматически выделит ячейки, от которых непосредственно зависит значение в текущей ячейке.

Проверка вводимых данных. Часто вводом данных в отчетные документы занимаются разные лица которые эти данные получают и которые отвечают за их правильность. Кроме того, ввод данных часто осуществляют в спешке, а возможность выполнить перепроверку существует не всегда. Для таких случаев в Excel предусмотрены средства автоматического подтверждения правильности ввода, основанные на деловых правилах, принятых в данной организации. Например, в организации может быть принят верхний предел оклада, так что все, что выходит за данный предел, является ошибкой ввода.

Правила, используемые для подтверждения правильности ввода, должны быть выработаны заранее, т.е. до того, как будет начат реальный ввод данных.

Выделив область рабочего листа, предназначенную для ввода данных определенного типа, выбирается команда ДанныеàПроверка. Условия, накладываемые на вводимые значения, задаются на вкладке Параметры диалогового окна Проверка вводимых значений.

Дополнительные средства контроля. Все инструменты контроля правильности электронных таблиц сосредоточены на панели инструментов Зависимости, которую можно открыть командой СервисàЗависимостиàПанель зависимостей. Здесь, в частности, имеются кнопки, аналогичные кнопкам панели инструментов Циклические ссылки, позволяющие прослеживать влияние и зависимость ячеек. Кроме того, панели инструментов Зависимости позволяет выделить ячейки, содержимое которых не отвечает заданным условиям правильности данных. Это особенно удобно, если часть данных была введена, до того как были заданы эти условия. Ячейки с неверным содержанием помечаются кружком.

4.7. Подготовка рабочей книги
к печати

Прежде чем печатать, необходимо убедиться в том, что, во-первых, в созданных таблицах нет ошибок, во-вторых, что печатный вариант таблицы будет так же хорош, как и электронный.

Проверку можно проводить для отдельных рабочих листов, рабочей книги в целом, а при необходимости – и для произвольных областей ячеек. В первом случае достаточно находиться на том рабочем листе, который нуждается в проверке, во втором – должны быть выделены все ярлычки рабочих листов в рабочей книге, в третьем – нужный диапазон ячеек. Отметим, что выделить ярлычки можно, щелкая по ним мышкой при нажатой клавише [Ctrl], либо выполнив команду Выделить все листы контекстно-зависимого меню, которое вызывается на экран при нажатии правой клавиши на ярлычке любого рабочего листа. Далее необходимо выполнить команду СервисàОрфография либо воспользоваться клавишей F7.

Затем рабочая таблица просматривается при выполнении команды ФайлàПредварительный
просмотр. Данный режим исключает всякую возможность редактирования, форматирования и других изменений созданного документа. Если результаты просмотра полностью удовлетворяют пользователя, можно приступить к печати. Процедура распечатки зависит от того, что печатать – всю рабочую книгу, текущий лист или избранный диапазон ячеек. Для распечатки текущего рабочего листа необходимо выполнить команду ФайлàПечать и в диалоговом окне Печатать установить переключатели все в группе Печатать и выделенные листы в группе Вывести на печать (рис. 5). Попутно можно указать число копий и нужно ли распечатанные страницы разбирать по копиям (флажок Разобрать по копиям). Аналогично производится распечатка нескольких рабочих листов, только требуется предварительно выделить их ярлычки в рабочей книге. Для печати всей рабочей книги можно выделить все ее рабочие листы и действовать по предыдущему сценарию, либо выполнить команду ФайлàПечать и установить переключатель всю книгу в группе Вывести на печать. В данном случае будут печататься только непустые листы.

Распечатать некоторый фрагмент можно двумя способами:

1. Выделить нужный фрагмент, выполнив команду ФайлàПечать и установить переключатель выделенный диапазон в группе Вывести на печать.
   
2. Выделить нужный фрагмент ФайлàОбласть печатиàЗадать. При этом область печати устанавливается жестко и просматривается в режиме предварительного просмотра. При необходимости распечатки другой области эту необходимо отменить, выполнив команду ФайлàОбласть печатиàУбрать.
   

Рис. 5. Настройка печати

Если таблица велика и не помещается на одной печатной странице, воспользуйтесь командой ФайлàПараметры и на вкладке Страницы выберите ориентацию Альбомная, в группе Масштаб установите переключатель разместить не более чем на 1 стр. в ширину и 2 стр. в высоту.
Поля Размер бумаги и Качество печати служат для выбора характеристик печатного листа и качества изображения соответственно. Вкладка Колонтитулы используются для создания колонтитулов, вид которых можно просто выбрать из списков Верхний колонтитул и Нижний колонтитул.

И, наконец, на вкладке Лист задаются параметры печати, такие как черно-белая или черновая печать, распечатка заголовков строк и столбцов. Настройка осуществляется с помощью соответствующих флажков в группе Печать. Здесь же можно указать порядок вывода печатных страниц: вниз, затем вправо или вправо, затем вниз. В группе Печатать на каждой странице
можно указать те строки/столбцы, которые будут использоваться в качестве заголовков на каждом листе при печати электронной таблицы, а в поле Выводить на печать диапазон: – ту область ячеек, которая предназначена для печати (рис. 6).

Рис. 6. Настройка параметров страницы

Вопросы для самоконтроля

1. Для чего используются ссылки Microsoft Excel?

2. Какие ссылки бывают в Microsoft Excel?

3. Для чего используется Мастер функций?

4. Что такое циклическая ссылка?

5. Можно ли вывести не всю таблицу, а только диапазон ячеек?

6. Возможно ли копирование формул в Microsoft Excel?

7. Как можно автоматически заполнить числами ячейки?

5. ТЕХНОЛОГИЯ ПОСТРОЕНИЯ ДИАГРАММ

В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отобразить несколько рядов данных.

Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может располагаться на том же листе, на котором находятся данные, или на любом другом листе. Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных автоматически меняет свой вид.

Для построения диаграммы обычно используют Мастер диаграмм, запускаемый щелчком по кнопке Мастер диаграмм на стандартной панели инструментов. Часто удобнее заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, но задать эту информацию можно и в ходе работы (рис. 7).

Рис. 7. Выбор типа диаграмм

На первом шаге работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке Тип на вкладке Стандартные. Для выбранного типа диаграммы справа имеется несколько вариантов представления данных (палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке Нестандартные отображается набор полностью сформированных типов диаграмм с готовым форматированием. После задания формы диаграммы следует щелкнуть по кнопке Далее.

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

Третий шаг работы мастера (после щелчка по кнопке Далее) состоит в выборе оформления диаграммы (рис. 8).

Рис. 8. Построение диаграммы

На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка Заголовки);
  
• отображение и маркировка осей координат (вкладка Оси);
  
• отображение сетки линий, параллельных осям координат (вкладка Линии сетки);
  
• описание построенных графиков (вкладка Легенда);
  
• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);
  
• представление данных, использованных при построении графика, в виде таблицы (вкладка Таблица данных).
  

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсутствовать.

На последнем шаге работы мастера (после щелчка на кнопке Далее) указывается, следует ли использовать для размещения диаграммы новый рабочий лист или один из имеющихся. Обычно этот выбор важен только для последующей печати документа, содержащего диаграмму. После щелчка по кнопке Готово диаграмма строится автоматически и вставляется на указанный рабочий лист.

Редактирование диаграммы. Готовую диаграмму можно изменить. Она состоит из набора отдельных элементов, таких как графики (ряды данных), оси координат, заголовок диаграммы, область построения и пр. При щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши – описывается всплывающей подсказкой. Используя контекстное меню, можно изменять параметры отображения выбранного элемента данных.

Если требуется внести в диаграмму существенные изменения, следует вновь воспользоваться мастером диаграмм. Чтобы удалить диаграмму, можно удалить рабочий лист, на котором она расположена (ПравкаàУдалить лист) или выделить диаграмму, внедренную в рабочий лист с данными, и нажать клавишу [Delete].

Вопросы для самопроверки

1. Какие средства создания диаграмм существуют в Microsoft Excel?
   
2. Какие этапы существуют при построении Microsoft Excel?
   
3. Что такое Легенда?
   
4. Как можно отредактировать диаграмму?
   
5. Можно ли построить диаграмму на отдельном листе?
   
6. Как можно удалить диаграмму?
   
7. Можно ли отобразить на диаграмме несколько рядов данных?
   

6. СПЕЦИАЛЬНЫЕ СРЕДСТВА ОБРАБОТКИ ДАННЫХ

6.1. База данных

В настоящее время часто используется термин «база данных». И хотя большая часть населения склонна считать данный термин порождением компьютерной среды, тем не менее, в общем смысле под базой данных может пониматься любая совокупность связанной информации, структурированная специальным образом и объединенная некоторым признакам.

Базы данных MS Excel часто называются списками, и это объяснимо, поскольку они размещаются в таблицах, столбцы которых называются полями, а строки записями.

Организация ввода информации зависит от того, планируются ли в базе данных вычисляемые поля. Если нет, то сначала формируется строка заголовков поле, далее с помощью команды ДанныеàФорма производятся ввод и просмотр информации. В качестве примера рассмотрим создание базы данных сотрудников кафедры (Рис. 9.).

Рис. 9. База данных сотрудников

На экране отразится окно диалога, которое представляет собой форму для ввода, просмотра, редактирования и удаления записей. Помимо этого форму можно использовать и для поиска конкретной информации. Поля формы заполняются последовательно, для перемещения по полям можно использовать мышку или клавишу TAB на клавиатуре. После ввода всей записи, т.е. заполнения всех полей необходимо щелкнуть по кнопке Добавить в окне диалога. После завершения процесса ввода можно проконтролировать результаты, пролистывая их при помощи кнопок Назад, Далее. Кнопка Удалить служит для уничтожения текущей записи. Индикатор номера записи над кнопками в окне диалога на каждом шаге отражает номер текущей записи и общее число записей в списке, например 1 из 12 (рис. 10).

Рис. 10. Добавление или удаление записи

6.2. Создание списков
с вычисляемыми полями

После открытия файла нагрузки сотрудников добавим новый рабочий лист командой ВставкаàЛист и переместим его в конец рабочей книги, перетащив с помощью мыши. Далее введем название полей и отформатируем их нужным образом (рис. 11).

Рис. 11. Отформатированная база данных

Надбавка за стаж – 15% для тех, чей стаж более 15 лет, 10% – более 10 лет, 5% – более 5 лет. Премия составляет 50% от оклада. Занесем в ячейки Н2 и I2 следующие формулы:

=ЕСЛИ(F2>15;G2*0,15;ЕСЛИ(F2>10;G2*0,1;ЕСЛИ(F2>5;G2*0,05;0)))

=G2*0,5

Сформирована первая запись в базе данных. Остальные записи формируются аналогично.

6.3 Выборки данных по группе критериев

Самый простой способ выбора информации – воспользоваться известной командой Форма и в окне диалога щелкнуть по кнопке Критерии. Ввести условия отбора в нужные поля и просмотреть подходящие записи, используя кнопки Назад, Далее в окне диалога. У этой процедуры есть свои достоинства и недостатки. С одной стороны, выбранные таким образом записи, не могут быть выделены из всего списка, т.е. их нельзя скопировать или вывести на печать. С другой стороны, ввод критериев позволяет сократить число записей, отображаемых в форме, что удобно в случае поиска какой-либо конкретной информации. К примеру, найти всех сотрудников, чья фамилия начинается на букву «И», с окладом более 60 000 рублей можно, задав следующие критерии отбора: букву «И» в поле «Фамилия» (причем можно вводить букву строчную и прописную – результат тот же) и условие > 60000 в поле «Оклад».

6.4. Фильтрация данных в списке

Под фильтрацией понимается просмотр и отбор записей в базе данных (списке), отвечающих определенным условиям. Причем результаты фильтрации можно копировать, перемещать, распечатывать. MS Excel дает две возможности фильтрации – с применением команд Автофильтр и Расширенный фильтр. Автофильтр можно использовать лишь в том случае, если условия отбора определяются только по одному полю и их не более двух. Расширенный фильтр предлагает более широкий спектр возможностей – выбор по условиям, определяемым для нескольких полей, по множественным и вычисляемым условиям.

Для работы выберем команду ДанныеàФильтрàАвтофильтр. Ее выполнение влечет за собой появление кнопок раскрывающихся списков в строке заголовков полей. Раскрыв список в любом поле, можно получить набор всех значений, встречающихся в данном столбце, и ряд команд. В базе данных в поле «Должность» раскрывающийся список содержит следующую информацию: ассистент, преподаватель, доцент, профессор, а также команды Все, Первые 10…. Условие. Для выбранной информации о профессорах щелчком мыши выберите данную должность в списке. В результате на экран выводится только часть записей, все остальные данные являются временно скрытыми. Вернуть их можно с помощью команды Все.

Далее команда, условно названная Первые 10…, служит для отбора некоторого количества наибольших и наименьших значений. На экране появится окно Наложение условия по списку. Данная команда доступна только для числовых полей. Наибольший интерес представляет фильтрация на основе пользовательских критериев. Такую возможность обеспечивает команда Условие… Произведем фильтрацию данных по полю «Стаж работы». В раскрывающемся списке данного поля выполним команду Условие…, а далее в окне диалога Пользовательский автофильтр зададим одно или два условия фильтрации по данному столбцу. Например, выбор записей, где стаж работы – от 10 до 20 лет, условия должны выглядеть, как представлено на рис. 12.

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

Рис. 12. Использование фильтра для отбора записей

6.5. Фильтрация данных в базе данных (списке)

Для расширения более сложных задач фильтрации используется средство Расширенный фильтр. Пользоваться им немного сложнее. Чем командой Автофильтр, поскольку необходимо предварительно сформировать диапазон условий фильтрации. Построим диапазон условий:

1. Добавьте пять пустых строк перед базой данных, для чего выделите пять строк таблицы и воспользуйтесь командой ВставкаàСтроки.
   
2. Скопируйте в первую строку заголовки полей.
   
3. В пустые строки введите условия отбора в виде неравенств, перечислений или неравенств с формулами. Используйте условия рис. 13.
   

   Далее установите курсор в любую ячейку списка и выполните команду ДанныеàФильтрàРасширенный фильтр. Область Обработка предназначена для определения места обработки списка: выберите переключатель скопировать результат в другое место, если не хотите испортить список отфильтрованными записями. В поле Исходный диапазон делаем ссылку на диапазон, содержащий список для фильтрации. Здесь обычно автоматически указывается область всей базы данных, в нашем случае $A$6:$I$18. Далее щелкните мышью в поле Область условий и укажите диапазон условий $A$1:$I$4, сформированный на рабочем листе. Проще всего это сделать, выделив нужную область при помощи мыши. Следующим шагом будет переход в поле Поместить результат в диапазон, тоже щелкнув в нем мышью, и ввод ссылку на ячейку, начиная с которой следует выводить отфильтрованные записи, например $A$20. Результат фильтрации увидите на экране.
   

   Рис. 13. Фильтрация данных в базе данных
   

   6.6. Сортировка базы данных (списка)
   

   Под сортировкой понимается процесс упорядочения записей в базе данных (списке) в соответствии с требованиями определенной задачи. Сортировку можно производить по одному или нескольким полям (двум или трем), причем если поле содержит разнородную информацию, то сортировка по возрастанию предполагает следующий порядок: числа, текст, логические значения, значения ошибок, пустые ячейки, а по убыванию соответственно, обратный порядок. При сортировке текстовых полей порядок по возрастанию означает алфавитный порядок, т.е. от А до Я, для числовых – от минимального к максимальному, для дат – от более ранней к более поздней. Отсортируем записи списка в порядке возрастания стажа работы. В окне диалога
   Сортировка диапазона команды Данные/Сортировка можно изменить параметры сортировки. Например, Учитывать регистр для различия строчных и прописных букв: изменить направление сортировки, т.е. сортировать строки диапазона (записи) или столбцы диапазона (поля); определить пользовательский порядок сортировки в поле Сортировка по первому ключу.
   

   6.7. Вычисление промежуточных итогов

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

   Рис. 14. Вычисление промежуточных итогов
   

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

   6.8. Консолидация данных

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

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

   При консолидации данных из табл. 1 и 2 на рис. 15 определим область назначения на том же рабочем листе.
   

   Рис. 15. Консолидация данных
   

   Проделайте следующие операции:
   

• поместите курсор в ячейку А30, начиная с которой будет выведена консолидированная информация;
  
• выполните команду меню ДанныеàКонсолидация;
  
• в окне диалога Консолидация в раскрывающемся списке Функция укажите функцию Сумма для использования при консолидировании данных;
  
• переместите курсор в поле Ссылка и последовательно сделайте ссылку на табл. 1 и табл. 2, т. е. на области-источники. Ссылку на нужную область проще всего сделать, выделив ее на рабочем листе при помощи мыши. Так как ссылки надо делать на несколько областей-источников, то после указания области табл. 1 обязательно щелкните по кнопке Добавить для перемещения ссылки в поле Список диапазонов, и только после этого укажите область табл. 2;
  
• область Использовать в качестве имен предназначается для указания, использовать или нет метки из первой строки (флажок подписи верхней строки) и из первого столбца (флажок значения левого столбца) облатей-источников;
  
• незадействованным остался только флажок Создавать связи с исходными данными, который используется для установления связей между областью назначения и областями-источниками для автоматического обновления результатов консолидации при изменении данных в областях-источниках.
  

  В результате консолидации будет получена таблица, по виду идентичная табл. 1 и 2, каждая ячейка которой будет содержать сумму соответствующих значений из табл. 1 и 2.
  

  В случае если области-источники располагаются на различных рабочих листах рабочей книги (например, на листах 1 и 2) и результирующую консолидируемую информацию необходимо поместить на отдельном рабочем листе (например, на листе 3), операция консолидации отличается лишь порядком указания ссылок на области-источники, которые в данном случае определяются ссылкой на лист и ячейки.
  

  При консолидации данных из различных рабочих книг удобнее всего открыть все рабочие книги и разместить на экране рядом, используя команду меню ОкноàРасположить. Далее операция консолидации выполняется аналогично предыдущим случаям, за исключением того, что ссылки на области-источники в данном случае содержат полный путь к файлу и ссылки на рабочую книгу, лист и ячейки.
  

  6.9. Макросы

   

  Макрос – это способ автоматизации часто встречающихся операций. В простейшем случае макрос создается запоминанием ряда операций, выполняемых на компьютере, которые затем воспроизводятся. В Excel существуют два вида макросов: командный и функциональный.
  

  Командный макрос – это последовательность команд в Excel и функциональный макрос – пользовательская функция, возвращающая значение.
  

  Для создания макроса необходимо выполнить команду Сервис à Макрос àЗапись макроса. В меню Сервис выбрать команду Запись макроса и установить флажок относительные ссылки. Это позволяет использовать макрос в любой ячейке таблицы. Затем ввести имя макроса и его описание. Имя макроса должно быть записано записано на языке Excel, т.е. состоять только из букв, цифр и знака подчеркивания.
  

  Щелкнуть по кнопке Параметры, диалоговое окно расширится, предоставив дополнительные операции, что даст возможность установить такие параметры как Сочетание клавиш, Описание макроса. Затем щелкнуть по кнопке [Ок]. Произойдет запись всех операций (включая и ошибки).
  

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

  Вопросы для самоконтроля

   

1. Что представляет собой база данных в Microsoft Excel?
   
2. Что является записями в базе данных Microsoft Excel?
   
3. Что является полями в базе данных Microsoft Excel?
   
4. Можно ли отобрать записи в базе данных по нескольким критериям?
   
5. Что понимается под фильтрацией в Microsoft Excel?
   
6. Для чего используется консолидация?
   
7. Что такое макрос и для чего он используется?
   

ЛИТЕРАТУРА

1. Алексеев А.П. Информатика 2001.М., 2001.
   
2. Бауэр Ф.Л. Гооз Г. Информатика. Вводный курс: В 2-х ч. /Пер. с нем. М., 1990.
   
3. Ваш компьютер. //Радиомир, , 2001, №№1 – 8.
   
4. Информатика для юристов и экономистов: Учебник. /Под ред. С.В.Симоновича , СПб., 2001.
   
5. Основы информатики: Учебн.пособие./А.Н.Морозевич, Н.Н. Говядинова, Б.А.Железко и др. / Под ред. А.Н. Морозевича. Мн., 2001.
   
6. Симонович С., Евсеев Г., Алексеев А. Общая информатика.М.,2000.
   
7. Шафрин Ю.А. Основы компьютерной технологии: Учебн.пособие.:М.:, 1996.
   
8. Экономическая информатика: Учебник. /Под ред. П.В.Конюховского, Д.Н. Колесова. СПб., 2000
   

ГЛОССАРИЙ

ТЕСТЫ

1. Для запуска табличного процессора EXCEL:

1. Необходимо из меню Программы выбрать пункт Microsoft Excel.
   
2. Нажать соответствующую кнопку на панели Office.
   
3. Любой из перечисленных способов.
   

   2. Электронная таблица по умолчанию называется…
   

4. Лист.
   
5. Файл.
   
6. Документ.
   
7. Любой из перечисленных.
   
8. Книга.
   

   3. Совокупность рабочих листов в EXCEL называется…
   

9. Книгой.
   
10. Листом.
    
11. Документом
    
12. Файлом.
    
13. Любой из перечисленных.
    

4. Новая рабочая книга состоит из…

1. 3 листов.
   
2. 16 листов.
   
3. 25 листов.
   
4. 30 листов.
   

   5. Для редактирования введенных ранее данных в ячейку необходимо:
   

5. Выполнить двойной щелчок по ячейке, подлежащей редактированию.
   
6. Нажать клавишу F2, предварительно выделив ячейку.
   
7. Выделить необходимую ячейку и щелкнуть мышкой в строке формул.
   
8. Все перечисленное.
   

   6. Можно ли поменять имя Лист 1 на другое?
   

9. Да, если дважды щелкнуть на ярлычке Лист 1.
   
10. Нет, так как это имя жестко закреплено.
    

    7. Столбцы в EXCEL обозначаются…
    

11. Буквами латинского алфавита.
    
12. Цифрами.
    
13. Сочетанием буквы и цифры.
    

    8. Строки в EXCEL обозначаются…
    

14. Буквами латинского алфавита.
    
15. Цифрами.
    
16. Сочетанием буквы и цифры.
    

    9. Ячейка в Excel имеет свой адрес, который определяется…
    

17. Соответствующей строкой и столбцом.
    
18. Соответствующей строкой.
    
19. Соответствующим столбцом .
    

10. В ячейках можно расположит:

1. Текст.
   
2. Числа.
   
3. Формулы.
   
4. Все перечисленное.
   

11. Совокупность нескольких ячеек называется…

1. Группой.
   
2. Листом.
   
3. Книгой.
   
4. Диапазоном.
   
5. Любое из перечисленного.
   

   12. Для выделения несмежных ячеек используется клавиша:
   

6. Ctrl.
   
7. Alt.
   
8. Shift.
   
9. Ctrl+Alt.
   
10. Любая из перечисленных.
    

13. Адреса ячеек в диапазоне разделяются:

1. Двоеточием.
   
2. Точкой с запятой.
   
3. Тире.
   
4. Точкой.
   

14. Для выделения строки (столбца) необходимо;

1. Щелкнуть мышкой по заголовку строки (столбца).
   
2. В меню Файл выбрать команду Выделить строку (столбец).
   
3. Дважды щелкнуть мышкой в любом месте строки (столбца)
   
4. Любой из перечисленных методов.
   

15. Для расположения текста в таблице в несколько строк необходимо:

1. Выбрать ФорматàЯчейкиàВыравнивание и далее поставить флажок Переносить по словам.
   
2. Выбрать ФорматàЯчейкиàРавнение и далее поставить флажок Переносить по словам.
   
3. Выбрать ФайлàЯчейкиàВыравнивание и далее поставить флажок Переносить по словам
   
   1. Все перечисленное.
      

   16. Строка формул расположена:
   

4. Непосредственно над заголовками столбцов..
   
5. В нижней части экрана.
   
6. Над строкой меню.
   
7. Над панелями инструментов.
   

17. Команда Автозаполнение используется:

1. Для ввода данных, представляющих собой некоторый ряд.
   
2. Для ввода данных, представляющих только числовой ряд.
   
3. Для ввода данных, представляющих собой только текстовый ряд.
   

   18. Любые формулы в Excel всегда начинаются:
   

4. Со знака “=”.
   
5. Со знака “-”.
   
6. Со знака ” ‘ “.
   
7. Формулы вводятся без какого-либо знака перед ней.
   

19. Если данные в активной ячейке неверны, то их можно удалить следующим способом:

1. Начать ввод новых данных, старые данные будут замещены новыми.
   
2. Нажать клавишу Del.
   
3. Из меню Правка выбрать команду Очистить.
   
4. Любым перечисленным способом .
   

   20. Для вставки нового рабочего листа необходимо:
   

5. Выделить рабочий лист, перед которым надо вставить новый и далее выбрать ВставкаàЛист.
   
6. выделить рабочий лист, за которым надо вставить новый и далее выбрать ВставкаàЛист.
   
7. Любой перечисленный способ .
   

21. Для переименования рабочего листа необходимо:

1. Дважды щелкнуть по ярлычку рабочего листа и ввести новое имя.
   
2. В контекстном меню выбрать команду Переименовать.
   
3. Выделить лист и выбрать ФорматàЛистàПереименовать.
   
4. Все перечисленное.
   

22. Сколько файлов будет занимать рабочая книга, состоящая из 18 листов?

1. 18
   
2. 3
   
3. В первой книге -16 листов, во 2 книге – 2 листа
   

23. Отличаются ли команды, выполненные с использованием контекстного меню и выполненные с использованием основного меню?

1. Нет.
   
2. Да.
   

24. Можно ли в диалоговом окне Открытие документа выполнить команду Копировать?

1. Да.
   
2. Нет .
   

25. Можно ли в диалоговом окне Открытие документа выполнить команду Удалить?

1. Да.
   
2. Нет.
   

26. Можно ли в диалоговом окне Открытие документа выполнить команду Переименовать?

1. Да.
   
2. Нет.
   

27. Если в формуле присутствует несколько диапазонов, то они между собой отделяются:

1. Двоеточием.
   
2. Точкой с запятой.
   
3. Точкой.
   
4. Запятой.
   

   28. При присвоении имени диапазону ячеек необходимо помнить, что:
   

5. Имена должны начинаться с буквы или знака подчеркивания, после буквы можно использовать любой знак кроме пробела или дефиса.
   
6. Имена могут начинаться как с буквы, так и с другого знака, главное чтобы это были буквы латинского алфавита.
   
7. В имени диапазона должны присутствовать только буквы русского алфавита.
   
8. Никаких ограничений при присвоении имени диапазону ячеек не существует.
   

29. При необходимости ввода в формулу значения из фиксированной ячейки используется:

1. Абсолютная ссылка.
   
2. Относительная ссылка.
   
3. Смешанная адресация.
   

30. Могут ли в формулах быть ссылки на ячейки другой рабочей книги?

1. Да.
   
2. Нет.
   

31. В Excel можно построить два типа диаграмм:

1. Внедренные и на отдельных листах
   
2. Внутренние и на отдельных листах
   
3. Внешние и внутренние
   
4. Внутренние и наружные
   

32. В вашем распоряжении имеется громоздкая таблица, где вам хотелось бы одновременно просмотреть столбцы, расположенные далеко друг от друга. Как это сделать, не нарушая структуры таблицы?

1. Уменьшить масштаб на экране так, чтобы на нем присутствовали все нужные столбцы.
   
2. Скрыть ненужные промежуточные столбцы с помощью команды ФорматàСтолбецàСкрыть.
   
3. Скопировать нужные столбцы на другой лист и просмотреть их там
   

33. После ввода числа в клетку Вы наблюдаете следующую картину.

В чем причина такой ситуации?

1. Не хватает ширины клетки, чтобы показать введенное число.
   
2. Число введено с ошибкой.
   
3. Число введено в защищенную клетку.
   

34. Вы вводите в клетку данные в виде 01/01/00. Какая дата будет храниться в этой клетке?

1. 1 января 1900 г.
   
2. 1 января 2000г.
   

35. Вам необходимо размножить формулу из клетки C2 так, чтобы ее копии по-прежнему обрабатывали клетку D2. Как это сделать наиболее эффективно?

1. Защитить клетку D2 от изменений и скопировать формулу в нужные клетки.
   
2. Скопировать формулу в нужные клетки и отредактировать каждую копию.
   
3. Изменить в оригинале формулы адрес D2 на адрес $D$2 и скопировать формулу в нужные клетки.
   

36. Вам необходимо в клетке F5 подсчитать сумму отношений данных из блока клеток B3:E3 и данных из блока клеток B2:E2. Как это сделать, не занимая место под промежуточные результаты?

1. Подсчитать каждое отношение в отдельной клетке, затем – отдельно сумму этих отношений, а затем скрыть клетки с промежуточными результатами.
   
2. Воспользоваться специальной функцией, выполняющей нужные вычисления.
   
3. В итоговой клетке воспользоваться формулой =SUM(B3:E3/B2:E2) и зафиксировать ее комбинацией клавиш Ctrl+Shift+Enter.
   

37. Вы защитили на листе клетки с формулами, но оставили незащищенными клетки с исходными данными для них. Изменятся ли результаты расчета формул при изменении клеток с исходными данными?

1. Да, изменятся.
   
2. Это зависит от типа формул.
   
3. Нет, не изменятся.
   

38. Вы построили диаграмму по некоторым данным из таблицы, а через некоторое время изменили эти данные. Как перестроить диаграмму для новых данных? (Укажите все верные ответы)

1. Достаточно один раз щелкнуть мышью по диаграмме.
   
2. Достаточно дважды щелкнуть мышью по диаграмме.
   
3. Пересчет в стандартном режиме произойдет автоматически.
   
4. В режиме ручных вычислений достаточно будет нажать клавишу F9
   

39. Вы часто строите однотипные диаграммы нестандартного вида для различных данных из разных таблиц. Каким образом зафиксировать этот нестандартный вид диаграмм и использовать его, не оформляя каждый раз подобную диаграмму заново?

1. Сохранить все поля одной из диаграмм как отдельные графические объекты и вставлять их всякий раз в новые диаграммы.
   
2. При создании очередной диаграммы копировать какую-либо старую диаграмму, объявить ее текущей по умолчанию и пользоваться ею при создании новых диаграмм.
   
3. Оформить очередную диаграмму, объявить ее текущей по умолчанию и пользоваться ею при создании новых диаграмм.
   

40. При подготовке к печати листа, содержащего таблицу и диаграмму, оказалось, что диаграмма печатается частями на разных страницах. Какие из перечисленных ниже приемов помогут решить эту проблему? (Укажите все верные ответы)

1. Уменьшить поля в параметрах страниц.
   
2. Изменить ориентацию.
   
3. Изменить масштаб изображения на экране.
   
4. Переместить диаграмму в другое место на листе.
   
5. Уменьшить высоту шрифта всех названий на диаграмме.
   
6. Изменить масштаб для печати листа.
   
7. Расставить на листе нужным образом жесткие разделители страниц.
   

41. В вашей таблице имеется столбец с названиями месяцев, расположенными в случайном порядке. Как отсортировать строки такой таблицы, чтобы названия месяцев в указанном столбце шли по порядку, а не по алфавиту?

1. Названия месяцев при сортировке автоматически ставятся по порядку, а не по алфавиту.
   
2. Необходимо щелкнуть по заголовку столбца с месяцами, а затем воспользоваться кнопкой Сортировка по возрастанию при нажатой клавише Shift.
   
3. Необходимо щелкнуть по заголовку столбца с месяцами и воспользоваться кнопкой ДанныеàСортировка, указав в ее параметрах порядок сортировки по месяцам.
   

42. Какое минимальное количество дополнительных таблиц необходимо подготовить, чтобы выполнить фильтрацию списка с помощью команды ДанныеàРасширенный Фильтр?

1. Дополнительных таблиц не нужно.
   
2. Необходимо оформить одну дополнительную таблицу с критериями фильтрации.
   
3. Необходимо оформить две дополнительные таблицы: одну – с критериями фильтрации и одну – для размещения результатов фильтрации.
   

43. Вы хотите построить в Excel сводную таблицу на основе сведений из внешней базы данных. Какая из команд поможет решить эту проблему?

1. Команда ФайлàОткрыть, где в поле Тип файла указан тип файлов нужной базы данных.
   
2. Команда ВставкаàОбъект.
   
3. Команда ДанныеàСводная таблица.
   
4. Команда ДанныеàПолучить Внешние данные.
   

44. Как наиболее простым способом получить данные из таблицы, созданной средствами Word, на листе Excel в пригодном для расчетов виде?

1. Набрать все данные на листе Excel вручную.
   
2. Воспользоваться командой ВставкаàОбъект и указать файл с таблицей Word.
   
3. В Word выделить таблицу и воспользоваться командой Копировать, в Excel воспользоваться командой Вставить.
   
4. Воспользоваться командой ДанныеàПолучить внешние данные.
   

45. Какой оператор не входит в группу арифметических операторов?

1. -
   
2. +
   
3. & **
   

4. ^

46. Что из перечисленного не является характеристикой ячейки?

1. Имя.
   
2. Адрес.
   
3. Размер.
   
4. Значение.
   

47. Какое значение может принимать ячейка?

1. Числовое.
   
2. Текстовое.
   
3. Возвращенное.
   
4. Все перечисленные.
   

48. Что может являться аргументом функции?

1. Ссылка.
   
2. Константа.
   
3. Функция.
   
4. Все варианты верны.
   

49. Указание адреса ячейки в формуле называется…

1. Ссылкой.
   
2. Функцией.
   
3. Оператором.
   
4. Именем ячейки.
   

50. Программа Excel используется для…

1. Создания текстовых документов.
   
2. Создания электронных таблиц.
   
3. Создания графических изображений.
   
4. Все варианты верны.
   

51. С какого символа начинается формула в Excel?

1. =
   
2. +
   
3. Пробел.
   
4. Все равно с какого символа.
   

52. На основе чего строится любая диаграмма?

1. Книги Excel.
   
2. Графического файла.
   
3. Текстового файла.
   
4. Данных таблиц.
   

53. В каком варианте правильно указана последовательность выполнения операторов в формуле?

1. + и – затем * и /.
   
2. Операторы сравнения затем операторы ссылок.
   
3. Операторы ссылок затем операторы сравнения.
   
4. / затем % .
   

54. Минимальной составляющей таблицы является…

1. Ячейка.
   
2. Формула.
   
3. Книга.
   
4. Нет верного ответа.
   

55. Для чего используется функция СУММ?

1. Для получения суммы квадратов указанных чисел.
   
2. Для получения суммы указанных чисел.
   
3. Для получения разности сумм чисел.
   
4. Для получения квадрата указанных чисел.
   

56. Сколько существует видов адресации ячеек в Excel?

1. Один.
   
2. Два.
   
3. Три.
   
4. Четыре
   

57. Что делает Excel, если в составленной формуле содержится ошибка?

1. Возвращает нуль как значение ячейки.
   
2. Выводит сообщение о типе ошибки как значение ячейки.
   
3. Исправляет ошибку в формуле.
   
4. Удаляет формулу.
   

58. Для чего используется диалоговое окно команды Форма?

1. Для заполнения записей таблицы.
   
2. Для форматирования таблицы.
   
3. Для проверки орфографии на листе.
   
4. Для фильтрации записей таблицы по условию.
   

59. Какая из ссылок является абсолютной?

1. C22
   
2. R1C2
   
3. $A$5
   
4. #A#5
   

60. Упорядочивание значений диапазона ячеек в определенной последовательности называют…

1. Форматирование.
   
2. Фильтрация.
   
3. Группировка.
   
4. Сортировка .
   

61. Если в диалоге Параметры страницы установить масштаб страницы “не более чем на 1 стр. в ширину и 1 стр. в высоту” то при печати, если лист будет больше этого размера,…

1. Страница будет обрезана до этих размеров.
   
2. Страница будет уменьшена до этого размера.
   
3. Страница не будет распечатана.
   
4. Страница будет увеличена до этого размера.
   

62. Какой командой нужно воспользоваться чтобы вставить в столбец числа от 1 до 10 500?

1. Команда ПравкаàЗаполнить.
   
2. Команда ВставкаàЯчейки.
   
3. Команда ФорматàЯчейки.
   
4. Команда ПравкаàЗаменить.
   

63. Какое форматирование применимо к ячейкам в Excel?

1. Обрамление и заливка.
   
2. Выравнивание текста и формат шрифта.
   
3. Тип данных, ширина и высота.
   
4. Все варианты верны.
   

ТРЕНИНГ УМЕНИЙ

Первое умение. Проектирование, форматирование и редактирование таблиц

ЗАДАНИЕ 1. Спроектировать электронную таблицу 1, произвести в ней расчет на основе следующего документа:

Таблица 1.

Ведомость продажи товаров в марте 200_ года

Рекомендации по выполнению

1. Спроектировать заголовок, подзаголовок и шапку документа с нумерацией граф, набирая информацию на клавиатуре и располагая ее в соответствующих колонках электронной таблицы: Номер – в колонке А, Наименование товара – в колонке В и т. д.

2. Ввести постоянную информацию (гр. 1 – 3)
произвольного содержания.

Пояснение.
При вводе порядкового номера использовать режим автозаполнения: набрать номера 1 и 2, а затем войти в режим автозаполнения и заполнить графы до 7-го номера. Для использования этого режима следует выделить ячейки с номерами: 1, 2, а затем потянуть выделенную область за правый нижний угол.

Возможен и альтернативный вариант с использованием меню: ПравкаàЗаполнитьàПрогрессияàПо столбцамàАвтозаполнение

3. Вписать формулу в первую строку графы Стоимость и скопировать ее в остальные части строки.

Внимание! Формулы писать на латыни через координаты клеток и начинать со знака равно, например, =C5*D5.

Копирование формул выполнить, потянув активную клетку, в которой расположена набранная формула, за правый нижний угол.

Ввести формулу в итоговую строку графы Стоимость.

Пояснение. При вводе формулы воспользоваться автосуммированием. Для этого надо выделить ячейки, которые надо просуммировать, и нажать символ суммирования (S) на панели инструментов.

4. Сохранить полученный макет таблицы в своей папке, воспользовавшись командой Сохранить как …, и присвоив файлу имя Макет.

5. Ввести в таблицу переменную информацию (гр. 4 – 5)
произвольного содержания – произойдет расчет.

6. Сохранить полученную расчетную таблицу, присвоив файлу имя Расчет.

ЗАДАНИЕ 2. Выполнить в файле Макет форматирование, добавить рамку и визуальные эффекты.

Рекомендации по выполнению

1. Установить денежный формат для граф Цена и Стоимость, а также числовой формат для графы Количество.

Пояснение. Сделать это можно, выделив необходимые ячейки и выполнив команду ФорматàЯчейки… вкладка Число. В появившемся окне выбрать Денежный или Числовой форматы, в качестве обозначения выбрать необходимую валюту, например, рубли и щелкнуть кнопку [ОК].

2. Сделать обрамление таблицы:

• рамка таблицы – жирная;
  
• шапка – жирная;
  
• итоговая строка – жирная;
  
• низ таблиц – двойная линия;
  
• все остальное – разграничить обычной рамкой.
  

Пояснение. Это можно сделать, воспользовавшись командой Форматà Ячейки… вкладка Граница и установив необходимый тип, толщину и цвет линии.

3. Установить следующие визуальные эффекты, воспользовавшись панелью инструментов:

• заголовок – шрифт 16, жирный, красный;
  
• подзаголовок – шрифт 14, жирный, подчеркнутый, красный;
  
• итоговая строка – шрифт 14, курсив, синий;
  
• строка нумерации граф – шрифт 14, курсив, синий;
  
• содержимое графы «Стоимость» – шрифт 14, курсив, зеленый;
  
• все остальное – шрифт 12, обычный.
  

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

4. Cохранить результаты работы в своей папке в файле Макет-формат.

5. Ввести в полученную таблицу (файл Макет-формат) новую переменную информацию произвольного содержания (гр. 4-5) – произойдет расчет.

6. Сохранить таблицу (результат расчета) в своей папке под именем Расчет-формат.

ЗАДАНИЕ 3. В файле Макет-формат выполнить удаление, вставку, замену строк и столбцов:

1. Удалить первую и вторую строки в содержательной части документа.

Пояснение. Удалить строку можно командой ПравкаàУдалить…, предварительно выделив ее, в появившемся окне выбрав строку и нажав затем кнопку [ОК].

2. Вставить:

• три строки в содержательную часть документа;
  
• столбец Единица измерения после графы Код товара;
  
• столбец Цена фактическая после графы Цена;
  
• столбец Количество фактическое после графы Количество;
  
• столбец Стоимость фактическая после графы Стоимость.
  

Пояснение. Сделать это можно, установив курсор на столбец, перед которым необходимо вставить столбец, либо на строку, сверху которой необходимо добавить строку, и выполнив команду ВставкаàСтолбцы или ВставкаàСтроки соответственно.

3. Заменить:

• в заголовке месяц и год – на текущие, т. е. сегодняшние;
  
• название графы Код – на Номенклатурный номер;
  
• название графы Цена – на Цена плановая;
  
• название графы Количество – на Количество плановое;
  
• название графы Стоимость – на Стоимость плановая.
  

Расположить слова в шапке в две строки, предварительно выделив их, а затем воспользовавшись командой:

ФорматàЯчейкаàВыравниваниеàПеренос по словам.

4. Отредактировать, отформатировать полученную таблицу, добавить недостающую постоянную информацию произвольного содержания, формулы и визуальные эффекты.

5. Сохранить отредактированную таблицу в своей папке под именем Макет-редакция.

6. Ввести новую переменную информацию произвольного содержания – произойдет расчет.

7. Сохранить расчетную таблицу под именем Расчет-редакция.

8. Перейти в таблице Макет в режим формул, воспользовавшись командой СервисàПараметры установить флажок Формулы и сохранить полученную таблицу под именем Макет-формулы.

9. На экране представить все подготовленные таблицы: Макет, Расчет, Макет-формат, Расчет-редакция, Макет-Редакция, Расчет-формат, Макет-формулы.

Задание для самостоятельной работы

1. Спроектировать таблицу Список клиентов, включив в него следующие данные:
   

• название фирмы;
  
• код;
  
• контактная персона;
  
• город;
  
• телефон;
  
• скидка (%).
  

2. Заполнить спроектированную табл. 2 Список клиентов записями, приведенного содержания, воспользовавшись формой данных.

Таблица 2

Список клиентов

фирмы

Код

Контактная персона

Город

Телефон

Скидка (%)

Интелеком

2001

Бирюкова М.Л.

Минск

0172-62-51-79

0,05

ЛЕЙСАН

2002

Ермоленко В.Г.

Киев

044-23-67-10

0,05

Техинтерторг

2201

Макарквич Д.Е.

Харьков

0572-56-6745

0,02

ЧП Гетман

2301

Гетман А.Л.

Москва

095-56-2345

0

Фоикс

2401

Мельник В.Л.

Гомель

0232-54-3490

0

Триумф-инфо

3001

Макаревич Е.Д.

Донецк

0622-57-4567

0,02

Нереида

3101

Ковалев Д.Г.

Минск

0172-45-7889

0,02

Белсервисмаш

3201

Зубович К.Д.

Москва

095-56-6758

0

ЧП Чиркун

3301

Чиркун Ю.С.

Минск

0323-50-1756

0,05

Второе умение. Проектирование диаграмм по готовой таблице

ЗАДАНИЕ 1. Спроектировать макет табл. 3 на основе данного документа и выполнить расчет.

Таблица 3

Ведомость реализации продукции по предприятию за ______ месяц 200_ г.

п/п

Дни

Молоч-ный

Мяс-ной

Колбас-ный

Бака-лея

Конди-терс

Рыб-ный

Вин-ный

Всего

 Понедельник         Вторник         Среда         Четверг         Пятница         Суббота         Воскресенье

И т о г о:

*

*

*

*

*

*

*

*

Рекомендации по выполнению

1. Спроектировать заголовок, подзаголовок (не указывая конкретные месяцы и год) и шапку документа с нумерацией граф.

Пояснение. При заполнении порядкового номера и дней недели использовать автозаполнение: ПравкаàЗаполнитьàПрогрессияàПо столбцамàАвтозаполнение.

2. Ввести постоянную информацию (графы 1 и 2).

3. Ввести формулы, используя копирование, в графе Всего и строке Итого.

Пояснение. Для заполнения строк и столбцов формулами достаточно ввести формулу в одну из ячеек, обычно первую. Затем выделить эту ячейку и выполнить команду ПравкаàКопировать. Далее нужно выделить оставшиеся ячейки, подлежащие заполнению формулами, и выполнить команду ПравкаàВставить.

4. Выполнить форматирование и ввести визуальные эффекты: шрифты, цветность, рамку, используя автоформат.

Пояснение. Для использования автоформата необходимо выделить нужную часть таблицы и выполнить команду ФорматàАвтоформат, после чего выбрать тип автоформата и нажать кнопку [ОК].

5. Сохранить спроектированный макет в своей папке под именем Диаграмма-Макет.

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

7. Сохранить расчетную таблицу под именем Диаграмма-Расчет.

ЗАДАНИЕ 2. Построить диаграммы и графики на основе данных созданной таблицы. Каждую диаграмму расположить на отдельном листе, а график – вместе с таблицей. Для всех диаграмм и графиков указать заголовки, подписи осей ОХ и ОУ и легенду.

Рекомендации по выполнению

1. Построить круговую плоскую диаграмму по графе Всего (без итога) на отдельном листе.

Пояснение. Сделать это можно, выполнив команду ВставкаàДиаграмма… и выбрав нужный тип диаграммы. Затем щелкнуть на кнопке Далее и указать диапазон данных, нажав на кнопку в конце строки, выделив нужные ячейки и повторно нажав на кнопку. После этого необходимо щелкнуть на кнопке Далее и выбрать подходящий тип легенды для диаграммы. Снова нажав на кнопку Далее нужно отметить создание диаграммы на отдельном листе и нажать кнопку Готово.

Внимание! Не забудьте указать название диаграммы, подписи осей ОХ и ОУ.

2. Построить плоскую гистограмму по дням недели, молочному и мясному отделам.

Пояснение. Сделать это можно аналогично предыдущему пункту, только следует выбрать другой тип диаграммы – плоскую гистограмму, указать другой диапазон данных, другое название диаграммы и другие подписи осей ОХ и ОY.

3. Построить объемную гистограмму по дням недели и выпуску по всем отделам.

4. Построить радар (лепестковую диаграмму) по дням недели и молочному и колбасному отделам.

5. Построить кольцевую диаграмму по всем отделам для итоговой строки.

6. Построить график по всем отделам и всем дням (без итогов) на том же листе, где и таблица.

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

Задание для самостоятельной работы

1. Спроектировать и сохранить макет таблицы 4, указав в нем: заголовок, подголовок, шапку с нумерацией граф, 25 строк с постоянной информацией, формулы в графе Годовой выпуск и итоговой строке.

Таблица 4

Выпуск продукции по месяцам 200_ года

Пояснение. Названия месяцев не сокращать и получить, используя Автозаполнение. Постоянная информация – это содержимое граф 1-3, которое следует заполнить по своему усмотрению согласно заданным элементам формата: А(15), 99, АААА).

2.
Построить график и три диаграммы разных типов с разными данными, на разных листах, используя таблицу Выпуск продукции. Все диаграммы и график описать, т.е. указать их названия, а также названия осей ОХ и ОУ.

Третье умение. Проектирование связанных таблиц

ЗАДАНИЕ 1. Спроектировать макет электронной таблицы 5 и произвести расчет на основе следующего документа:

Таблица 5

Производство изделий в 1-м квартале

Рекомендации по выполнению

1. Спроектировать на первом листе электронной таблицы макет предложенной таблицы, содержащий заголовок, подзаголовок, шапку, нумерацию граф, 10 строк постоянной информации произвольного содержания (графы 1 – 3), формулы для расчетов за квартал, формулы для расчетов в итоговой строке и визуальные эффекты.

2. Переименовать Лист1 в Макет1, щелкнув правой клавишей мыши по
вкладке
Лист1, выбрав
Переименовать
и набрав новое имя Макет1.

3. Скопировать Макет1 на Лист2, выделив
Макет1, поместив его в
буфер командой
ПравкаàКопировать
и вставив на
Лист2 командой ПравкаàВставить.

4. Заполнить скопированный макет переменной информацией произвольного содержания (графы 4-6) и произойдет расчет.

5. Переименовать Лист2 в Квартал1.

ЗАДАНИЕ 2. Спроектировать аналогичные макеты на 2, 3, 4 кварталы, заполнить другой переменной информацией и произвести расчет.

Рекомендации по выполнению

1. Скопировать Макет1 на Лист3, воспользовавшись операцией копирования таблицы Макет1 и необходимым последующим редактированием.

Замечание. Если в книге не хватает листов, то добавить их, выполнив команду: ВставкаàЛист.

2. Переименовать
Лист3
в Макет2.

3. Скопировать
Макет2
на
Лист4.

4. Заполнить скопированную таблицу Макет2 произвольной переменной информацией и произвести расчет.

5. Переименовать Лист4 в Квартал2.

6. Аналогично спроектировать макеты и расчетные таблицы для 3-го и 4-го кварталов.

ЗАДАНИЕ 3. На основе имеющихся макетов по кварталам спроектировать новый макет документа в таблице 6

Таблица 6

Производство изделий по кварталам

п/п

Наименование

изделия

Номенклатурный

номер

Произведено по кварталам, млн. руб.

За год

I

II

III

IV

1

2

…

10
       Итого *****

Рекомендации по выполнению

1. Спроектировать макет, используя операцию копирования таблицы Макет1, а затем выполняя необходимое ее редактирование.
   
2. 
   Переименовать соответствующий Лист, на котором спроектирован макет для годового расчета, в Макет-Год.
   
3. 
   Заполнить вновь созданный макет переменной информацией.
   

   Пояснение. При заполнении использовать формулы, связанные с предыдущими расчетными таблицами, например, формула для расчета выпуска одного изделия в первом квартале может быть такой: =Квартал1!G6, для второго квартала =Квартал2!G6, а для расчета годового выпуска =Квартал1G6+Квартал2!G6+Квартал3!G6+Квартал4!G6. Формулы выпуска всех остальных изделий за соответствующие кварталы можно получить из приведенных формул копированием.
   

4. Переименовать лист с полученной годовой расчетной таблицей в Расчет-Год.
   

    

   Задание для самостоятельной работы
   

5. Заполнить исходные таблицы новой переменной информацией так, чтобы в каждом квартале 2-3 вида изделий в каждом месяце квартала не производились и в соответствующих графах, в т.ч. и в графе За квартал, стояли нули.
   
6. При подсчете За год в соответствующей графе годовой таблицы поставить прочерк, воспользовавшись функцией ЕСЛИ.
   

   Пояснение. Функция ЕСЛИ выдает одно значение, если заданное условие принимает значение ИСТИНА, и другое, если оно ложно. Например: функция ЕСЛИ (G6=0;”—”;G6) выдаст прочерк, если содержимое G6=0 и выдаст само содержимое G6, если оно не равно нулю.
   

7. Представить на экране все полученные макеты и расчетные таблицы, расположив их на отдельных переименованных листах.
   

   Четвертое умение. Работа с таблицей как с базой данных: создание, добавление, сортировка данных, фильтрация данных, получение итогов
   

    

   1. Создать табл 7.
   

    

   Таблица 7
   

   Данные о клиентах брачного агентства

   Код	Пол	Фамилия	Имя	Возраст	Рост, см	Доход, $	Место жительства	Образование
   3045	Ж	Кирова	Ольга	21	168	125	Могилев	Нет	ср/о
   3046	М	Сидоров	Иван	23	185	187	Гомель	Нет	в/о
   3047	М	Климчук	Олег	35	171	243	Минск	Да	ср/о
   3048	Ж	Петрова	Мария	48	168	156	Минск	Да	в/о
   3049	М	Самков	Петр	51	174	447	Гродно	Нет	в/о
   3050	М	Котов	Юрий	32	180	311	Витебск	Да	в/о
   3051	Ж	Сомова	Елена	37	162	181	Минск	Нет	ср/о
   3052	Ж	Жукова	Инна	27	163	415	Слоним	Нет	в/о
   3053	М	Нечаев	Андрей	44	178	356	Минск	Да	ср/о
   3054	Ж	Крылова	Юлия	62	170	273	Могилев	Да	в/о

   Пояснение. При проектировании шапки таблицы, которую предстоит использовать как базу данных, старайтесь избегать объединения ячеек.
   

   2. Используя форму данных добавить в созданную таблицу следующие данные:

• Ж, Орлова, Ольга, 34, 159, 311, Минск, нет, ср/о;
  
• М, Иванов, Роман, 46, 187, 512, Бобруйск, да, в/о;
  
• М, Серов, Навел, 52, 168, З67, Гомель, да, ср/о;
  
• Ж, Зайцева, Нина, 35, 164, 300, Орша, да, в/о;
  
• Ж, Петрова, Вера, 65, 158, 250, Орша, нет, ср/о.
  

3. Удалить последнюю из добавленных строк. Сохранить полученную таблицу под именем Брак.

4. Установите курсор на любую ячейку списка (т. е. в любую ячейку таблицы). Выполните команду ДанныеàФорма. Появится диалоговое окно формы данных. Чтобы добавить новую запись в список, нажмите кнопку Добавить. Появится пустая форма данных. Введите добавляемые данные в поля ввода этой формы.

Замечание.
Чтобы перейти к редактированию очередного ноля ввода, нажимайте клавишу Таb. Чтобы вернутся к предыдущему полю ввода, нажимайте клавиши [Shift+Таb].

5.
После окончания ввода данных текущей записи нажимайте клавишу Enter. Будет появляться пустая форма данных, в которую можно ввести
следующую запись.

6. Закрыть диалоговое окно формы данных и
сохранить вновь полученную таблицу в своей папке, присвоив ей имя Брак.

7. Произвести сортировку данных таблицы:

• по одному полю (на одном уровне): расположить фамилии клиентов в алфавитном порядке. Полученные данные поместить на Лист2. Лист2 переименовать в Сорт1;
  
• по двум полям (на двух уровнях): по убыванию возраста и роста. Полученный результат пометить на новый лист, присвоив ему имя Сорт2;
  
• по трем полям (на трех уровнях): расположить фамилии клиентов в порядке возрастания возраста, убывания роста и возрастания доходов. Полученный результат поместить на новый лист под именем СортЗ.
  

8. Скопируйте полученную таблицу на новый лист.

9. Установить курсор на любую ячейку списка, который надо отсортировать, например на ячейку, где содержится слово Сидоров.

Замечание. Если вы выделите несколько записей, т. е. если нужно отсортировать только часть списка, то в появившемся окне Обнаружены данные
вне указанного списка надо выбрать пункт Автоматически расширить выделенный диапазон и нажать кнопку Сортировать.

10. Выбрать команду ДанныеàСортировка. Появится диалоговое окно Сортировка диапазона. Чтобы исключить участие в сортировке строки заголовков полей списка, выберите в окне Сортировка диапазона в группе Идентифицировать поля по переключатель Подписям (первая строка диапазона). В этом же диалоговом окне в раскрывшемся списке группы Сортировать
по выделить название поля, по которому надо сортировать список, например по фамилии. Выбрать переключатель По возрастанию или По убыванию. Нажать кнопку [ОК] или клавишу [Еntег].

11. Аналогично выполнить сортировку по двум, а затем по трем полям, скопировав предварительно исходную таблицу на новые листы.

Пояснение. Чтобы задать сортировку еще по одному или двум полям, выделите имена полей в раскрывающихся списках Затем по и В последнюю очередь, по.

Пятое умение. Фильтрация данных

ЗАДАНИЕ 1. Произвести фильтрацию данных с использованием автофильтра:

• вывести на экран данные о женщинах-минчанках. Расположить полученные данные на листе под именем Автоф1;
  
• вывести на экран сведения о мужчинах, живущих в Гомеле и имеющих квартиру. Расположить полученные данные на листе под именем Автоф2;
  
• вывести на экран сведения о клиентах, имеющих высшее образование и квартиру. Расположить полученные данные на листе под именем Автоф3;
  
• восстановить список на Листе1, т. е. отключить автофильтр.

Рекомендации по выполнению

1. Установить курсор на любую ячейку списка. Выполнить команду ДанныеàФильтрàАвтофильтр. В каждой ячейке заголовка списка появится кнопка с изображением стрелки. С помощью этой кнопки можно раскрыть список, содержащий все имеющиеся в списке значения данного поля.

3. Нажать кнопку с изображением стрелки в столбце, по значениям которого надо выбрать записи для отображения, например, Пол. Появится раскрывающийся список критериев фильтрации.

4. Выделить один из элементов в раскрывающемся списке, удовлетворяющий данному условию, например Ж.

5. Поместить полученный результат на новый лист, присвоив ему имя Автоф1.

6. Повторить три предыдущих шага, чтобы отфильтровать список по другим полям.

Пояснение. Для восстановления списка используйте команду ДанныеàФильтрàАвтофильтр.

ЗАДАНИЕ 2. Произвести фильтрацию данных, используя усиленный автофильтр:

• отобразить на экране сведения о клиентах, у которых доход >200$, но <400$. Поместить на листе под именем Фильтр1;
  
• отобразить на экране сведения о мужчинах-минчанах в возрасте >30 лет, но <45. Поместить на листе под именем Фильтр2;
  
• отобразить на экране сведения о женщинах с высшим образованием, не старше 30 лет. Поместить на листе под именем ФильтрЗ.
  

Пояснение. Усиленный фильтр позволяет при использовании автофильтра задать более одного условия.

Рекомендации по выполнению

1. Установить курсор на любую ячейку списка. Выполнить команду ДанныеàФильтрàАвтофильтр. Нажмите кнопку с изображением стрелки в столбце, по значениям которого надо отфильтровать записи. Выделить в раскрывшемся списке элемент (Условие…).
   
2. Выделить в раскрывшемся окне Пользовательский автофильтр операцию сравнения, например, больше, и ввести в поле ввода (или выделите в очередном раскрывающемся списке) значение поля записи, с которым надо произвести сравнение данных списка, например, 200.
   
3. Выбрать дополнительный критерий, установив переключатель И или ИЛИ в требуемое положение. Например, выбрать И. Аналогично задать новое условие меньше 400.
   
4. Нажать кнопку [Оk], или клавишу Еntег. Результат скопировать на новый лист, присвоив ему имя Фильтр1.
   
5. Аналогичные действия проделать для остальных пунктов задания.
   

ЗАДАНИЕ 3. Используя расширенный фильтр, вывести на экран данные:

• о женщинах с высшим образованием, имеющих квартиру. Результат поместить на лист под именем Расшир1;
  

• о клиентах, доход которых >401$, но <499$. Результат поместить на лист под именем Расшир2;
  
• о клиентах, имеющих квартиру и живущих в городах, название которых начинается на букву М. Поместить на лист под именем РасширЗ.
  

Рекомендации по выполнению

1. На
свободном месте листа создать диапазон условий (критериев), в соответствии с которым будет осуществляться выборка. Для этого скопируйте заголовки полей, по которым осуществляется выборка, например, всю шапку, в любое свободное место листа;

Внимание! Заголовки должны вставляться
копированием, а не набираться на клавиатуре.

2. В следующей строке под соответствующими заголовками полей сформировать условия (критерии), например, в графе Пол указать Ж и т.д.

Замечание. Используйте знак ?, заменяющий один любой символ. В нашем случае в качестве критерия по месту жительства, можно использовать М????, что означает любой город, начинающийся с М и содержащий пять символов.

Используйте знак *, заменяющий любое количество символов. В нашем примере в качестве критерия по месту жительства можно использовать М*, что означает город, начинающийся с буквы М.

При задании критериев знак равенства (=) для обозначения точного соответствия поля можно не использовать.

1. Для выходного диапазона сформировать строку заголовка, идентичную строке заголовка в диапазоне условий (критериев), тоже пользуясь копированием.
   
2. Установить курсор на любую ячейку в исходном списке.
   
3. Выполнить команду ДанныеàФильтрàРасширенный фильтр.
   
4. В окне Расширенный фильтр установить флажок Скопировать
   результат в другое место.
   
5. В этом же окне Расширенный фильтр щелкнуть мышкой в поле Диапазон
   условий (по красной стрелке справа) и на листе, где нарисована шапка диапазона условий, выделить мышкой строки диапазона условий вместе с заголовком. После чего вернуться в окно Расширенный фильтр, щелкнув по кнопке со стрелкой.
   
6. Снова оказавшись в окне Расширенный фильтр, щелкнуть мышкой в поле Поместить
   результат в диапазон и на листе, где расположена шапка выходного диапазона, выделить мышкой строку заголовка выходного диапазона.
   
7. Нажать кнопку ОК, или клавишу Enter. Результат расположить на листе под именем Расшир1.
   
8. Аналогичные действия выполнить и для остальных пунктов задания.
   

ЗАДАНИЕ 4. Используя возможности получения итогов, найти:

• максимальное значение дохода у мужчин, живущих в Минске и не имеющих высшего образования. Сохранить результат под именем Итоги1;
  
  
• количество клиентов, живущих в Минске. Результат сохранить под именем Итоги2;
  
• минимальное значение дохода жителя каждого города. Сохранить результат под именем ИтогиЗ.
  

   

  Рекомендации по выполнению
  

1. Отсортировать список по полю, по которому надо подвести итоги, у нас это поле Пол.
   
2. Выделить любую ячейку списка, например, Пол. Выбрать команду ДанныеàИтоги.
   
3. Выбрать поле, содержащее группы, по которым надо подвести итоги. Для этого выделить его название в раскрывающемся списке При каждом
   изменении в. Это должно
   быть поле, по которому проводилась сортировка на шаге 1, т. е. Пол.
   
4. Выбрать функцию для подведения итогов в раскрывающемся списке Операция, у нас максимум.
   
5. В списке Добавить итоги по выделить названия полей, содержащих значения, по которым надо подвести итоги. Нажать кнопку [Ок], или клавишу [Епtег].
   
6. Аналогичные действия выполните и для других пунктов задания.
   

   Замечание. Для удаления итогов из таблицы
   выбрать команду: ДанныеàИтоги. В появившемся диалоговом окне нажать кнопку Убрать все.
   

   Шестое умение. Консолидация данных, сводные таблицы
   

    

   ЗАДАНИЕ 1. Даны две таблицы. Путем консолидации данных определить для каждого предпринимателя:
   

• максимальное (минимальное) количество проданного товара в магазине (табл. 8).
  
• общее число проданных товаров (табл. 9).
  

Таблица 8

Отчет о продажах строительных материалов

Таблица 9

Владельцы магазинов

Рекомендации по выполнению

1. Выделить блок ячеек списка, например, проданный товар, и скопировать их на новый лист.
   
2. Для освоения команды консолидации при работе с несколькими областями-источниками выполнить следующую подготовку:
   

• отсортировать строки таблицы в алфавитном порядке, выполнить команду
  ДанныеàСортировка;
  
• разделить строки таблицы по наименованию магазина на несколько областей путем вставки пустых строк для чего использовать команду ВставкаàСтрока;
  
• переставить столбец Проданный товар так, чтобы он был справа от столбца ФИО предпринимателя, для этого вставить пустой столбец после столбца ФИО предпринимателя через команду ТаблицаàВставить столбец, затем выделить столбец Проданный товар, и скопировать его в пустой столбец, сначала выполнив команду ПравкаàКопировать, после чего команду ПравкаàВставить.
  

1. Вставить рабочий лист и переименовать его в Итоги консолидации:
   

• щелкнуть правой кнопкой мыши по названию предыдущего листа;
  
• выбрать команду ДобавитьàЛист;
  
• удалить название листа и напечатать свое.
  

4. Переместить курсор в место расположения итогов консолидации. Консолидация будет выполнена по категориям таблицы ФИО предпринимателя, т.е. по каждому предпринимателю будет рассчитан среднее количество проданного товара по наименованию. Для этого выполнить команду ДанныеàКонсолидация и сделать следующие установки:

• в появившемся диалоговом окне выбрать функцию Среднее;
  
• затем перейти на лист Итоги консолидации;
  
• для первой группы (области источника) определить ссылку – выделитеь блок ячеек и столбцов ФИО предпринимателя и Проданный товар.
  
• в диалоговом окне Консолидации поставить курсор в графу Ссылка и нажать кнопку Добавить и перейти к определению следующей области-источника.
  
• выбрать переключатель Использовать метки в левом столбце, т.к. консолидация будет производиться по каждому преподавателю.
  
• Выбрать переключатель Создавать связи с исходными данными
  

5. Для получения других консолидированных итогов курсор переставить в новое место с теми данными, которые вы собираетесь консолидировать и выполнить команду ДанныеàКонсолидация, определять области-источники заново не требуется.

ЗАДАНИЕ 2. По продажам товаров в приведенных выше таблицах подвести итоги по каждому предпринимателю:

• количество проданных товаров;
  
• минимальное (максимальное) количество проданного товара;
  

По каждому магазину подвести итог в разрезе проданных товаров:

• количество проданных товаров;
  
• структура прибыльности.
  

Рекомендации к выполнению

1. Вставить новый лист и переименовать в Сводные таблицы.
   
2. Выделить блок ячеек, например, таблицу №1 и скопировать их на новый лист
   
3. Установить курсор в область списка и выполнить команду ДанныеàСводная таблица
   
4. В появившемся диалоговом окне одобрить источник В списке или базе данных Excel.
   Затем нажать клавишу
   Далее.
   
5. Задать диапазон ячеек:
   

• выделить ту группу ячеек, с которой мы собираемся работать;
  
• установить курсор в диалоговое окно диапазона и нажать клавишу Далее.
  

1. Перетащить поля в нужные области диаграммы.
   

• переместить кнопки с названием ячеек, причем при перетаскивании кнопки в область Данные у нас автоматически идет подсчет итогов;
  
• для меньшей громоздкости желательно выполнить по каждому магазину в отдельности;
  
• после выполнения нажать кнопку Готово и выбрать лист, где будет находиться таблица.
  

1. Выполнить автоформатирование полученной сводной таблицы ФорматàАвтоформатирование
   
2. Внести изменения в исходные данные и выполнить команду ДанныеàОбновить данные.
   
3. 
   Аналогичным образом строятся и другие сводные таблицы.
   

   Задание для самостоятельной работы
   

   1. Исследовать зависимость различных комбинаций клавиш между собой с помощью возможностей Excel. Для исследования данной зависимости построить табл. 10.
   

   
   Таблица 10
   

   Наименование товара	Цена товара	Количество товара	Стоимость товара
   Болты	31	345
   Гайки	12	234
   Итого

   2. Построить приведенную в условии таблицу и внести в нее формулы, позволяющие произвести вычисления: стоимости товара, количества товара и суммы денег, потраченной на его приобретение.
   

   3. Формулы могут быть связаны между собой, для изучения зависимости одной формулы от другой выполнить команду СервисàЗависимостиàПанель зависимостей. Появится панель с пиктограммами, отражающими различные зависимости. Нажимая эти пиктограммы, выясните, какая зависимость существует между ячейками.
   

   Седьмое умение. Создание макроса
   

   ЗАДАНИЕ 1. Создать макрос, форматирующий ячейки таблицы и продемонстрировать его работу
   

   Рекомендации по выполнению
   

    

   1. Начать запись макроса. Для этого:
      

• в меню Сервис выполнить команду МакросàНачать запись. Откроется диалоговое окно Запись макроса;
  
  • в поле Имя макроса этого окна в качестве имени набрать любое, например, Таблица
    (имя не должно содержать пробелов);
    
• 
  в поле Описание ввести или отредактировать описание макроса, например, Форматирует ячейки. Щелкнуть по кнопке [Ок].
  

• Выполнить действия, которые должен записать макрос. Для этого:
  
  • выделить весь рабочий лист, щёлкнув верхний левый угол таблицы;
    
  • выполнить команду ФорматàЯчейки, в появившемся диалоговом окне Формат ячеек выбрать вкладку Шрифт, и в открывшемся диалоговом окне выполнить следующие действия:
    
    • в поле Шрифт выбрать MS Serif;
      
    • в поле Начертание – Полужирный;
      
    • в поле Цвет – синий;
      
    • в поле Размер – 14;
      
  • щелкнуть по кнопке [Оk].
    
  • выполнить команду ФорматàЯчейки, в появившемся диалоговом окне Формат ячеек
    выбрать вкладку Граница и в открывшемся диалоговом окне выбрать тип линии и ее цвет, например: жирная, зеленая.
    
  • щелкнуть по кнопке [Ок].
    

3.Остановить запись макроса,
выполнив команду СервисàМакросàОстановить запись, или щелкнуть по кнопке Остановить запись на специализированной панели для записи макроса.

4. Перейти на новый лист.

5. Создать таблицу 11, следующей формы:

Таблица 11

6. Продемонстрировать работу макроса. Для этого:

• выделить любую ячейку таблицы;
  
• выполнить команду СервисàМакросàМакросы;
  
• из списка макросов в появившемся окне Макрос выбрать созданный макрос и активизировать его.
  
• нажать кнопку Выполнить.
  

На экране появится таблица, приведенная в пункте 5, с учётом выбранных в пункте 2 шрифта, начертания, цвета, размера, типа и цвета рамки.

ЗАДАНИЕ 2. Создать макрос, оформляющий следующий столбец:

Продемонстрировать работу макроса, назначив ему «горячие» клавиши.

Рекомендации по выполнению

1. Перейти на следующий лист.

2. Выполнить команду СервисàМакросàНачать запись.

3. Назначить макросу имя, например, Столбец
и сделать его описание, например, Оформление столбца.

4. Нажать на кнопку [Оk].

5. Создайть приведенный в условии задачи столбец.

6. Остановить запись макроса, выполнив команду

СервисàМакросàОстановить запись, или щелкнув по кнопке Остановить запись на специализированной панели для записи макроса.

7. Продемонстровать работу макроса с помощью назначения «горячих» клавиш на клавиатуре. Для этого:

• перейти на новый лист;
  
• выполнить команду СервисàМакросàМакросы;
  
• активизировать нужный макрос в списке макросов;
  
• нажать кнопку Параметры и в открывшемся диалоговом окне назначить сочетание клавиш, введя удобную для вас алфавитно-цифровую клавишу в сочетании с клавишей [Ctrl], например: [Ctrl + Z] и нажать [Ок].
  

8. Запустить макрос, нажав на назначенных вами «горячих» клавиш. На экране появится столбец, приведенный в задании.

ЗАДАНИЕ 3. Создать макрос, оформляющий произвольную таблицу, и продемонстрировать работу макроса следующим способом – назначение ему графического объекта.

Рекомендации по выполнению

1.
Создать макрос Группа, оформляющий произвольный таблицу, например, содержащую сведения о группе (ФИО, год рождения, место жительства и т. д.), аналогично заданию 2, пункты 1 – 6.

2. Назначить макросу графический образ и продемонстрировать работу макроса, для этого:

• перейти на новый лист;
  
• установить указатель мыши на любой панели инструментов, нажать правую кнопку мыши и выбрать команду Рисование (если она не выбрана);
  
• щелкнуть по любой кнопке панели рисования на панели инструментов и создать графический объект, например, небольшую окружность;
  
• установить указатель мыши внутри объекта, нажать правую кнопку и выбрать в контекстном меню команду Назначить макрос;
  
• когда Excel выведет диалоговое окно Назначить макрос, выбрать макрос Группа, который выполнит вставку таблицы и нажать [Ок];
  
• снять выделение нарисованного объекта (окружности);
  
• зьпустите макрос, подведя указатель мыши к границе объекта (окружности) и при изменении вида указателя нажать левую кнопку мыши. На экране появится созданная таблица.
  

ЗАДАНИЕ 1. 1. Создать макрос, проектирующий следующую форму документа и продемонстрировать его работу, присвоив макросу имя
Документ

Зачетно-экзаменационная ведомость

Экзамен по ______________

группы _____ ______ курса ___

2. Продемонстрировать совместную работу ранее созданных макросов.

3. Перейти на новый лист.

4.Запустить макрос Документ, проектирующий документ «Зачетно-экзаменационная ведомость»

5. Запустить макрос Столбец, проектирующий столбец «Номер»

6. Запустить макрос Таблица, форматирующий ячейки таблицы.

ЗАДАНИЕ 2. Создать макрос по проектированию табл. 12 многоразового использования.

Таблица 12

Таблица многоразового использования

Фирма

Выручка

Расходы

Налоги

Зарплата

Чистая прибыль

Покупка сырья

Аренда помещений

Проценты по кредитам

СОДЕРЖАНИЕ

1. НАЗНАЧЕНИЕ И КЛАССИФИКАЦИЯ ТАБЛИЧНЫХ ПРОЦЕССОРОВ    2

Вопросы для самоконтроля    2

2. ИНТЕРФЕЙС ТАБЛИЧНОГО ПРОЦЕССОРА    2

MICROSOFT EXCEL    2

Вопросы для самоконтроля    2

3. ТЕХНОЛОГИЯ СОЗДАНИЯ РАБОЧЕЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ    2

3.1. Ввод, редактирование и форматирование данных    2

3.2. Форматирование содержимого ячеек    2

3.3. Форматирование строк и столбцов    2

3.4. Форматирование ячеек (областей) рабочего листа    2

3.5. Сохранение таблицы на диске    2

Вопросы для самоконтроля    2

4. ТЕХНОЛОГИЯ ВЫЧИСЛЕНИЙ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ    2

4.1. Редактирование формул    2

4.2. Ссылки абсолютные и относительные    2

4.3. Копирование содержимого ячеек    2

4.5. Стандартные функции    2

4.6. Средства контроля    2

4.7. Подготовка рабочей книги к печати    2

Вопросы для самоконтроля    2

5. ТЕХНОЛОГИЯ ПОСТРОЕНИЯ ДИАГРАММ    2

Вопросы для самопроверки    2

6. СПЕЦИАЛЬНЫЕ СРЕДСТВА ОБРАБОТКИ ДАННЫХ    2

6.1. База данных    2

6.2. Создание списков с вычисляемыми полями    2

6.3 Выборки данных по группе критериев    2

6.4. Фильтрация данных в списке    2

6.5. Фильтрация данных в базе данных (списке)    2

6.7. Вычисление промежуточных итогов    2

6.8. Консолидация данных    2

6.9. Макросы    2

Вопросы для самоконтроля    2

ЛИТЕРАТУРА    2

ГЛОССАРИЙ    2

ТЕСТЫ    2

ТРЕНИНГ УМЕНИЙ    2

Учебное издание

Бородина Алла Ивановна

Крошинская Лариса Израйлевна

Сапун Оксана Леонидовна

Основы информатики

и вычислительной техники

Прикладное программное обеспечение.

Табличные процессоры

Компьютерная верстка О.Н. Якубович

Подписано в печать 26.11.2004 г.

Формат 60х84 ¹/₁₆. Печать офсетная. Гарнитура «Таймс».

Усл. печ. л. 2,15. Уч.-изд. л. 3,43. Тираж 100 экз. Заказ № 56.

ООО «БИП-С Плюс». 220004, г. Минск, ул. Короля, 3.

Лицензия №02330/0133317 от 09.06.2004 г.

Размножено на ризографе для внутреннего использования

Метки текущей записи:

bip, Microsoft Excel, Белорусский Институт Правоведения, БИП, Бородина, вопрос, документы, закон, информатика, книга, Макрос, Макросы, Налог, образование, Основы информатики и математики, Понятие, права, право, признак, раздел, Симон, спор, указание, читы, ЭВМ, экономист

Автор статьи: Дмитрий

Дмитрий написал 6135 статей.

Больше из этой рубрики

• Самый крутой киносайт : https://kino-24.su/
• Мир танков World of Tanks: моды, читы
• Введение в дисциплину «Основы информатики и вычислительной техники»
• Защита деловой информации. Программные меры защиты информации
• Системы программирования

Больше этого автора

• Понятие и виды векселей
• При выборе короля
• Обучение в Чехии: вратa в европейское образование
• Sen Chuck Grassleys Office Has Produced twenty Married Couples
• Виды покера на костях