Кораблина Т.В., Ляховец М.В., Бабичева Н.Б., Фетинина Е.П. Решение задач информатизации с помощью MS Excel Часть I - файл n1.doc

Кораблина Т.В., Ляховец М.В., Бабичева Н.Б., Фетинина Е.П. Решение задач информатизации с помощью MS Excel Часть I
Скачать все файлы (3739 kb.)

Доступные файлы (1):
n1.doc3739kb.01.04.2014 05:51скачать

n1.doc

  1   2   3


Институт информационных технологий и автоматизированных систем

Кафедра систем информатики и управления


Решение задач информатизации с помощью MS Excel

Часть I

Основы работы в MS Excel

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


Новокузнецк
2012
Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования

«Сибирский государственный индустриальный университет»
Кафедра систем информатики и управления

Решение задач информатизации с помощью
MS Excel

Часть I

Основы работы в MS Excel

Методические указания к выполнению практических

и самостоятельных работ


Новокузнецк

2012

УДК 004.9 (07)

Р-470
Рецензент

кандидат технических наук,

доцент кафедры информационных технологий в металлургии

Кожемяченко В.И.


Р-470 Решение задач информатизации с помощью MS Excel. Часть I. Основы работы в MS Excel: метод. указ. / Сиб. гос. индустр. ун-т ; сост. Н.Б. Бабичева, Т.В. Кораблина, М.В. Ляховец, Е.П. Фетинина. – Новокузнецк : Изд. центр СибГИУ, 2012. – 45 с., ил.


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

Предназначены для студентов всех форм обучения направления подготовки 230700.62 «Прикладная информатика».






ОГЛАВЛЕНИЕ


ОГЛАВЛЕНИЕ 3

Тема 8 Работа с примечаниями 45



ВВЕДЕНИЕ

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

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

Быстродействие программы зависит в значительной мере от объёма памяти.

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

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

В Exсel реализован первичный статистический анализ с выводом результатов в отдельную таблицу.

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

Тема 1 Общие сведения об Excel

Основные вопросы:

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

  1. Щелкнуть мышью на кнопке Пуск, которая находится в левом нижнем углу экрана.

  2. В открывшемся меню выделить элемент Все программы или Программы.

  3. В появившемся подменю выбрать последовательно элементы MicrosoftOffice ?MicrosoftExcel.

При запуске Excel автоматически создается новая книга под названием Книга1.

Демонстрационная задача

Запустите электронную таблицу Excel. На экране откроется рабочее окно Excel (рисунок 1.1). Некоторые его элементы присущи всем программам Windows, например, кнопки – свернуть, развернуть, восстановить, закрыть. Остальные есть только в окне Excel. Главный элемент пользовательского интерфейса MicrosoftExcel представляет собой ленту, которая идет вдоль верхней части окна.

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

Внешний вид ленты зависит от ширины окна: чем больше ширина, тем подробнее отображаются элементы вкладки. На рисунках приведено отображение ленты вкладки Главная при разной ширине окна.

Вся рабочая область окна занята чистым рабочим листом, разделённым на отдельные ячейки (рисунок 1.1). Столбцы озаглавлены буквами, строки – цифрами.

Щёлкните мышью на любой ячейке листа, таким образом, вы его пометите серой рамкой. Слева в строке формул, расположенной под панелями инструментов, в поле имени высветится адрес текущей ячейки, например, А1.

Один лист содержит 256 столбцов и 16386 строк.

С
Поле имени

Имя файла

рабочей книги

Лента вкладки Главная

права и снизу рабочего листа находятся полосы прокрутки. Для перемещения построчно и поколоночно щёлкните по соответствующей кнопке прокрутки . Для более быстрого перемещения по рабочему листу надо «схватить» бегунок мышью и переместить в нужном направлении.

line 264


line 260line 274



Текущая

ячейка
line 276
Строка формул
line 278
Заголовок

столбца
line 280
Полосы вертикальной и горизонтальной прокрутки
line 282 line 283
Заголовок

строки
line 285
Ярлычки

листов
line 287 line 295 line 297 rectangle 453


Маркер разделения

Маркер


Рисунок 1.1 – Рабочее окно Excel

Слева от полосы горизонтальной прокрутки – маркер (рисунок 1.1), с помощью которого можно изменить её длину и количество одновременно доступных ярлычков листа.

Окно рабочей книги состоит из нескольких рабочих листов, ярлычки которых: Лист1, Лист2, … находятся внизу окна рабочей книги. Нужный рабочий лист можно выбрать, щёлкнув мышью на соответствующем ярлычке.

Если на экране не высвечиваются ярлычки рабочих листов, то нажмите Кнопку «Office» и в нижней части выпавшего меню выберите команду Параметры Excel. В появившемся окне Параметры откройте раздел Основные и установите необходимое количество листов.

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

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

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

  1. Нажмите кнопку Настройка панели быстрого доступа.

  2. В меню выберите наименование необходимого элемента (рисунок 1.2). Элементы, отмеченные галочкой, уже присутствуют на панели.



Рисунок 1.2 – Настройка панели быстрого доступа

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

  2. В разделе Настройка окна Параметры Excel в раскрывающемся списке Выбрать команды из: выберите вкладку, в которой расположен добавляемый элемент, затем выделите элемент в списке и нажмите кнопку Добавить.



Рисунок 1.3 – Добавление элемента

Тема 2 Ввод данных и работа с ними

Основные вопросы:

В ячейках таблицы могут содержаться числа, текстовая информация, формулы. Формулы, вводимые в ячейки, могут содержать такие элементы:

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

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

Таблица 2.1 – Знаки основных операций

Знак

Операция

Знак

Операция

/\

Возведение в степень

=

Равно

*

Умножение

<

Меньше

/

Деление

<=

Меньше или равно

+

Сложение

>

Больше



Вычитание

>=

Больше или равно

&

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

<>

Не равно


Основной принцип работы с данными в таблицах Excel:

При выполнении расчетов используем не числа, а адреса ячеек,
в которых эти числа хранятся!


Демонстрационная задача

Создайте следующую таблицу (таблица 2.2) на листе 1 (Замечание: чтобы в формуле ввести адрес ячейки, из которой берутся данные, достаточно щелкнуть по ней мышью).

Таблица 2.2 – Исходные данные для демонстрационной задачи

Адрес

Содержимое ячейки

А1

5

В1

10

С1

=А1+В1

После ввода формулы в ячейке С1 вы увидите не формулу, а результат (рисунок 2.1).



Рисунок 2.1 – Результат расчёта

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

  1. Выделить ячейку или несколько ячеек курсором, удерживая левую кнопку мыши.

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

  3. Выбрать ячейку (или интервал ячеек), с которой будет начинаться вставка.

  4. Выбрать на вкладке Главная или в контекстном меню, появляющемся при нажатии правой кнопки мыши, команду Вставить.

Перенесите содержимое ячейки А1 в ячейку А2.

Для копирования данных:

  1. Выделить ячейку или интервал ячеек курсором.

  2. Выбрать на вкладке Главная или в контекстном меню, появляющемся при нажатии правой кнопки мыши, команду Копировать.

  3. Выбрать ячейку (или интервал ячеек), с которой будет начинаться вставка.

  4. Выбрать на вкладке Главная или в контекстном меню, появляющемся при нажатии правой кнопки мыши, команду Вставить.

Скопируйте содержимое ячейки А2 в ячейки А1 и А3.

Для процесса заполнения ячеек:

  1. Выделить ячейку или интервал ячеек.

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

Заполните интервал ячеек С2:С4 содержимым ячейки С1.

Адреса ячеек в Exel бывают относительные и абсолютные

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

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

  • Чтобы задать абсолютный адрес ставится значок $ перед той частью адреса, которую менять не надо.

Например, в вышеприведённой формуле С1=А1+В1, если адрес А1 меняться не должен, тогда в ячейке с данной формулой нужно написать: = $А$1+В1.Для быстрого задания абсолютного адреса после ввода адреса ячейки нажать клавишу F4.

Замените в ячейке С2 адрес А2 на $A$2 и заполните интервал D2:F4 содержимым ячейки С2.

Упражнение 1

  1. Создайте таблицу (рисунок 2.2).

  2. В ячейки С1чС3 введите соответствующие формулы.

  3. Заполните интервал D1:G1 содержимым ячейки С1.



Рисунок 2.2 – Пример таблицы упражнения 1

  1. Заполните интервал D2:G3 содержимым интервала ячеек С2:С3.

  2. Перенесите интервал С2:С3 в I2:I3.

  3. Скопируйте содержимое ячейки F1 в интервал G2:G3.

  4. Заполните интервал ячеек С4:С7содержимым ячейки С1 так, чтобы адрес ячейки В1 в формуле не менялся.

  5. Скопируйте ячейки I2:I3 в ячейки J2:J3 так, чтобы адреса ячеек В1и В2 в формулах не менялись.

Тема 3 Составление и оформление таблиц

Основные вопросы:

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

Если необходимо выделить строки, столбцы или интервалы,

расположенные не подряд, нужно на клавиатуре нажать клавишу Ctrl и, удерживая её, мышью отметить нужные ячейки или интервалы.

oval 304line 307
Помечается весь лист


Рисунок 3.1 – Выделение фрагментов листа

Для установки форматов ячеек можно использовать инструменты ленты вкладки Главная (рисунок 3.2):



Рисунок 3.2 – Инструменты форматирования на вкладке Главная

Демонстрационная задача

Введите данные в ячейки (таблица 3.1):

Таблица 3.1 – Исходные данные демонстрационной задачи

Адрес

Содержимое ячейки

Адрес

Содержимое ячейки

А1

Занятие 2

F3

Итог

А2

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

В4

100

А3

Квартал

В5

98

А4

План

С4

100

А5

Факт

С5

115

А6

%

D4

110

В3

Март

D5

120

С3

Июнь

E4

112

D3

Сентябрь

E5

106

Е3

Декабрь

В6

=В5/В4

Заполните интервал C6:F6 формулой из ячейки В6. Чтобы получить итог, выделить интервал В4:Е4 и выбрать в сроке стандартных инструментов кнопку автосуммирования. В ячейке F4 вы получите итоговую сумму. Также получите сумму и в ячейке F5.

Для установки высоты и ширины ячеек предлагаются способы:

  1. Захватить мышью разделитель между строк или столбцов и переместить его на нужное расстояние.

  2. Выбрать на ленте вкладки Главная в разделе Ячейки команду Формат и указать нужную высоту или ширину.

Установка параметров шрифта. Установка параметров шрифта изменяет отображение данных на листе и при печати. Отображение данных в строке формул не изменяется.

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

Для установки параметров шрифта используют элементы группы Шрифт вкладки Главная, мини-панель инструментов , а также вкладку Шрифт диалогового окна Формат ячеек.

Для изменения размера шрифта можно воспользоваться также кнопками Увеличить размер и Уменьшить размер вкладки Главная .

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

  1. Выделите ячейку или диапазон ячеек.

  2. Щелкните по стрелке кнопки Цвет текста вкладки Главная и выберите требуемый цвет шрифта. При наведении указателя мыши на выбираемый цвет срабатывает функция предпросмотра, и содержимое фрагмента листа отображается указанным цветом шрифта.

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

Начертание шрифта. Начертание определяет особенности внешнего вида символов. Можно установить полужирное начертание и курсив.

  1. Выделите ячейку или диапазон ячеек.

  2. Для установки полужирного начертания шрифта нажмите кнопку Полужирный (Ж), а для установки курсивного – кнопку Курсив (К) вкладки Главная.

Подчеркивание. Можно установить одинарное или двойное подчеркивание.

  1. Выделите ячейку или диапазон ячеек.

  2. Щелкните по стрелке кнопки Подчеркнутый вкладки Главная и выберите способ подчеркивания.

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

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

  1. Выделите ячейку или диапазон ячеек.

  2. Отобразите вкладку Шрифт диалогового окна Формат ячейки.

  3. В раскрывающемся списке Подчеркивание выберите способ подчеркивания.

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

Сброс параметров шрифта. Можно отказаться от всех установленных параметров шрифта.

  1. Выделите ячейку или диапазон ячеек.

  2. Отобразите вкладку Шрифт диалогового окна Формат ячейки.

  3. Установите флажок Обычный.

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

Для установки выравнивания используют элементы группы Выравнивание вкладки Главная (рисунок 3.3), мини-панель инструментов, а также вкладку Выравнивание диалогового окна Формат ячеек.

rectangle 458autoshape 459

Рисунок 3.3 – Вкладка Выравнивание диалогового окна
Формат ячеек

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

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

  2. Выделите ячейку или диапазон ячеек.

  3. Отобразите вкладку Выравнивание диалогового окна Формат ячейки.

  4. В списке по горизонтали форматы выберите с заполнением.

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

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

  1. Выделите ячейку или диапазон ячеек.

  2. Нажмите кнопку Перенос текста .

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

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

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

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

  1. Введите данные в левую верхнюю ячейку объединяемого диапазона.

  2. Выделите диапазон ячеек.

  3. Щелкните по стрелке кнопки Объединить и поместить в центре и выберите один из вариантов объединения.

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

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

  1. Выделите диапазон ячеек.

  2. Щелкните по стрелке кнопки Ориентация и выберите один из вариантов направления .

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

  4. Во вкладке Выравнивание диалогового окна Формат ячеек в группе Ориентация выберите способ поворота и требуемый угол. Содержимое ячейки можно повернуть на любой угол с точностью 1 градус. Для этого щелкните по метке нужного угла поворота в поле, где написано слово Надпись, или установите значение в счетчике градусов. Для изменения ориентации на вертикальную (буквы расположены «столбиком») щелкните в поле, где написано слово Текст.

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

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

Для удобства просмотра и печати таблицы можно установить границы ячеек (обрамление ячеек).

  1. Выделите диапазон ячеек.

  2. Щелкните по стрелке кнопки Границы вкладки Главная и выберите один из вариантов границы (рисунок 3.4).



Рисунок 3.4 – Меню Границы

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

  1. В меню кнопки Границы выберите способ рисования границ. Способ Границы рисунка позволяет рисовать внешнюю границу обрамляемой области; Сетка по границе рисунка позволяет рисовать сплошную сетку.

  2. Проведите указателем мыши при нажатой левой кнопке мыши по обрамляемым ячейкам.

При удерживании нажатой клавиши клавиатуры Ctrl происходит временное переключение между режимом Граница рисунка и режимом Сетка по границе рисунка.

Для окончания рисования границ необходимо нажать клавишу клавиатуры Esc.

Границы можно удалять независимо от способа их установки.

  1. Выделите диапазон ячеек.

  2. Щелкните по стрелке кнопки Границы вкладки Главная и выберите вариант Нет границы.

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

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

Заливка ячеек

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

  1. Выделите ячейку или диапазон ячеек.

  2. Щелкните по стрелке кнопки Цвет заливки группы Шрифт вкладки Главная и выберите цвет заливки (рисунок 3.5). При наведении указателя мыши на выбираемый цвет срабатывает функция предпросмотра, и ячейки фрагмента листа отображается указанным цветом.

Для удаления заливки ячеек следует щелкнуть по кнопке Цвет заливки группы Шрифт вкладки Главная и выбрать команду Нет заливки.



Рисунок 3.5 – Выбор цвета заливки

Установка числовых форматов

Под числами в Microsoft Excel понимаются собственно числа, включая числа с десятичными и/или простыми дробями и числа с указанием символа процентов, а также даты и время. Форматирование чисел используется для более удобного представления данных на листе. Для установки числовых форматов используют элементы группы Число вкладки Главная, а также вкладку Число диалогового окна Формат ячеек (рисунок 3.6).

Основные числовые форматы можно выбрать в раскрывающемся списке Числовой формат (рисунок 3.6).

  1. Выделите ячейку или диапазон ячеек.

  2. Щелкните по стрелке списка Числовой формат группы Число вкладки Главная и выберите нужный формат.



Рисунок 3.6 – Основные числовые форматы

Кроме того, в группе Число вкладки Главная расположены кнопки для установки финансового формата, процентного формата, а также для установки разделителя разрядов и управлением количества отображаемых десятичных разрядов (рисунок 3.7).

rectangle 474autoshape 475

Рисунок 3.7 – Диалоговое окно Формат ячеек вкладка Число
  1   2   3
Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации