Учеб_мет_пос_Пустовалова_Кишкурно

Формулу со знаком суммы нужно создать с помощью Microsoft Equation 3.0. Фигурные скобки вставить с использованием команды Вставка/Символ. В таблице с контрольным просчетом для оформления использовать объединение ячеек, а для подсчета суммы – команду

Формула из пункта меню Таблица.

3. ПРИЛОЖЕНИЕ MICROSOFT EXCEL

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

Основную часть экрана занимает рабочая область, представляющая собой сетку, столбцы которой обозначены буквами латинского алфавита, а строки – арабскими цифрами. На пересечении строк и столбцов образуются клетки – ячейки, имеющие свое имя (адрес), который состоит из буквы столбца и номера строки, например, А2, В7, С34 и т. д.

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

Документ, созданный в Excel, называется книгой (Book) и при открытии имеет имя «Книга1». В состав книги входят рабочие листы (Worksheets), называемые «Лист1», «Лист2» и т. д. Листы можно переименовывать, удалять и добавлять новые с помощью контекстного меню либо используя соответствующую команду: Правка/Удалить Лист, Вставка/Лист, Формат/Лист/Переименовать.

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

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

ВExcel существует три типа данных: текст, числовые данные, формулы. Тип данных идентифицируется по первому вводимому символу. Формула всегда должна начинаться со знака «=». Для ввода данных в ячейку надо подвести к ней курсор мыши, ввести нужную информацию и нажать клавишу ввода <Enter>.

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

21

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

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

више <Ctrl>.

Выделение. Чтобы выделить нужную ячейку, необходимо щелк-

нуть по ней мышью. Для выделения нескольких несмежных блоков ячеек следует выделить один блок, нажать клавишу <Ctrl> и, не отпуская ее, выделить другие блоки ячеек. Для выделения смежных блоков ячеек надо щелкнуть мышью на начальном блоке и, удерживая нажатой клавишу <Shift> щелкнуть на последнем блоке. Чтобы выделить целиком столбец/строку таблицы, необходимо щелкнуть мышью по заголовку столбца/строки. Для выделения рабочего листа следует щелкнуть по кнопке, находящейся на пересечении заголовков строк и столбцов. Для выделения нескольких листов надо нажать клавишу <Ctrl> и, не отпуская ее, щелкнуть по ярлычкам листов.

Операции со строками и столбцами. Для вставки столбца/стро-

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

Удалять можно как содержимое строк и столбцов, так и целиком строки и столбцы. Для очистки содержимого ячеек проще всего использовать клавишу <Del>. Можно удалить интервал ячеек с помощью команды Del, предварительно его выделив.

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

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

ки ширины столбца (строки) на самый длинный текст необходимо дважды щелкнуть мышью на границе между столбцами (либо на ниж-

22

ней границе строки). В этом случае высота строки будет ориентироваться на самые высокие в этой строке данные.

Для изменения высоты и ширины нескольких строк и столбцов одновременно надо их выделить и выполнить команду Формат/

Строка (Столбец)/Автоподбор высоты (Автоподбор ширины).

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

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

3.2. Форматирование таблицы

Процесс форматирования сводится к установке требуемых параметров для ячеек и областей рабочего листа. Для изменения параметров используются либо кнопки панели Форматирование, либо команда Формат/Ячейки с последующим выбором соответствующих вкладок (Число, Выравнивание, Шрифт, Граница, Вид, Защита).

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

Для изменения цвета фона и цвета линий можно использовать как кнопки панели Форматирование, так и команды меню Формат или контекстного меню.

Кнопка Внешние границы на панели Форматирование служит для прорисовки рамок в таблице.

Использование команды Формат/Условное форматирование…

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

23

вводе положительных чисел – один цвет чисел, при вводе чисел, превышающих некоторое значение, – другой цвет и т. д.

Для оформления заголовков таблицы удобно пользоваться инст-

рументом Объединить и поместить в центре на панели инструментов Форматирование или во вкладке Выравнивание установить флажок Объединить ячейки и флажок Переносить по словам.

В Excel 2007 можно быстро форматировать данные таблиц на листе, используя темы и конкретные стили. Тема представляет собой заранее определенный набор цветов, шрифтов, линий и эффектов заливок, который можно применить ко всей книге или к ее отдельным элементам, например, диаграммам или таблицам. Для очистки содержимого ячеек служит кнопка Очистить на вкладке Главная. На этой же вкладке на-

ходятся инструменты групп Шрифт, Выравнивание, Число, Ячейки,

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

ке Главная выбрать кнопку Условное форматирование и затем кнопку

Создать правило, где можно выбрать тип правила и задать параметры. Фиксирование заголовков. Если таблица большая, то при верти-

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

но/Снять закрепление областей.

В Excel 2007 закрепить области можно с помощью команды За-

крепить области на вкладке Вид.

3.3. Вычисления в Excel

Выполнение всех математических расчетов выполняется с помощью формул. В формулах используются адреса ячеек, состоящие из буквы столбца и номера строки, например, А2, В7, С34 и т. д. При вводе формул надо соблюдать следующие правила:

все формулы начинаются со знака «=»;

вадресе ячеек используют только латинские буквы;

вадресе ячеек можно указывать как одну ячейку, так и диапазон, при этом используются символы «:» – диапазона и «;» – объединения;

арифметические операции обозначаются символами: «*» – умножение, «/» – деление, «+» – сложение, «–» – вычитание, «^» – возведение в степень;

24

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

Например:

=A2*2,2+СУММА(С1:С10)

=МАКС(A1:D4;F1:H4)

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

Ссылки на ячейки в формулах. Существуют относительные, абсо-

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

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

Ссылки могут быть также смешанными. Если нужно зафиксировать столбец, то знак $ ставится перед буквой столбца, например, $А7. Если необходимо зафиксировать строку, то знак $ ставится перед номером строки, например, А$7.

Кроме ссылок на ячейки текущего рабочего листа, в формулах и функциях могут быть ссылки на ячейки других рабочих листов текущей рабочей книги (внутренние ссылки) или другой рабочей книги (внешние ссылки). Ссылка на данные другого рабочего листа имеет вид:

Имя рабочего листа!Имя ячейки

Например: Лист1!A1

Объемные ссылки – это ссылки на ячейки диапазона листов в книге. Например, формула: =СУММ(Лист1:Лист6!В1:В20) вычисляет сумму всех значений из диапазонов В1:В20, расположенных на шести листах.

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

[Имя книги]Имя рабочего листа!Имя ячейки Например: [Книга2]Лист2!D5

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

25

“C:\Имя папки\[Имя книги.xls]Лист5”!$A$3

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

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

Одна из самых простых и часто используемых в Excel функций –

это функция автоматического суммирования. Кнопка Автосумма находится на панели Стандартная.

Логические функции. Предназначены для проверки выполнения условия или для проверки нескольких условий. К ним относятся функции: ЕСЛИ, И, НЕ, ИЛИ.

Функция ЕСЛИ используется для выбора направления вычислений. Например: = ЕСЛИ (Е3>2; 0,5*D3; 0)

Здесь, если условие Е3>2 выполняется, то содержимое ячейки, в которой приведена эта формула, равно 0,5*D3. Если условие не выполняется, то содержимое ячейки равно 0.

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

#ИМЯ? – Excel не смог распознать имя, использованное в формуле;

#ДЕЛ/0! – в формуле делается попытка деления на нуль;

#ЗНАЧ! – использован недопустимый тип аргумента;

#Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;

#ПУСТО! – неверно указано пересечение двух областей, которые не имеют общих ячеек;

#ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;

#ЧИСЛО! – нарушены правила задания операторов, принятые в математике.

Сортировка и фильтрация данных. Содержимое выделенных ин-

тервалов можно сортировать с помощью кнопок Сортировка по воз-

26

растанию/убыванию на панели Стандартная или с помощью команды Данные/Сортировка….

При использовании команды Данные/Фильтр/Автофильтр в верхнем ряду данных таблицы появляются небольшие кнопки, нажав на которые можно задать условия выборки данных. При записи условий выборки для текста надо учитывать, что один неизвестный символ обозначается – «?», а несколько – «*».

В Excel 2007 можно сортировать данные по цветам и по уровню (вплоть до 64). Можно также фильтровать данные по цветам или датам, отображать более 1000 элементов в раскрывающемся списке Автофильтр, выделять несколько элементов для фильтрации и фильтровать данные в сводных таблицах.

3.4. Создание диаграмм. Вставка объектов и печать

Чтобы построить диаграмму, надо:

–выделить зону информации, для которой будет строиться диаграмма;

–нажать кнопку Мастер диаграмм на панели Стандартная, вы-

брать вид диаграммы и нажать кнопку Далее;

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

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

В электронных таблицах Excel 2007 для создания диаграмм используется группа инструментов Диаграммы на вкладке Вставка.

Вставка объектов. На рабочих листах могут быть размещены форму-

лы, созданные с помощью Microsoft Equation. Для оформления текста можно воспользоваться Мастером текста WordArt. Работа с этими объектами построена по тем же правилам, что и в текстовом редакторе Word.

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

27

Печать. Перед печатью следует предварительно просмотреть подготовленный документ с помощью кнопки Предварительный про-

смотр или команды Файл/Предварительный просмотр. Увеличен-

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

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

Печать таблицы осуществляется по команде Файл/Печать или при щелчке по кнопке Печать на панели Стандартная.

3.5.Содержание заданий

1.Создать таблицу, представленную на рис. 3.1.

Зарплата работников за январь

Номер	Фамилия

1

2

3

4

5

Сумма

Среднее

Должность	Стаж	Оклад	Премия	Надбака за стаж	Итого	Налоги	Полуить	Доля

Рис. 3.1. Вид таблицы задания

2.Заполнить произвольно поля таблицы Фамилия и Должность. Стаж задать в диапазоне 0–30 лет. Оклад: 400 000–900 000 руб.

3.Премия составляет 50% оклада.

4.Надбавка за стаж вычисляется на основании стажа. Если стаж больше 10 лет, тогда надбавка равна 20% от оклада, иначе – 0. Здесь используется встроенная функция ЕСЛИ.

5.Налоги составляют 20% от значения поля Итого.

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

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

28

8.Построить столбчатую гистограмму для столбцов Фамилия и Получить. В легенде отобразить фамилии работников.

9.На 2-м и 3-м листах создать аналогичные таблицы за февраль и март.

10.Используя объемные ссылки, увеличить оклад за февраль на 10%, а за март на 5% по сравнению с февралем.

11.На 4-м листе создать таблицу с полями Месяц и Всего получить для директора (или другого специалиста).

12.Построить график зарплаты директора фирмы за январь, февраль и март.

4. ИСПОЛЬЗОВАНИЕ VBA В СРЕДЕ EXCEL. ЛИНЕЙНЫЕ ПРОГРАММЫ.

VBA (Visual Basic for Application) − это объектно-ориентиро-

ванный язык программирования, встроенный во все программы Microsoft Office. Язык VBA использует понятие объектов: (рабочая книга (Workbooks), рабочий лист (Worksheets), ячейки (Cells), об-

ласть (Range), элементы управления на рабочих листах).

4.1. Элементы управления

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

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

Для размещения элемента управления на рабочем листе в Excel 2003 надо вызвать панель инструментов Элементы управления (ко-

мандой Вид/Панели инструментов/Элементы управления), активи-

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

Надпись, шрифт и цвет надписи и т. д. можно изменить, открыв окно свойств нажатием на кнопку Свойства на панели инструментов Элементы управления (рис. 4.1). В левом столбце окна свойств

29

написаны названия свойств, в правом – их значения. Для изменения надписи используется свойство Caption, для изменения шрифта надписи – Font, для изменения цвета букв – ForeColor, для изменения цвета кнопки – BackColor и т. д. Значение свойства Name, определяющее имя элемента, задается только латинскими буквами.

–переход в режим Конструктора (редактора VBA)

–активизация окна Свойства

–переход в окно Исходный текст (программы)

–флажок

–поле

–кнопка

–переключатель

–простой список

–комбинированный список

–выключатель

–счетчик

–полоса прокрутки

–надпись

–рисунок

–другие элементы

Рис. 4.1. Панель Элементы управления

Чтобы разместить элементы управления на рабочем листе в Excel 2007, необходимо придерживаться следующего порядка действий. Вначале надо поместить на ленту вкладку Разработчик. Для этого надо на-

жать кнопку Оffice в левом верхнем углу приложения, в появившемся окне нажать кнопку Параметры Excel и в следующем появившемся окне поставить галочку у надписи Показывать вкладку «Разработчик» на ленте. Затем следует активизировать данную вкладку на ленте,

щелкнуть по кнопке Вставить элементы управления и на панели

Элементы ActiveX выбрать нужный элемент управления.

Для записи процедуры, которая связывается с некоторым элементом управления, необходимо:

перейти в режим конструктора, щелкнув по кнопке Режим кон-

структора на панели инструментов Элементы управления (если этот режим не установлен первоначально);

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

30