Инфоурок / Информатика / Конспекты / Лекции MS Excel 2010

Лекции MS Excel 2010


Напоминаем, что в соответствии с профстандартом педагога (утверждён Приказом Минтруда России), если у Вас нет соответствующего преподаваемому предмету образования, то Вам необходимо пройти профессиональную переподготовку по профилю педагогической деятельности. Сделать это Вы можете дистанционно на сайте проекта "Инфоурок" и получить диплом с присвоением квалификации уже через 2 месяца!

Только сейчас действует СКИДКА 50% для всех педагогов на все 184 курса профессиональной переподготовки! Доступна рассрочка с первым взносом всего 10%, при этом цена курса не увеличивается из-за использования рассрочки!

ВЫБРАТЬ КУРС И ПОДАТЬ ЗАЯВКУ
библиотека
материалов

32


ОГЛАВЛЕНИЕ



ВВЕДЕНИЕ

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

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

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

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

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

В настоящее время Microsoft Excel – ведущая программа обработки электронных таблиц, представляющая собой достаточно мощное средство разработки информационных систем, которое включает как электронные таблицы (со средствами финансового и статистического анализа, набором стандартных математических функций, доступных в языках программирования высокого уровня, рядом дополнительных функций, встречающихся только в библиотеках инженерных программ), так и средства визуального программирования (Visual Basic for Application). С помощью VBA можно автоматизировать всю работу, начиная от сбора информации, ее обработки до создания итоговой документации как для офисного пользования, так и для размещения на Web-узле.

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

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

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

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

Кроме того, табличные процессоры позволяют, следующее:

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

    • автоматизировать экономическо-финансовую деятельность;

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

    • строить различные графики и диаграммы;

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

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

    • автоматизировать выполнение операций и расчетов с помощью макросов и программных вставок.







ЛЕКЦИЯ 1: ИНТЕРФЕЙС MICROSOFT EXCEL

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

hello_html_3f409d1a.png

Рис. 1.1.  Лента


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

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

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

hello_html_m38e7159b.png

Рис. 1.2.  Отображение ленты вкладки Главная


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

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

Вкладка Разметка страницы предназначена для установки параметров страниц документов.

Вкладка Вставка предназначена для вставки в документы различных объектов. И так далее.


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

В некоторых случаях появляется сразу несколько вкладок, например, при работе с диаграммами появляются три вкладки: Конструктор, Макет и Формат.

hello_html_m581a01bc.png
Рис. 1.3.  Контекстные вкладки для работы с таблицами

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


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

hello_html_13e4cf0d.png
Рис. 1.4.  Использование значка группы и диалоговое окно этой группы


hello_html_47687b9a.pngКнопка "Office" (в версии 2007) или меню Файл (версия 2010)

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


Сохранение файлов

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

  1. Нажмите кнопку Office и выберите команду Сохранить.

  2. В окне Сохранение документа перейдите к нужной папке.

  3. В поле Имя файла введите имя файла и нажмите кнопку Сохранить.


Рис. 1.5.  Кнопка и меню "Office"


hello_html_m331131c9.png

hello_html_m331131c9.pngЛЕКЦИЯ 2: РАБОТА С ДОКУМЕНТОМ. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ


Документ Microsoft Excel называют книгой.

Книга Microsoft Excel состоит из отдельных листов. Вновь создаваемая книга обычно содержит 3 листа. Листы можно добавлять в книгу. Максимальное количество листов не ограничено. Листы можно удалять. Минимальное количество листов в книге – один.

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

Каждый лист имеет имя. Имена листов в книге не могут повторяться.

Ярлыки листов расположены в нижней части окна Microsoft Excel.

Лист состоит из ячеек, объединенных в столбцы и строки.

Лист содержит 16834 столбцов. Столбцы именуются буквами английского алфавита.

Лист содержит 1048576 строк. Строки именуются арабскими цифрами.

Каждая ячейка имеет адрес, состоящий из заголовка столбца и заголовка строки. Например, самая левая верхняя ячейка листа имеет адрес А1, а самая правая нижняя – XFD1048576.

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

Изменение режима просмотра листа

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

hello_html_32a7dc89.png

Рис. 2.1.  Выбор режима просмотра листа

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

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

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


Выделение фрагментов листа

Хотя бы одна ячейка на листе всегда выделена. Эта ячейка обведена толстой линией. Ячейки выделенного фрагмента затенены, кроме одной, как правило, самой левой верхней ячейки.

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

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

Для выделения нескольких несмежных ячеек нужно выделить первую ячейку, а затем каждую следующую – при нажатой клавише клавиатуры Ctrl. Точно так же можно выделить и несколько несмежных диапазонов. Первый диапазон выделяется обычным образом, а каждый следующий – при нажатой клавише клавиатуры Ctrl. При описании диапазона несмежных ячеек указывают через точку с запятой каждый диапазон, например, А1:С12; Е4:Н8.

Общие правила ввода данных

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

  1. Выделите ячейку.

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

  3. Подтвердите ввод, нажав клавишу Enter.

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


Ввод чисел

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

hello_html_m38b5a970.png

Рис. 2.2.  Отображение чисел в ячейке

Наибольшее число, которое можно ввести в ячейку составляет 9,99999999999999*10307. Точность представления чисел – 15 разрядов (значащих цифр).

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

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

hello_html_60b1d54a.png

Рис. 2.3.  Отображение простой дроби на листе и в строке формул


Ввод дат и времени

Microsoft Excel воспринимает даты начиная с 1 января 1900 года. Даты до 1 января 1900 года воспринимаются как текст. Наибольшая возможная дата – 31 декабря 9999 года.

Произвольную дату следует вводить в таком порядке: число месяца, месяц, год. В качестве разделителей можно использовать точку (.), дефис (-), дробь (/). При этом все данные вводятся в числовом виде. Точка в конце не ставится. Например, для ввода даты 12 августа 1918 года с клавиатуры в ячейку следует ввести:

  • 12.8.1918 или

  • 12-8-1918 или

  • 12/8/1918.


Использование автозаполнения

Использование стандартных списков

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

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

  2. Выделите ячейку.

  3. Наведите указатель мыши на маркер автозаполнения (маленький черный квадрат в правом нижнем углу выделенной ячейки). Указатель мыши при наведении на маркер принимает вид черного креста.

  4. При нажатой левой кнопке мыши перетащите маркер автозаполнения в сторону изменения значений.

hello_html_m4cd7b99c.png

Рис. 2.4.  Автозаполнение по столбцу с возрастанием


hello_html_m2e16026f.png

Рис. 2.5.  Автозаполнение по строке с возрастанием


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

hello_html_5db1e1e3.png

Рис. 2.6.  Меню автозаполнения при работе с числами


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

Содержимое ячейки можно редактировать непосредственно в ячейке или в строке формул.

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


Создание таблиц

hello_html_m331131c9.pngКак правило, на листе размещают одну таблицу.

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

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

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


Удаление столбцов и строк

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


Изменение ширины столбцов

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

hello_html_m73d97a2c.png

Рис. 2.7.  Изменение ширины столбца перетаскиванием


Изменение высоты строк

Высоту строки можно изменить, перетащив ее нижнюю границу между заголовками строк. Например, для того чтобы изменить высоту строки 2, следует перетащить границу между строками 2 и 3 (рис.2.8).

hello_html_m270562aa.png

Рис. 2.8.  Изменение высоты строки перетаскиванием


Распределение текста в несколько строк

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

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

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

hello_html_223b26b1.png

Рис. 2.9.  Установка отображения нескольких строк текста внутри ячейки

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

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


Объединение ячеек

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

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

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

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

hello_html_m15ec7d4d.png

Рис. 2.10.  Выравнивание по центру произвольного диапазона

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

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


Установка границ ячеек

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

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

hello_html_m61298300.png

Рис. 2.11.  Установка границ


ЛЕКЦИЯ 3: ОСНОВЫ ВЫЧИСЛЕНИЙ


Структура формулы

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

Например, в формуле

=СУММ(В2:В8)*30

СУММ() – функция;

В2 и В8 – ссылки на ячейки;

: (двоеточие) и * (звездочка) – операторы;

30 – константа.


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

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

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

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

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

Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.


Арифметические операторы

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


+ (знак плюс)

Сложение

A1+A2

(знак минус)

Вычитание

Отрицание


A1-A2

-A1

* (звездочка)

Умножение

A1*A2

/ (косая черта)

Деление

A1/A2

% (знак процента)

Процент

50%

^ (крышка)

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

A1^2


Операторы сравнения

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


= (знак равенства)

Равно

(A1=B1)

> (знак больше)

Больше

(A1>B1)

< (знак меньше)

Меньше

(A1

>= (знак больше и равно)

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

(A1>=B1)

<= (знак меньше и равно)

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

(A1<=B1)

<> (знак меньше и больше)

Не равно

(A1<>B1)


Ввод формул с клавиатуры

Формулы можно вводить с использованием клавиатуры и мыши.

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

  1. Выделите ячейку, в которую требуется ввести формулу.

  2. Введите = (знак равенства).

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

  4. Введите знак оператора.

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

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

  7. Подтвердите ввод формулы в ячейку нажатием клавиши Enter.


Нhello_html_3587a30f.pngапример, необходимо создать формулу для расчета стоимости нескольких книг в ячейке D2 таблицы на рис.3.1.

  1. выделите ячейку D2,

  2. введите знак =,

  3. щелкните мышью по ячейке В2,

  4. введите знак *,

  5. щелкните мышью по ячейке С2,

  6. нажмите клавишу Enter.


Рис. 3.1.  Ввод формулы с клавиатуры


Создание формул с использованием мастера функций

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

Для создания формул с функциями используют вкладку Формулы (рис. 3.2).

hello_html_5a4f24e5.png

Рис. 3.2.  Вкладка Формулы

  1. Выделите ячейку, в которую требуется ввести формулу.

  2. Щелкните по кнопке нужной категории функций в группе Библиотека функций и выберите нужную функцию.

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

  4. В окне Аргументы функции нажмите кнопку ОК.


Например, в таблице на рис. 3.3 в ячейке А6 необходимо создать формулу для округления до двух знаков после запятой значения в ячейке А5:

  1. выделите ячейку А6;

  2. щелкните по кнопке категории Математические в группе Библиотека функций и выберите функцию ОКРУГЛ;

  3. в окне Аргументы функции (рис. 3.3) установите курсор в поле Число и на листе выделите ячейку А4, затем установите курсор в поле Число разрядов и с клавиатуры введите число 2;

hello_html_78dba250.png

Рис. 3.3.  Ввод аргументов функции

  1. нажмите кнопку ОК.


Создание формул с использованием кнопки "АвтоСумма"

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

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

Для подтверждения ввода формулы следует нажать клавишу Enter.

Для вычисления суммы произвольно расположенных ячеек следует выделить ячейку, в которой должна быть вычислена сумма, нажать на кнопку АвтоСумма, а затем на листе выделить суммируемые ячейки и/или диапазоны ячеек. Для подтверждения ввода формулы следует нажать клавишу Enter.

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


Редактирование формул

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


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

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

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

Например, при копировании автозаполнением ячейки D2 (рис. 3.4) на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D4 будет формула =В4*С4 и т. д.

hello_html_m789f1edb.png

Рис. 3.4.  Копирование формул

В некоторых случаях использование относительных ссылок недопустимо (рис.3.5).

Дhello_html_m7459b3fe.pngля того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка ячейки имеет формат $A$1, где $ – служебный символ, показывающий абсолютную ссылку.

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



Рис.3.5. Абсолютные ссылки

Ошибки в функциях и аргументах

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

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

hello_html_mc2effd6.png

Рис. 3.6.  Кнопка и меню кнопки Источник ошибки

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


#ЗНАЧ!

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

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

#ДЕЛ/0!

Деление числа на 0 (ноль).

В формуле содержится явное деление на ноль (например, =А1/0). Используется ссылка на пустую ячейку или ячейку, содержащую 0 в качестве делителя

#ИМЯ?

Excel не может распознать имя, используемое в формуле

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

  • Ошибка в написании имени ячейки или диапазона.

  • Ошибка в написании имени функции.

  • В ссылке на диапазон ячеек пропущено двоеточие (:)

  • В формулу введен текст, не заключенный в двойные кавычки.

#Н/Д

Значение недоступно функции или формуле

Не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа

#ССЫЛКА!

Ссылка на ячейку указана неверно

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

#ЧИСЛО!

Неправильные числовые значения в формуле или функции.

  • В функции с числовым аргументом используется неприемлемый аргумент

  • Числовое значение результата вычисления формулы слишком велико или слишком мало, чтобы его можно было представить в Excel


#ПУСТО!

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

Используется ошибочный оператор диапазона


Трассировка связей между формулами и ячейками

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

Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы. Если кнопка не отображается, щелкните сначала по стрелке кнопки Зависимости формул вкладки Формулы (рис. 3.7).

hello_html_m7a869c29.png

Рис. 3.7.  Трассировка влияющих ячеек

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

Для отображения ячеек, в формулы которых входит какая-либо ячейка, ее следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы. Если кнопка не отображается, щелкните сначала по стрелке кнопки Зависимости формул вкладки Формулы (рис. 3.8).

hello_html_11619da7.png

Рис. 3.8.  Трассировка зависимых ячеек

Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее.

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

Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы.














ЛЕКЦИЯ 4: ФИНАНСОВЫЕ ВЫЧИСЛЕНИЯ. О ФИНАНСОВЫХ ФУНКЦИЯХ

Финансовые функции используют в планово-экономических расчетах. Всего в категории "Финансовые" имеется 53 функции.


Расчет амортизационных отчислений

Для расчета амортизационных отчислений необходимо знать, по крайней мере, три параметра:

  1. начальная стоимость имущества;

  2. остаточная стоимость по окончании эксплуатации;

  3. продолжительность эксплуатации.

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


Линейный метод

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

Синтаксис функции:

АПЛ(А;В;С),

где A – начальная стоимость имущества;

В– остаточная стоимость имущества;

C – продолжительность эксплуатации.

Например, приобретено оборудование стоимостью 97000 руб. Продолжительность эксплуатации оборудования – 8 лет. Остаточная стоимость – 7500 руб. Величина амортизационных отчислений составит 11187,50 руб. за каждый и любой год эксплуатации (рис. 4.1).

hello_html_m58e9581a.png

Рис. 4.1.  Расчет амортизационных отчислений линейным методом


Метод "суммы чисел"

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

Синтаксис функции:

АСЧ(А;В;С;D),

где A – начальная стоимость имущества;

В– остаточная стоимость имущества;

C – продолжительность эксплуатации;

D – год, для которого рассчитывается величина амортизационных отчислений.

Например, приобретено оборудование стоимостью 100000 руб. Продолжительность эксплуатации оборудования – 8 лет. Остаточная стоимость – 12000 руб. Величина амортизационных отчислений за первый год эксплуатации составит 19 555,56 руб., за второй год – 17 111,11 руб. и т. д. (рис. 4.2).

hello_html_m2ed35fd6.png

Рис. 4.2.  Расчет амортизационных отчислений методом "суммы чисел"


Метод двойного уменьшения остатка

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

Синтаксис функции:

ДДОБ(А;В;С;D;Е),

где A – начальная стоимость имущества;

В– остаточная стоимость имущества;

C – продолжительность эксплуатации;

D – год, для которого рассчитывается величина амортизационных отчислений;

E – коэффициент, учитывающий процентную ставку снижающегося остатка. Если коэффициент опущен, то он полагается равным 2.

Например, приобретено оборудование стоимостью 100000 руб. Продолжительность эксплуатации оборудования – 8 лет. Остаточная стоимость – 12000 руб. Величина амортизационных отчислений за первый год эксплуатации составит 25000 руб., за второй год – 18 750,00 руб. и т. д. (рис. 4.3, рис. 4.1).

hello_html_m5f2c386d.png

Рис. 4.3.  Расчет амортизационных отчислений методом двойного уменьшения остатка

При другом значении коэффициента уменьшения остатка, получатся другие значения амортизационных отчислений. Например, при уменьшении остатка в три раза, величина амортизационных отчислений за первый год эксплуатации составит 37500 руб., за второй год – 23 437,50 руб. и т. д. (рис. 4.4, рис. 4.1).

hello_html_m2571c52c.png

Рис. 4.4.  Расчет амортизационных отчислений методом тройного уменьшения остатка


Метод фиксированного уменьшения остатка

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

Синтаксис функции

ФУО(А;В;С;D;Е),

где A – начальная стоимость имущества;

В– остаточная стоимость имущества;

C – продолжительность эксплуатации;

D – год, для которого рассчитывается величина амортизационных отчислений;

E – месяцы первого календарного года эксплуатации.

Например, приобретено оборудование стоимостью 100000 руб. Продолжительность эксплуатации оборудования – 8 лет. Остаточная стоимость – 12000 руб. Величина амортизационных отчислений за три месяца первого календарного года эксплуатации составит 5 825,00 руб., за второй год – 21 942,78 руб. и т. д. За девять месяцев последнего (девятого) года эксплуатации – 2 569,92 руб. (рис. 4.5).

hello_html_mc545152.png

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

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


Расчет амортизации для любого выбранного периода

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

Синтаксис функции

ПУО(А;В;С;D;Е;F;G),

где A – начальная стоимость имущества;

В– остаточная стоимость имущества;

C – продолжительность эксплуатации;

D – начальный период, для которого рассчитывается величина амортизационных отчислений;

E – конечный период, для которого рассчитывается величина амортизационных отчислений;

F – коэффициент, учитывающий процентную ставку снижающегося остатка. Если коэффициент опущен, то он полагается равным 2;

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

Например, приобретено оборудование стоимостью 100000 руб. Продолжительность эксплуатации оборудования – 8 лет. Остаточная стоимость – 12000 руб. Величина амортизационных отчислений со второго по пятый годы эксплуатации включительно составит 32 519,53 руб. (рис. 4.6).

hello_html_226f2c2f.png

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


Анализ инвестиций

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

Во всех этих случаях для расчета необходимо знать, по крайней мере, три параметра:

  1. процентная ставка за период;

  2. общее число периодов платежей;

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


Расчет суммы вклада (величины займа)

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

Синтаксис функции

БС(А;В;С;D;Е),

где A – процентная ставка за период;

В– общее число платежей;

C – выплата, производимая в каждый период и не меняющаяся за все время выплаты;

D – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0);

E – число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен – в конце периода, 1 – в начале периода.

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

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

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


Например, необходимо рассчитать будущую сумму вклада в сумме 1000 руб., внесенного на 10 лет с ежегодным начислением 10% (рис. 4.7). Или будущую сумму вклада при тех же условиях, но с ежегодным внесением 1000 руб. (рис. 4.8).

hello_html_m1c560b71.png

Рис. 4.7.  Расчет величины вклада с начальным взносом

hello_html_m30b44362.png

Рис. 4.8.  Расчет величины вклада с начальным взносом при регулярном пополнении

Результат вычисления: в первом случае - 2593,74 руб., во втором - 18531,17руб.


Или, необходимо рассчитать будущую сумму вклада при ежемесячном внесении 200 руб. в течение 8 лет с ежегодным начислением 6%. Начальный вклад равен 0 (рис. 4.9).

hello_html_49ccb390.png

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

Результат вычисления – 24 565, 71 руб.

Эту же формулу (см. рис. 4.9) можно использовать и для расчета величины возможного займа. Например, требуется рассчитать, какую сумму можно занять на 8 лет под 6% годовых, если есть возможность выплачивать ежемесячно по 200 руб. Результат будет тот же самый – 24 565,71 руб.

Расчет стоимости инвестиции

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

Синтаксис функции

ПС(А;В;С;D;Е),

где A – процентная ставка за период.

В– общее число платежей.

C – выплата, производимая в каждый период и не меняющаяся за все время выплаты.

D – значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0.

E – число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен – в конце периода, 1 – в начале периода.

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

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


Например, необходимо рассчитать величину вложения под 10 % годовых, которое будет ежегодно в течение 10 лет приносить доход 1000 руб. (рис. 4.10).

hello_html_m32a8b178.png

Рис. 4.10.  Расчет стоимости инвестиции

Результат вычисления получается отрицательным (-6 144,57 руб.) поскольку эту сумму необходимо заплатить.

Или, например, необходимо рассчитать величину вложения под 10 % годовых, которое через 10 лет принесет доход 10000 руб. (рис. 4.11).

hello_html_29a1b20c.png

Рис. 4.11.  Расчет стоимости инвестиции

Результат вычисления получается отрицательным (-3855,43 руб.) поскольку эту сумму необходимо заплатить.


Расчет процентных платежей

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

Синтаксис функции

ПЛТ(А;В;С;D;Е),

где A – процентная ставка за период;

В– общее число платежей;

C – выплата, производимая в каждый период и не меняющаяся за все время выплаты;

D – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0);

E – число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен – в конце периода. 1 – в начале периода.

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

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


Например, необходимо рассчитать величину ежемесячного вложения под 6 % годовых, которое через 12 лет составит сумму вклада 50000 руб. (рис. 4.12).

hello_html_69c5f91.png

Рис. 4.12.  Расчет процентных платежей

Результат вычисления получается отрицательным (-237,95 руб.), поскольку эту сумму необходимо выплачивать.

Или при тех же условиях, но с начальным вкладом 10000 руб. (рис. 4.13).

hello_html_19bb8a38.png

Рис. 4.13.  Расчет процентных платежей

Результат вычисления получается отрицательным (-335,51 руб.), поскольку эту сумму необходимо выплачивать.

Эту же формулу (рис. 4.12) можно использовать и при расчете платежей по займу. Например, необходимо рассчитать величину ежемесячной выплаты по займу в 50000 руб. под 6 % годовых на 12 лет. Результат будет тот же самый -237,95 руб.


Расчет продолжительности платежей

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

Синтаксис функции

КПЕР(А;В;С;D;Е),

где A – процентная ставка за период;

В– выплата, производимая в каждый период и не меняющаяся за все время выплаты;

C – приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей;

D – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0);

E – число 0 или 1, обозначающее, когда должна производиться выплата. 0 или опущен – в конце периода, 1 – в начале периода.

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

Например, необходимо рассчитать количество ежемесячных платежей для погашения займа в 10000 руб., полученного под 10 % годовых, при условии ежемесячной выплаты 200 руб. (рис. 4.14).

hello_html_m3e855749.png

Рис. 4.14.  Расчет количества платежей с использованием функции КПЕР

Результат вычисления – 42 ежемесячные выплаты.


Использование логических функций. О логических функциях.

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

Вместо функций ЛОЖЬ и ИСТИНА можно непосредственно ввести слово с клавиатуры в ячейку или в формулу.

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

Оператор

Значение

=

Равно

<

Меньше

>

Больше

<=

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

>=

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

<>

Не равно


Проверка и анализ данных

Для наглядного представления результатов анализа данных можно использовать функцию ЕСЛИ.

Синтаксис функции:

ЕСЛИ(А;В;С),

где A – логическое выражение, правильность которого следует проверить;

В– значение, если логическое выражение истинно;

C – значение, если логическое выражение ложно.

Например, в таблице на рис. 4.22 функция ЕСЛИ используется для проверки значений в ячейках В2:В12 по условию <0,6%. Если значение удовлетворяет условию, то функция принимает значение "ДА", а если значение не удовлетворяет условию, то функция принимает значение "нет".

hello_html_m2017d1ca.png

Рис. 4.22.  Проверка значений




Условные вычисления

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

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

Например, в таблице на рис. 4.23 при расчете стоимости товара цена зависит от объема партии товара. При объеме партии более 30 цена понижается на 10%. Следовательно, при выполнении условия используется формула B:B*C:C*0,9, а при невыполнении условия – B:B*C:C.

hello_html_m1f0d5399.png

Рис. 4.23.  Условное вычисление



ЛЕКЦИЯ 5: РАБОТА С ДИАГРАММАМИ

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

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

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

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

Диаграмма постоянно связана с данными, на основе которых она создана, и обновляется автоматически при изменении исходных данных. Более того, изменение положения или размера элементов данных на диаграмме может привести к изменению данных на листе.

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

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


Создание диаграммы

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

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

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

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

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

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

  1. Выделите фрагмент таблицы, для которого создается диаграмма.

  2. На вкладке Вставка в группе Диаграммы щелкните по кнопке с нужным типом диаграмм и в галерее выберите конкретный вид диаграммы (рис. 5.1).

hello_html_m55b46d6d.png

Рис. 5.1.  Выбор типа и вида создаваемой диаграммы

  1. На листе будет создана диаграмма выбранного вида (рис. 5.2).

hello_html_93c9e09.png

Рис. 5.2.  Созданная диаграмма

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


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

Выделение диаграммы

Все действия выполняются с выделенной диаграммой или с ее выделенными элементами.

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

hello_html_m21468f6b.png

Рис. 5.3.  Выделение диаграммы

Для выделения какого-либо элемента диаграммы следует щелкнуть по нему мышью. Одновременно может быть выделен только один элемент диаграммы.


Изменение типа диаграммы

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

  1. В группе Тип вкладки Конструктор нажмите кнопку Изменить тип диаграммы.

  2. В окне Изменение типа диаграммы требуемый тип и вид диаграммы.


Добавление и удаление элементов диаграммы

Независимо от выбранного макета диаграммы можно добавлять и удалять ее отдельные элементы. Для этого используют элементы вкладки Макет (рис. 5.4).

hello_html_m670a4219.png

Рис. 5.4.  Добавление и удаление элементов диаграммы


Например, можно добавить название диаграммы.

  1. Вкладка Макет щелкните по кнопке Название диаграммы.

  2. Выберите вариант расположения названия диаграммы.

  3. В поле названия диаграммы с клавиатуры введите название диаграммы.


Изменение положения всей диаграммы

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

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


Диаграмму, размещенную на листе с данными, можно переместить на отдельный лист.

  1. Нажмите кнопку Переместить диаграмму вкладки Конструктор.

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

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


Выбор стиля оформления

Стиль оформления диаграммы определяет, в первую очередь, цвета элементов диаграммы.

  1. В группе Стили диаграмм вкладки Конструктор щелкните по кнопке Дополнительные параметры галереи стилей.

  2. Выберите требуемый вариант (рис. 5.5)

hello_html_m6a18f282.png

Рис. 5.5.  Выбор стиля диаграммы


Оформление элементов диаграммы

Независимо от выбранного стиля диаграммы можно оформлять ее отдельные элементы. Для этого используют элементы вкладки Формат (рис. 5.6).

hello_html_1b7d173d.png

Рис. 5.6.  Оформление элементов диаграммы


hello_html_m331131c9.pnghello_html_m331131c9.pngЛЕКЦИЯ 6: РАБОТА С ДАННЫМИ


Поиск данных

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

  1. В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Найти (рис. 6.1).

hello_html_m38757fec.png

Рис. 6.1.  Поиск данных

  1. В поле Найти вкладки Найти окна Найти и заменить введите искомые данные (рис. 6.2). При поиске можно использовать подстановочные знаки: * (звездочка) заменяет любое количество любых символов; ? (знак вопроса) заменяет один любой символ.

hello_html_m1d93bc78.png

Рис. 6.2.  Ввод искомых данных

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

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


Замена данных

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

  1. В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Заменить.

  2. В поле Найти вкладки Заменить окна Найти и заменить введите искомые данные, а в поле Заменить на – заменяющие данные.

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


Сортировка данных

Сортировка – расположение данных на листе в определенном порядке.

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

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

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

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

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

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


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

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

  2. Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите направление сортировки.


Для сортировки можно также использовать кнопки группы Сортировка и фильтр вкладки Данные (рис. 12.7).

hello_html_m1fa787ba.png

Рис. 6.3.  Простая сортировка


Сортировка по нескольким столбцам

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

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

  2. Нажмите кнопку Сортировка вкладки Данные.

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

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

  5. После выбора всех необходимых уровней сортировки нажмите кнопку ОК.

hello_html_1d19ca7e.png

Рис. 6.4.  Настройка сортировки по нескольким столбцам


Отбор данных

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

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

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

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


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

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

  2. Нажмите кнопку Фильтр вкладки Данные.

  3. После установки фильтров в названиях столбцов таблицы появятся значки раскрывающих списков (ячейки A1:F1 в таблице на рис. 6.5).

hello_html_3f939adc.png

Рис. 6.5.  Таблица с установленными фильтрами

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


Работа с фильтром

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


Отбор по условию

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

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

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

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

hello_html_m1bc5ecb.png

Рис. 6.6.  Отбор по условию для числовых значений


hello_html_m331131c9.png


ЛЕКЦИЯ 7: ЗАЩИТА ИНФОРМАЦИИ

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

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

Можно применить защиту к элементам книги, ограничив просмотр отдельных листов и/или изменение данных на листе.

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

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


Пароли

Защита информации от просмотра и изменения основана на использовании паролей.

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

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

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

При вводе пароля он никогда не отображается на экране. Вместо каждого символа вводимого пароля отображается точка (рис. 7.1).

hello_html_m763e7a8b.png

Рис. 7.1.  Пример отображения пароля при вводе

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


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

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


Защита от несанкционированного просмотра. Защита файлов от открытия

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

  1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

  2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

  3. В окне Параметры сохранения введите пароль в поле Пароль для открытия и нажмите кнопку ОК.

  4. В окне подтверждения пароля введите его еще раз.

  5. В окне Сохранение документа нажмите кнопку Сохранить. Если файл сохраняется под существующим именем в исходную папку, подтвердите замену существующего файла.


Снятие пароля

Снять пароль можно только после открытия файла.

  1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

  2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

  3. В окне Параметры сохранения очистите поле Пароль для открытия и нажмите кнопку ОК.

  4. В окне Сохранение документа нажмите кнопку Сохранить. Если файл сохраняется под существующим именем в исходную папку, подтвердите замену существующего файла.


Защита листов от просмотра. Скрытие листов

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

  1. Щелкните по ярлыку скрываемого листа правой кнопкой мыши.

  2. В контекстном меню выберите команду Скрыть.

После этого обязательно необходимо защитить структуру книги.

Нельзя скрыть все листы книги. Хотя бы один лист всегда должен отображаться.

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


Скрытие формул

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

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

  2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

  3. Во вкладке Защита окна Формат ячеек установите флажок Скрыть формулы.


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


Отображение формул

Для отображения формул достаточно снять защиту листа от изменений.


Защита от несанкционированного изменения. Защита файлов

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

  1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

  2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

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

  4. В окне подтверждения пароля введите его еще раз.

  5. В окне Сохранение документа нажмите кнопку Сохранить. Если файл сохраняется под существующим именем в исходную папку, подтвердите замену существующего файла.


Снятие пароля

Снять пароль можно только после открытия файла.

  1. Нажмите кнопку Office, а затем выберите команду Сохранить как.

  2. В окне Сохранение документа нажмите кнопку Сервис выберите команду Общие параметры.

  3. В окне Параметры сохранения очистите поле Пароль для изменения и нажмите кнопку ОК.

  4. В окне Сохранение документа нажмите кнопку Сохранить. Если файл сохраняется под существующим именем в исходную папку, подтвердите замену существующего файла.


Защита всех ячеек листа

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

  1. Выделите все ячейки листа.

  2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

  3. Во вкладке Защита окна Формат ячеек должен быть установлен флажок Защищаемая ячейка.


Можно защитить как данные на листе, так и структуру листа.

  1. В группе Изменения вкладки Рецензирование нажмите кнопку Защитить лист.

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

hello_html_m3d9e8525.png

Рис. 7.2.  Установка защиты листа

  1. В окне подтверждения пароля введите его еще раз.


Защита отдельных ячеек листа

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

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

  2. Нажмите кнопку Формат в группе Ячейки вкладки Главная и выберите команду Формат ячейки или щелкните правой кнопкой мыши по выделенным ячейкам и в контекстном меню выберите команду Формат ячеек.

  3. Во вкладке Защита окна Формат ячеек снимите флажок Защищаемая ячейка.

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


Снятие защиты листа

  1. В группе Изменения вкладки Рецензирование нажмите кнопку Снять защиту листа.

  2. В окне Снять защиту листа введите пароль и нажмите кнопку ОК.


Защита книги

Можно защищать от изменений структуру книги и окно книги.

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

Под защитой окна книги понимается запрет перемещения, изменения размеров, скрытие, показ и закрытие окон.

  1. В группе Изменения вкладки Рецензирование нажмите кнопку Защитить книгу и выберите команду Защита структуры и окон (рис. 7.3).

hello_html_m243ae700.png

Рис. 7.3.  Защита книги

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


  1. В окне подтверждения пароля введите его еще раз.


Снятие защиты книги

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

  2. В окне Снять защиту книги введите пароль и нажмите кнопку ОК.


hello_html_m331131c9.pnghello_html_m331131c9.png

Общая информация

Номер материала: ДВ-168690


Очень низкие цены на курсы переподготовки от Московского учебного центра для педагогов

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

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

Подайте заявку на интересующий Вас курс сейчас: KURSY.ORG

Похожие материалы

Только до конца зимы! Скидка 60% для педагогов на ДИПЛОМЫ от Столичного учебного центра!

Курсы профессиональной переподготовки и повышения квалификации от 1 400 руб.
Для выбора курса воспользуйтесь удобным поиском на сайте KURSY.ORG


Вы получите официальный Диплом или Удостоверение установленного образца в соответствии с требованиями государства (образовательная Лицензия № 038767 выдана ООО "Столичный учебный центр" Департаментом образования города МОСКВЫ).

Московские документы для аттестации: KURSY.ORG


Вам будут интересны эти курсы:

Курс повышения квалификации «Табличный процессор MS Excel в профессиональной деятельности учителя математики»
Курс повышения квалификации «Информационные технологии в деятельности учителя физики»
Курс повышения квалификации «Методика преподавания информатики в начальных классах»
Курс повышения квалификации «Современные информационные технологии и их использование в работе преподавателей. Системы автоматизированного проектирования одежды и организация технологического процесса»
Курс повышения квалификации «Основы создания интерактивного урока: от презентации до видеоурока»
Курс повышения квалификации «Организация работы по формированию медиаграмотности и повышению уровня информационных компетенций всех участников образовательного процесса»
Курс «Фирменный стиль» (Corel Draw, Photoshop)
Курс «1С: Предприятие 7.7»
Курс «WEB-ВЕРСТКА (HTML, CSS)»
Курс повышения квалификации «Использование компьютерных технологий в процессе обучения в условиях реализации ФГОС»
Курс повышения квалификации «Специфика преподавания информатики в начальных классах с учетом ФГОС НОО»
Курс повышения квалификации «Введение в программирование на языке С (СИ)»
Курс профессиональной переподготовки «Теория и методика обучения информатике в начальной школе»
Курс повышения квалификации «Современные тенденции цифровизации образования»
Курс повышения квалификации «Специфика преподавания дисциплины «Информационные технологии» в условиях реализации ФГОС СПО по ТОП-50»