Базовые сведения по работе Excel 2010

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

MS Excel работает в интерактивном режиме. Это означает, что пользователь выбирает некоторые команды или производит действия, а MS Excel на них реагирует. Интерфейс программы MS Excel 2010 основан на концепции Ленты и Панели быстрого доступа. Лента организована в виде набора тематических вкладок, каждая из которых содержит инструменты управления отдельными аспектами работы с программой. Результатом выполнения некоторых команд (пиктограмм и надписей со стрелками) может быть отображение еще одной, вложенной группы инструментов. Описание команд, доступных на ленте, в общем виде может быть следующим: Название вкладки| Название группы инструментов| Название команды| Название вложенной команды.

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

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

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

Вкладка Файл располагается первой в списке вкладок и её содержимое занимает все окно, не позволяя редактировать активный документ. Такое состояние программы, нужное для работы со свойствами активного документа и настройками приложения, носит название представления Backstage. Оно представляет окно, левая часть которого содержит команды открытия, закрытия и сохранения документа, а также разделы, позволяющие настраивать свойства и выполнять печать и отправку текущей рабочей книги Excel. При выборе соответствующего раздела настройка нужных параметров и свойств документа выполняется в правой части.

Выход из представления Backstage и возврат в исходное состояние осуществляется нажатием клавиши <Esc>.

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

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

Рабочая книга и её основные элементы

Документы, с которыми производится работа в Excel, носят название рабочих книг. При стандартном запуске Excel автоматически создается одна новая рабочая книга (с названием Книга1). В Excel допускается работа с несколькими рабочими книгами одновременно, при этом каждая открывается в собственном окне. Для перехода к одной из открытых рабочих книг можно выполнить команду Вид| Перейти в другое окно. Рабочая книга состоит из нескольких рабочих листов. Рабочий лист − это самый распространенный лист, который обычно имеют ввиду, говоря об электронной таблице. Каждый лист имеет имя, которое выводится на ярлычке данного листа внизу слева. Для переименования текущего листа можно произвести двойной щелчок на ярлычке рабочего листа и ввести новое имя. По умолчанию новая рабочая книга состоит из трех листов. Для изменения количества рабочих листов по умолчанию следует выполнить команду Файл| Параметры| Общие и изменить значение счетчика Число листов. Для того чтобы вставить в книгу новый рабочий лист достаточно нажать кнопку Вставить лист, которая находится внизу рабочего листа рядом с ярлычками рабочих листов.

Рабочий лист состоит из прямоугольных ячеек. Каждая ячейка имеет адрес («ссылку»), который состоит из имени столбца и номера строки, например ячейка D3 − это ячейка, стоящая на пересечении третьей строки и четвертого столбца. Адресация ячейки рабочего листа является лишь частью полного адреса ячейки, который в общем случае включает имя рабочего листа и полное имя файла книги, например [Пример1.xlsx]Лист1!A1.

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

Для того чтобы выделть диапазон:

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

2. Пробуксируйте указатель (белый крест) к противоположному углу диапазона.

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

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

2. Пробуксируйте указатель (белый крест) при нажатой клавише <Ctrl> к противоположному углу диапазона.

3. Выполните шаги 1 и 2, выделяя необходимые диапазоны.

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

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

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

3. Выберите ячейку противоположного угла диапазона при нажатой клавише <Shift>.

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

Ввод данных в ячейку

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

1. Выберите ячейку.

2. Наберите данные с клавиатуры.

3. Нажмите клавишу <Enter>.

MS Excel предоставляет дополнительное средство для ввода повторяющихся данных в столбец − выбор из списка.

Для отображения списка в ячейке:

1. Выберите ячейку.

2. Нажмите правую кнопку мыши, на экране отбразится контекстное меню.

3. В контекстном меню укажите команду Выбрать из списка.

4. На экране отобразится список ранее введенных в данном столбце различных значений.

5. Выберите необходимое значение.

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

1. Выбрать ячейку.

2. Нажать клавишу <F2>, либо дважды щелкнуть по ячейке.

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

4. Нажать клавишу <Enter>.

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

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

2. Укажите команду Главная| Ячейки| Формат| Формат ячеек (или просто нажмите правую кнопку мыши).

3. В открывшемся диалоговом окне выберите необходимые параметры шрифта.

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

Ввод текстовых значений

Текстовыми считаются те данные, которые Excel не смог распознать как число, дату или время. Текст может содержать буквы, цифры и специальные символы. Когда необходимо ввести текст как данные другого типа, тогда перед ними ставится апостроф «’», например − ’1.03.2012.

Ввод последовательностей данных

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

1. Ввести начальное значение в первую ячейку.

2. Выделить диапазон ячеек, в котором должна содержаться последовательность.

3. Выполнить команду Главная| Редактирование| Заполнить| Прогрессия. Появится диалоговое окно Прогрессия, в котором задаются параметры последовательности.

4. В появившемся диалоговом окне Прогрессия заполнить все необходимые поля и нажать ОК. В результате будет построена необходимая последовательностью

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

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

Средства поиска и замены

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

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

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

Для изменения ширины столбца надо выполнить команду Главная| Ячейки| Формат| Ширина столбца и в поле открывшегося диалогово окна Ширина столбца задать новое значение.

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

Аналогично можно изменить высоту строки.

Для того чтобы скрать (или отобразить) один или несколько столбцов можно выполнить команду Главная| Ячейки| Формат| Скрыть или отобразить| Скрыть столбца (Отобразить столбцы). Аналогичные команды выполняются для скрытия или отображения строк.

Средства защиты

Защита ячеек (или наоборот, снятие защиты) устанавливается в два приема. Сначала для выделенного диапазона ячеек выполняется команда Главная| Формат ячеек| Защита и ставится (или убирается) флажок Защищаемая ячейка.

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

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

Для снятия защиты листа нужно выполнить команду Рецензирование| Изменения| Снять защиту листа. Для снятия защиты рабочей книги нужно выполнить команду Рецензирование| Изменения| Защитить книгу и ввести пароль в диалоговом окне Снять защиту книги.

Использование стилей при форматировании рабочих листов

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

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

Для создания нового стиля на основе имеющегося нужно:

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

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

3. Нажать кнопку Формат и изменить, при необходимости, параметры форматирования.

4. В поле Имя стиля заменить название стиля.

5. Нажать кнопку ОК. Имя созданного стиля будет добавлено в группу стилей Пользовательские. Для применения созданного стиля к ячейке или группе ячеек, достаточно щелкнуть по кнопке с именем созданного стиля.

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

Формат таблицы

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

Для применения автоформата нужно:

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

2. Выполнить команду Главная| Стили| Форматировать как таблицу.

3. На появившейся панели выбрать подходящий вариант автоформата.

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

Для удаления форматирования таблицы следует:

1. Выделить таблицу.

2. Выполнить команду Конструктор| Стили| Экспресс-стили| Очистить

Данная процедура удаляет всякое форматирование соответствующего диапазона.

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

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

1. Выделить исходную ячейку и выполнить команду Главная| Копировать.

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

3. Выполнить команду Главная| Буфер обмена| Вставить| Специальная вставка.

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

5. Нажать ОК.

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

Формула рабочего листа

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

Например, формула рабочего листа =A1+A2 складывает содержимое ячеек A1 и A2.

Формула, введенная в ячейку должна начинаться со знака равенства.

Если ввести формулу в ячейку, то в ней отобразится результат расчета по формуле. При выборе ячейки формула видна в строке формул. Текст в формулы вводиться при помощи ковычек. Например, формула =”Итого: ” &B1 возвращает результат конкатенации текстовой строки и содержимого ячейки B1.

Даты и время в формулы также вводятся при помощи кавычек. Например, следующая формула возвращает число дней, прошедших между двумя датами =”11.08.2000” − ”11.03.2000”.

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

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

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

Если возникает необходимость удалить формулу из ячейки, оставив только результат, то надо выбрать ячейку, а затем последовательно нажать клавиши <F2>, <F9>, <Enter>. Данная процедура заменяет формулу в отдельной ячейке. Чтобы заменить формулы в диапазоне ячее нужно

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

2. Выполнить команду Главная|Буфер обмена|Копировать.

3. Выделить исходный диапазон еще раз.

4. Выполнить команду Главная| Буфер обмена| Вставить| Специальная вставка.

5. В открывшемся диалоговом окне Специальная вставка в группе Вставить установить флажок Значения.

6. Нажать кнопку ОК.

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

Ошибки, возвращаемые формулами

Иногда формула возвращает сообщение об ошибке начинающееся со знака #. Наиболее часто встречаются ошибки:

В формулах допускается использование ссылок на ячейки (или диапазоны), расположенные на различных рабочих листах (трехмерные ссылки). Например, формула =СУММ(Лист1:Лист3!А1:В2) возвращает сумму значений из диапазонов, расположенных в рабочих листах Лист1, Лист2, Лист3.

Относительные и абсолютные ссылки

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

Например, в ячейке D3 введена формула =В3+С3. Выбираем ячейку D3 и располагаем указатель мыши на маркере заполнения (черном квадратике, расположенном в нижнем правом углу ячейки). Протягиваем маркер заполнения на диапазон D4:D5. В результате ячейки D4 и D5 будут введены формулы =В4+С4 и =В5+С5.

На практике часто возникает ситуация, когда ссылка на ячейку не должна изменяться в формуле при её копировании или перемещении. Такие ссылки называются абсолютными. Для того чтобы создать абсолютную ссылку на ячейку, достаточно перед именем строки и столбца поставить знак $. Например $D$7 − это абсолютная ссылка на ячейку D7.

Клавиша <F4> позволяет ускорить ввод абсолютной ссылки в формулу. Выделите в формуле ссылку на ячейку или диапазон, а затем нажмите клавишу <F4>. Знаки $ будут добавлены в формулу автоматически.

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

Абсолютная ссылка на строку. В этом случае знак $ размещается только перед номером строки, например, D$3.

Абсолютную ссылку на столбец. В этом случае знак $ размещается только перед именем столбца, например, $D3.

Построение графиков

Математические функции в Excel

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

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

При вводе формулы можно использовать список функций, который становится доступен при выполнении команд: Формулы| Вставить функцию. При выполнении этой команды открывается диалоговое окно Мастер функций, в верхней части которого можно выделить нужную группу функций, а в нижней − конкретную функцию из группы

Построение графика функции одной переменной

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

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

Для построения графика функции необходимо первоначально построить таблицу ее значений при различных значениях аргумента, причем аргумент изменяют обычно с фиксированным шагом. Шаг выбирают небольшим, чтобы таблица значений функции отражала ее поведение на заданном интервале. В нашем случае возьмем в качестве шага 0,1, и введем в ячейки диапазона А3:А13 последовательность значений образующих арифметическую прогрессию. Затем в ячейку В3 введем формулу: =cos(ПИ()*A1)^2 и продлим вниз на диапазон В4:В13.

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

Перейдем к конструированию графика функции по существующей таблице значений аргументов и соответствующих значений функции. Выделим диапазон ячеек, содержащих координаты x и y − A3:B13, затем выполняем команды:

1. Вставка| Диаграмма| Точечная. Точечная диаграмма отображает зависимость у от величины, которая меняется, в общем случае с переменным шагом по оси x.

2. В появившейся вкладке выбираем тип Точечная с гладкими линиями.

На экране будет построена диаграмма выбранного вами типа и откроется вкладка для работы с диаграммой − Конструктор. Список групп на этой вкладке:

Тип− позволяет изменить тип выбранной диаграммы; сохраняет образец.

Данные − позволяет переориентировать ряды/столбцы, изменить исходные данные.

Макеты диаграмм − позволяет выбрать способ размещения легенды и исходных данных на диаграмме.

Стили диаграмм − позволяет изменить визуальный стиль исполнения диаграммы.

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

После редактирования график функции будет иметь вид

Замечание. Чтобы вставить математическую формулу выполняем команды Вставка| Символы| Формула. Затем с помощью вкладки Конструктор вводим и редактируем формулу.

Два графика в одной системе координат

Рассмотрим пример построения в одной системе координат графиков функций:

, при .

Сначала строим график функции

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

Добавляем новый ряд и соответствующие значения по оси X и по оси Y.

Нажимаем ОК.

Далее оформляем график

График функции состоящей из двух и более условий

Рассмотрим пример построения графика функции при , где

График строится по принципам построения графика функции одной переменной, за исключением того, что в столбец значений y вводится формула, использующая логическую функцию ЕСЛИ:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).

Рассмотрим пример построения графика функции, имеющей точку разрыва.

Построим график функции

,

на отрезке .

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

12
• Далее ⇒