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



Введение

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

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

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

После запуска  Excel на экране появляется окно, состоящее из нескольких элементов:



 



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

Контекстное меню активизируется нажатием правой кнопки мыши (или Shift + F10). Для выхода из контекстного меню нужно щелкнуть кнопкой мыши вне его или нажать Esc;

Типы данных, используемых в Excel

Excel использует два основных типа данных:

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

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

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

Например, данными числового  типа в Excel являются следующие выражения:

  • =46+55;
  • =200*В5;
  • =А7/В4.

Ввод формулы всегда начинается со знака «равно» ( = ) или «плюс» (+).

 

Примечания:

  • Формулу, содержащуюся в ячейке, по умолчанию можно увидеть в строке формул, когда данная ячейка станет активной, а в самой ячейке виден только результат вычислений. Для отображения формул в ячейках необходимо нажать кнопку Office, в открывшемся окне нажать кнопку Параметры Excel, в окне Параметры Excel выбрать параметр Дополнительно, в группе Показать параметры для следующего листа включить параметр Показывать формулы, а не их значения.
  • Excel вычисляет формулу каждый раз, когда изменяется содержимое таблицы, ссылающееся на данную формулу.
  • Если результат вычислений не помещается в ячейке таблицы, Excel может вывести на экран последовательность символов «#######», говорящую о том, что необходимо увеличить ширину столбца.
  • При вводе десятичных чисел используется запятая (,) в качестве десятичного разделителя.

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

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

Имя функции(<аргумент1; аргумент2; аргумент3 и т.д.), где:

Имя функции показывает, о каких вычислениях идет речь. Примеры имен функции – СУММ, СРЗНАЧ и др.

Аргументы – значения, которые функция использует, вычисляя результат. Аргументы перечисляются в скобках следом за именем функции. В качестве аргументов могут выступать числовые значения, текст, логические значения, массивы, значения ошибок или ссылки, дата/время, а также другие функции и формулы. В Excel различают обязательные (которые всегда нужно задавать) и необязательные аргументы. Отдельные аргументы разделяются символом «точка с запятой» (;).

Результат – значение, полученное при вычислении функции.

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

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

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

Функции в  Excel подразделяются на следующие основные группы:

    • Математические, арифметические и тригонометрические функции.
    • Функции для работы с датами и временем.
    • Финансовые функции.
    • Логические функции.
    • Функции для работы со ссылками и массивами.
    • Функции для работы с базами данных.
    • Статистические функции.
    • Текстовые функции и др.

 

 

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

Функции для работы с датой  и временем позволяют анализировать  и работать со значениями даты и  времени в формулах. Значения даты и времени сохраняются и обрабатываются программой как числа. Начальной датой является 1 января 1900 г. Ей соответствует целое значение 1. Каждый следующий день представлен целочисленным значением, на единицу больше значения предыдущей даты.

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

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

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

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

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

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

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

Диагностика ошибок в формулах Excel

Если Excel не может выполнить обработку формулы в ячейке и вывести результат, то он генерирует сообщение об ошибке и выводит его в данной ячейке (вместо самой формулы или ее результата). Сообщение об ошибке всегда начинается со знака «#».

Сообщения об ошибках в  Excel могут принадлежать к одному из 8 типов:

    • ######
    • #ЗНАЧ!
    • #ДЕЛ/0!
    • #ИМЯ?
    • #Н/Д
    • #ССЫЛКА!
    • #ЧИСЛО!
    • #ПУСТО!

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

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

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

    • при наборе имени произошла опечатка;
    • текст ошибочно не был заключен в двойные кавычки;
    • в ссылке на диапазон ячеек пропущен знак двоеточия (:).

 Ошибка #Н/Д является сокращением термина «Неопределенные Данные».

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

Сообщение об ошибке вида #ЧИСЛО! возникает в том случае, когда в формуле задан неприемлемый аргумент для функции.

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

Ввод  и обработка данных в Excel

Значительная  часть работы в Excel приходится на ввод данных, их редактирование и обработку.

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

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

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

Форматирование  и защита рабочих листов

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

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

Форматирование  в Excel включает в себя решение следующих вопросов:

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

Работа  с электронными таблицами

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки. Столбцы идентифицируются буквами латинского алфавита (А, B, C, D…), а строки – арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных.

Например, адрес F10 говорит  о том, что вводимая информация при  активной ячейке F10 попадет именно в  эту ячейку, а содержимое  этой ячейки отразится в строке формул.

Многие команды  Excel позволяют работать с блоками ячеек. Блок ячеек - это прямоугольник, задаваемый координатами противоположных углов, обычно, верхней левой и нижней правой ячеек. Имена ячеек в блоках разделяются двоеточием  (:). Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3 и В4. Выделение блока ячеек осуществляется протаскиванием курсора мыши на нужный диапазон. При этом ячейка, начиная с которой выделяется блок, остается белого цвета, а остальные ячейки затемняются. Но блок ячеек не обязательно может включать смежные ячейки. Для выделения таких ячеек в блоке следует использовать клавишу <Ctrl>.

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

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

 

ГЛАВА 1

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

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

Любая таблица  по своей структуре состоит из 3 частей:

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

Ввод  заголовка, шапки и исходных данных таблицы

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

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

Ввод исходных данных в  ячейку будет завершен в результате выполнения одного из следующих действий:

    • нажатия клавиши Enter;
    • нажатия соответствующих клавиш управления курсором на клавиатуре;
    • щелчка кнопкой мыши по следующей ячейке для ввода данных;
    • нажатия клавиши табуляции.

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

  1. Нажать клавишу Alt(левую или правую) и, удерживая ее,  нажать коротко клавишу Enter после любого введенного в ячейку слова или словосочетания. Одновременное нажатие клавиш принято обозначать символом “+”, например Alt+ Enter.
  2. Использовать вкладку  Главная , где в группе Ячейки нажать кнопку Формат, в раскрывшемся окне выбрать команду Формат ячеек, а затем в окне Формат ячеек, на вкладке Выравнивание установить переключатель Переносить по словам в области Отображение.
  3. Нажать кнопку Перенос текста в группе Выравнивание на вкладке Главная.

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

Упражнение. Осуществить ввод заголовка, шапки и исходных данных контрольного примера в соответствии с рисунком 1, для чего:

 

 

Рис.1

 

    • В ячейку А1 на Листе 1 ввести “Прайс-лист ООО «Техносервис»” и нажать клавишу Enter.
    • В ячейку А2 ввести “Наименование”, нажать одновременно клавиши Alt и Enter, ввести “товара” и нажать  Enter.
    • В ячейку В2 ввести “Цена”,  нажать одновременно Alt и Enter, ввести “в у.е.” и нажать Enter.

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

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

Внимание! Введенный текст заголовка таблицы занял несколько соседних с ячейкой А1 ячеек. Однако если перевести курсор, например в ячейку В1, то в строке состояния, где обязательно отражается содержимое ячейки, в которой находится курсор, ничего не будет отражено. Это объясняется тем, что введенный текст, ширина которого превышает ширину ячейки А1, расположился в соседних ячейках справа лишь на экране дисплея. Если бы в дальнейшем потребовалось ввести в ячейку В1 какие-либо данные, произошло бы усечение текста до размеров ячейки А1, хотя на самом деле в ячейке А1 сам текст остался полностью.

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

Рис.2   

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

Редактирование  содержимого ячейки

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

Excel позволяет редактировать содержимое ячеек несколькими способами:

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

Удаление ошибочной информации осуществляется нажатием клавиши Delete в активной ячейке.

 

Упражнение. Для удобства ориентации среди электронных документов рекомендуется давать листам рабочей книги соответствующие названия. Предлагается назвать первый лист рабочей книги «Прайс-лист». Для этого необходимо:

    • Установить указатель мыши на название Лист1.
    • Щелкнуть правой кнопкой мыши.
    • В контекстном меню выбрать пункт Переименовать.
    • С клавиатуры набрать новое название листа.
    • Нажать Enter.

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

Оформление  электронной таблицы

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

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

Большинство операций по оформлению таблиц сосредоточено на вкладке Главная. Наиболее употребляемые операции (цвет заливки, цвет шрифта, выравнивание по различным признакам, границы, разрядность и т.п.) выведены в качестве кнопок в группах Шрифт, Выравнивание, Число.

 

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

Рис.3 

 

    • Выделить блок ячеек А1:В2.
    • На вкладке Главная в группе Шрифт нажать кнопку со стрелкой (справа от названия группы).
    • В раскрывшемся окне Формат ячеек на вкладке Шрифт в опции Начертание выбрать Полужирный, в опции Размер установить 12 и нажать ОК.

Примечание: те же действия могут быть выполнены с помощью кнопки Формат в группе Ячейки на вкладке Главная, после нажатия которой нужно выбрать команду Формат ячеек, а так же при помощи команды Формат ячеек в контекстном меню, вызываемым нажатием правой кнопки мыши после выделения соответствующего блока ячеек, или соответствующей мини-панели.

    • Выделить всю таблицу (блок ячеек А2:В10).
    • На вкладке Главная в группе Шрифт нажать кнопку Границы и щелкнуть левой кнопкой мыши по стрелке справа от нее.
    • Из списка вариантов выбрать Внешние границы (граница вокруг всей таблицы).
    • Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока.
    • Путем выделения отдельных частей таблицы (шапки, колонок), закончить оформление таблицы в соответствии рис. 3, используя кнопку Границы группе Шрифт на вкладке Главная.

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

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

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

 Активизировав Кнопку «Office» в верхнем левом углу окна, вы увидите несколько команд, позволяющих сохранить файл EXCEL: Сохранить, Сохранить как, Закрыть. Каждая из этих команд имеет свою специфику. Команда Сохранить как, обычно, используется при первом сохранении документа или для выбора способа его сохранения.  Команда Сохранить применяется для сохранения изменений, сделанных в существующем документе. Сохранить как Web- страницу сохраняет документ на Web-странице. При выборе команды Закрыть, во избежание случайной потери выполненной работы, Excel всегда запрашивает, не хотите ли вы сохранить свои изменения. Кроме того, нажав кнопку Сохранить на панели инструментов, вы можете быстро сохранить текущий документ точно так же, как с помощью команды Сохранить из меню Office. После сохранения файла книги, с помощью одной из перечисленных выше команд сохранения, книга остается открытой. Файл удаляется с экрана только при закрытии книги.

 

Упражнение. Необходимо сохранить рабочую книгу, где находится таблица Прайс-лист ООО «Техносервис» как файл с именем «Продажи» (данное имя вводится с учетом того, что в этой же книге будут сформированы новые таблицы с данными о продажах товаров). Необходимо сохранить файл в сетевом каталоге, на дискете или в папке, которую укажет преподаватель. Для этого:

    • Активизировать Кнопку «Office».
    • В выпадающем меню выбрать Сохранить как Книга EXCEL В появившемся окне указать диск, папку (уточнить у преподавателя) и имя сохраняемого файла (Продажи).
    • Щелкнуть указателем мыши по кнопке Сохранить.

Загрузка  рабочей книги

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

Формирование  заголовка и шапки таблицы

Для создания следующей таблицы  необходимо перейти на Лист2 рабочей книги «Продажи».

 

Заголовок и шапка новой  таблицы представлены на рис.4.

Рис.4

 

Упражнение. В соответствии с рисунком 4 необходимо:

    • Ввести заголовок в ячейку В1.
    • Перейти на «Прайс-лист».
    • Выделить блок ячеек А2:В2 и нажать правую кнопку мыши
    • В контекстном меню выбрать команду Копировать
    • Вернуться на Лист2 .
    • Установить курсор в ячейку А2 и нажать правую кнопку мыши.
    • В контекстном меню выбрать Специальная вставка и в открывшемся окне выбрать опцию Значения. Нажать ОК.
    • Выделить вторую строку таблицы, для чего щелкнуть левой кнопкой мыши по маркеру строки, расположенному на вертикальной линейке.
    • Нажать правую кнопку мыши и в контекстном меню выбрать команду Формат ячеек.
    • На вкладке Выравнивание установить флажок в опции Переносить по словам и нажать ОК.

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

    • Ввести соответствующие названия граф таблицы по образцу (рис.4).
    • Выделить блок ячеек А1:Н2 и на вкладке Главная в группе Шрифт  установить полужирный шрифт (Ж).

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

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

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

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