1719

Основы работы в табличном процессоре Microsoft Excel

Реферат

Информатика, кибернетика и программирование

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

Русский

2013-01-06

178.39 KB

79 чел.

Основы работы в табличном процессоре Microsoft Excel

  1.  Общие сведения

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

Для управления работой ЭТ используется специальный комплекс программ – табличный процессор.

Главное достоинство ЭТ – это возможность мгновенного пересчета всех данных, связанных формулами, при изменении значения любого из них.

Первая ЭТ Visicalc была разработана студентами Гарвардского университета в 1979г. Затем появились ЭТ SuperCalc, Lotus 1-2-3. В 1987г. был создан табличный процессор фирмы Microsoft. Наибольшее распространение получила версия Excel 5.0. Сейчас, в основном, используются версии Excel 2003 и Excel 2007, входящие в пакеты Microsoft Office.

Основные функции Excel:

  1.  обработка и анализ данных любого типа.
  2.  графическое представление данных в виде диаграмм любого типа.
  3.  работа со списками (базами данных).

Выполнение действий возможно разными способами:

  1.  Команды управляющего меню.
  2.  Контекстное меню (ПКМ).
  3.  Панели инструментов
  4.  Горячие клавиши.

Интерфейс Excel

Основные элементы окна Excel:

  1.  строка заголовка
  2.  строка меню
  3.  панель инструментов Стандартная
  4.  панель инструментов Форматирование
  5.  поле имени
  6.  строка формул
  7.  заголовки столбцов
  8.  заголовки строк
  9.  ярлыки листов
  10.  окно документа рабочей книги
  11.  строка состояния
  12.  полосы прокрутки

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

  1.  Рабочие книги и рабочие листы

Excel хранит свои документы в файлах с типом XLS. Каждый такой файл называется Рабочей книгой. Рабочая книга состоит из рабочих листов. Каждый такой лист – это отдельная ЭТ. Количество листов при создании книги задается параметрами Excel (вкладка Общие).

Под таблицей находятся ярлыки листов, при создании рабочей книги они имеют имена Лист1, Лист2 и т.д. Используя КМ ярлыка и буксировку мышью, можно выполнить действия с листами:

  1.  переименовать;
  2.  добавить;
  3.  удалить;
  4.  переместить;
  5.  копировать.

  1.  Управление файлами:

Выполняется с помощью меню Файл:

  1.  Создание нового документа

Файл – Создать   

  1.  Открытие документа

Файл - Открыть…  

  1.  Сохранение документа
  2.   под новым именем Файл - Сохранить как…
  3.   повторно Файл – Сохранить    
  4.  Просмотр документа перед печатью

Файл - Предварительный просмотр

  1.  Печать документа

Файл – Печать

ДО Параметры страницы можно открыть из предварительного просмотра документа. Оно содержит 4 вкладки: страница, поля, колонтитулы, лист.

На вкладке Страница указываются:

  1.  масштаб вывода;
  2.  размер бумаги;
  3.  ориентация страницы.

На вкладке Поля указываются:

  1.  расположение полей;
  2.  расположение колонтитулов.

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

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

Для вывода таблицы с формулами их нужно предварительно установить на экране (Сервис – Параметры – Вид – Формулы).

  1.  Структура таблицы

Структуру таблицы составляют строки и столбцы. Столбцы обозначаются буквами латинского алфавита, а строки цифрами. Заголовки столбцов : A, B, C…Z, AA, AB…IV (256) заголовки строк: 1,2..65536.

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

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

Ссылка на диапазон смежных ячеек   А5:В10

несмежных ячеек   А5:В10 ; D1:D4

все ячейки одной строки 5:5

все ячейки одного столбца Е:Е

Выделение диапазонов

№

Вид диапазона

Способ выделения

1

Ячейка

Щелчок по ячейке

2

Столбец, строка

Щелчок по заголовку столбца, строки

3

Диапазон смежных ячеек, строк, столбцов
  1.  Выделение диапазона при нажатой ЛКМ
  2.   Щелчок в первой ячейке, Shift + щелчок в последней ячейке

4

Диапазон несмежных ячеек, строк, столбцов

Выделение каждого диапазона смежных ячеек + Ctrl

5

Весь рабочий лист

Щелчок по кнопке перед заголовками строк и столбцов

5. Создание таблиц, Ввод данных.

Правила создания таблиц:

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

Ввод данных

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

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

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

При вводе чисел они выравниваются по правому краю ячейки. Числа вводятся с десятичной запятой.

Excel хранит даты и время как числовые значения. Дата – это число дней прошедших с 1 января 1900 года, время – часть суток между 12:00 ночи и заданным временем. Представление даты и времени в ячейке зависит от формата ячейки. Максимальная дата в Excel 31 декабря 9999 года представляется числом 2958465. В режиме отображения формул дата и время отображаются как числовые значения. Например, 24 декабря 1996 года отображается числом 35423.

При вводе дат в качестве разделителя используется десятичная точка (23.06.09), времени – двоеточие (12:22:10).

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

Арифметические операции в порядке убывания приоритета:

- (унарный минус);

% (процент);

^ (возведение в степень);

* (умножение), / (деление);

+ (сложение), - (вычитание).

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

Пример: =(A1+B3)*3+ sin(C4)^2

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

Для отображения формул в ячейках используется команда Сервис – Параметры, на вкладке Вид установить режим √ Формулы.

Использование ссылок в формулах

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

Например:

Относительная ссылка D4

Абсолютная ссылка  $D$4

Смешанные ссылки  $D4 D$4

Можно использовать клавишу F4 для циклического изменения ссылок.

Например, при копировании формулы =А1+В1 из ячейки С1 в ячейки С2 и С3 в них появятся соответственно формулы: = А2+В2 и = А3+В3. При копировании формулы из ячейки С1 в ячейки D1 и Е1 – формулы = В2+С2 и = С2+D2. Т.е. при копировании формулы входящие в нее ссылки на ячейки изменяются в соответствии с новым положением формулы.

При копировании формулы =$А$1+В1 ссылка на ячейку В1 будет изменяться, а ссылка $А$1 будет постоянной независимо от нового положения формулы.

Вставка функций в формулы:

  1.  ввод с клавиатуры
  2.  использование Мастера функций 

Вставка - Функция - … или инструмент fx

Часто используемые функции:

СРЗНАЧ (диапазон)  среднее арифметическое

МИН (диапазон)  минимальное значение в диапазоне

МАКС (диапазон)  максимальное значение в диапазоне

СУММ (диапазон)  суммирование чисел в диапазоне

Автосуммирование

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

Выполнение:

1 способ:

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

2 способ:

  1.  выделить ячейку, в которую должна быть помещена сумма;
  2.  щелкнуть по кнопке Автосумма;
  3.  выделить суммируемый диапазон;
  4.  нажать Enter. 

Автоматическое заполнение ячеек

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

Используется для:

  1.  ввода повторяющихся данных;

 1  1 1

  1.  продолжения рядов с некоторым шагом;

5  10 15

  1.  расширения списков;

январь февраль март

цех 1 цех 2  цех 3

  1.  копирования формул в смежные ячейки;

Выполнение:

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

Удерживание клавиши Ctrl меняет режим заполнения. Например, при вводе в первую ячейку текста январь, во всех остальных появится тот же текст, а не названия следующих месяцев. Ряд с любым начальным значением будет продолжен с шагом 1.

6. Редактирование таблиц

Команды отмены и повторения 

Правка - Отменить / Повторить

Инструменты 

Редактирование данных в ячейке

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

Очистка ячеек

Ячейка кроме содержимого (текста, чисел и т.п.) включает форматы и примечания. Для очистки разных элементов ячейки используется команда Правка - Очистить -…. Для очистки только содержимого можно использовать клавишу Delete.

Вставка ячеек

  1.  строки (столбцы)

выделить строки (столбцы); 

выполнить команду Вставка - Строка (Столбец) 

Вставляется столько новых строк или столбцов, сколько было выделено.

  1.  блок ячеек:
  2.  выделить ячейки;
  3.  выполнить команду Вставка - Ячейки - …
  4.  указать: со сдвигом вправо или вниз

Удаление ячеек

  1.  строки (столбцы)
  2.  выделить строки (столбцы);
  3.  выполнить команду Правка - Удалить
  4.  блок ячеек:
  5.  выделить ячейки;
  6.  выполнить команду Правка - Удалить …
  7.  указать: со сдвигом влево или вверх

Перемещение и копирование

Способ 1. Использование буфера обмена

При вставке указывается левый верхний угол нового диапазона. 

Вставить вырезанный диапазон из БО можно только 1 раз!

Вставить скопированный диапазон из БО можно много раз, за последней вставкой нажать клавишу Enter.. Для удаления отмеченного для копирования диапазона клавишу Esc.

Способ 2. Перетаскивание мышью

  1.  выделить диапазон;
  2.  переместить при нажатой ЛКМ; копирование + Ctrl

Способ 3. Копирование методом автозаполнения

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

Когда используются способы:

1 – для больших таблиц, разных листов;

2 – при близких перемещениях на одном листе;

3 – при заполнении смежных ячеек.

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

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

Понятие форматирования включает в себя следующие элементы: 

  1.  высота и ширина строк и столбцов;
  2.  шрифт, его размер и начертание;
  3.  форма представления числовых данных;
  4.  элементы оформления: границы, узор, цвет фона и символов;
  5.  выравнивание данных в ячейке;
  6.  защита ячеек.

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

Способ 1:

  1.  переместить границы в строке заголовков мышью.

Способ 2:

  1.  выделить столбцы или строки;
  2.  выполнить команду Формат - Столбец (Строка) - ... 

(здесь можно скрыть или показать скрытые столбцы/строки)

Все остальные элементы форматирования устанавливаются в ДО Формат ячеек. Для его открытия необходимо:

  1.  выделить диапазон;
  2.  выполнить команду Формат - Ячейки - ... 

или команду Формат ячеек - ... в контекстном меню выделенного диапазона.

Диалоговое окно имеет 6 вкладок: число, выравнивание, шрифт, граница, вид, защита. Если ДО открывается при вводе или редактировании ячейки (с курсором), то доступна только вкладка Шрифт.

Часто используемые форматы вынесены на ПИ Форматирование

Число:

  1.  числовые форматы;
  2.  число десятичных знаков для числового, финансового, процентного, денежного форматов.

Увеличить или уменьшить число десятичных разрядов можно с помощью кнопок на ПИ Увеличить разрядность и Уменьшить разрядность.

Не использованные ранее или очищенные ячейки имеют формат Общий. Это означает, что числа отображаются с максимально возможной точностью. Если число достаточно велико или мало, оно отображается в экспоненциальной форме (1,24328E+17). Если ширины ячейки не хватает для отображения числа, ячейка заполняется символами #.

Выравнивание:

  1.  выравнивание по горизонтали;
  2.  выравнивание по вертикали;
  3.  ориентация текста;
  4.  перенос по словам для длинного текста (для того, чтобы поместить его в одну ячейку);
  5.  автоподбор ширины ячейки по ее содержимому;
  6.  объединение ячеек.

Шрифт:

  1.  название и размер шрифта;
  2.  начертание шрифта (курсив, полужирный и т.д.);
  3.  цвет символов;
  4.  надстрочный и подстрочный текст.

Граница:

  1.  расположение границ;
  2.  тип линии границы;
  3.  цвет линии границы.

Вид:

  1.  цвет заливки ячейки (фона);
  2.  вид фонового узора.

Защита:

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

Копирование форматов

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

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

Условное форматирование

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

Для установки условного форматирования необходимо:

  1.  выделить ячейки, для которых требуется установить условное форматирование;
  2.  выполнить команду Формат - Условное форматирование;
  3.  задать необходимые условия и форматы в ДО Условное форматирование.

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

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

Для выбора формата щелкнуть по кнопке Формат. 

Для добавления следующего условия щелкнуть по кнопке А также>>. 

Может быть задано до трех условий. Если ни одно из заданных условий не принимает значения ИСТИНА, формат ячеек остается прежним.

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

Изменение или удаление условного формата

  1.  выделить ячейки, для которых требуется установить условное форматирование;
  2.  выполнить команду Формат - Условное форматирование.

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

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

Для того, чтобы для выделенных ячеек удалить как условные, так и другие форматы, выполнить команду Правка - Очистить - Форматы.