Методическая разработка "Электронные таблицы Excel"

МИНОБРНАУКИ РОССИИ

Федеральное государственное бюджетное  образовательное учреждение высшего образования

«Ухтинский государственный технический университет» (УГТУ) Индустриальный институт (СПО)

МЕТОДИЧЕСКАЯ РАЗРАБОТКА

« Электронные таблицы Excel»

Разработал: преподаватель

ИИ(СПО) УГТУ

Лихачева Татьяна Эдуардовна

Ухта,2022

ОГЛАВЛЕНИЕ

Обработка данных с помощью электронных таблиц.......................................................................... 4

Основные понятия электронных таблиц.............................................................................................. 4

Ввод, редактирование и форматирование данных.............................................................................. 5

Выравнивание и изменение ориентации текста и чисел..................................................................... 6

Форматирование содержимого ячеек................................................................................................... 7

Вычисления в электронных таблицах................................................................................................... 8

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

Ссылки на ячейки................................................................................................................................... 9

Копирование содержимого ячеек........................................................................................................ 12

Автоматизация процесса ввода........................................................................................................... 12

Использование стандартных функций................................................................................................ 13

Построение диаграмм и графиков....................................................................................................... 16

ТЕСТ...................................................................................................................................................... 17

Практическая работа 1. Создать таблицу, рассчитать столбцы, построить диаграмму................ 18

Практическая работа 2......................................................................................................................... 18

Практическая работа 4. «Мои биоритмы»......................................................................................... 18

Практическая работа 3. Определение точки безубыточности. ......................................................... 18 ....................................................................................................................................................................

Практическая работа 5. «База данных».............................................................................................. 18

Контрольная работа.............................................................................................................................. 33 

 ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL

Введение

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

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

•      создавать и редактировать таблицу;

•      производить различные расчеты в таблице;

•      отбирать из таблицы необходимую информацию по определенным условиям;

•      оформлять необходимым образом таблицу перед печатью;

•      строить и изменять диаграммы на основе данных таблиц;

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

Программа Excel функционирует в операционной среде Windows. Для запуска Excel  из операционной системы Windows необходимо:

•      выполнить щелчок по кнопке Пуск на панели задач

•      выбрать пункт Все программы- Microsoft Office- Microsoft Excel.

Обработка данных с помощью электронных таблиц

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

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

ª  Автоматизации итоговых вычислений

ª  Подготовки табличных документов

ª  Ведения простых баз данных и их обработки

ª  Построения диаграмм и графиков

ª  Создания сводных таблиц

ª  Совместной работы с экономическими и бухгалтерскими документами

Среди наиболее известных программных приложений, предназначенных для ведения электронных таблиц, могут быть названы SuperCalc, Lotus 1-2-3, Quattro Pro. Программа Microsoft Excel, входящая в состав офисного пакета Microsoft Office 2000, занимает лидирующее положение среди электронных таблиц, совмещая в себе преимущества как электронных таблиц со средствами финансового и статистического анализа, так и средств визуального программирования посредством встроенного языка программирования Visual Basic for Applications (VBA). VBA позволяет автоматизировать весь комплекс работ от сбора информации, её обработки, анализа до создания отчётной документации и её публикации как для офисного пользования, так и на Web-узлах.

   Основные понятия электронных таблиц

Документ, созданный в MS Excel, называется рабочей книгой. Рабочая книга состоит из набора рабочих листов. Листов в книге Excel может быть до 255. Каждый рабочий лист имеет имя, по умолчанию Лист1, Лист2, Лист3. Названия листов отображаются в нижней части листа. С помощью ярлычков, на которых написаны имена листов, можно переключаться между рабочими листами, входящими в рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть на его ярлычке, либо нажать правой кнопкой мыши на ярлычке и выбрать пункт Переименовать. Листам можно давать произвольные имена длиною до 31-го символа,

исключая символы * : / \  ? [ ]  

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

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

Пространство рабочего листа состоит из строк и столбцов. По умолчанию каждый рабочий лист имеет 256 столбцов, (каждый столбец имеет имя, озаглавленное латинской буквой) и 65536 строк (нумерация от 1 до 65536), таким образом, всего на листе имеется 16777216 ячеек.

На пересечении строк и столбцов образуются ячейки таблицы. Ячейка – это минимально адресуемый элемент рабочего листа.  Имя ячейки (адрес) состоит из имени столбца и номера строки, например, A10 или D23. Адресация ячеек используется при записи формул. Одна из ячеек всегда является активной, и в ней  производятся операции ввода и редактирования. Группа ячеек называется диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах, например: А5:C20.

Выделение ячеек с помощью мыши

Таблица 1

Чтобы выделить:	Действия пользователя
Лист	Щёлкнуть мышью на сером прямоугольнике, расположенном на пересечении строки с номером 1 и столбца с именем А
Столбец	Щёлкнуть на заголовке столбца (на имени столбца)
Строку	Щёлкнуть на номере строки
Ячейку	Щёлкнуть на ячейке мышью
Диапазон несмежных ячеек	Выделить мышью первый диапазон. Удерживая клавишу CTRL, выделить другой диапазон и т. д.

Ввод, редактирование и форматирование данных

Ввод текста и чисел

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

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

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

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

ª  Ввести данные с клавиатуры

ª  Нажать клавишу Enter

или

ª  Сделать двойной щелчок мышью на нужной ячейке

ª  Ввести данные с клавиатуры

ª  Нажать клавишу Enter

Редактировать данные можно также несколькими способами, например:

Для замены одних данных в ячейке другими следует сделать её активной и ввести новые данные.

или

ª  Сделать активной ячейку

ª  Выполнить двойной щелчок на редактируемой ячейке

или

ª  Сделать активной ячейку

ª  Нажать клавишу F2

или

ª  Сделать активной ячейку

ª  Сделать щелчок в строке формул

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

 Выравнивание и изменение ориентации текста и чисел

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

Рисунок 1

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

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

ª  Выполните команду Формат / Ячейки. На экране отобразится диалоговое окно Формат ячеек (рис.2)

Рисунок 2

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

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

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

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

ª  Выполнить команду Формат / Ячейки. Откроется диалоговое окно Формат ячеек (рис.3)

ª  Выберите вкладку Число этого диалогового окна и установите тот тип формата, который вам нужен

Рисунок 3

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

Таблица 2

Формат	Описание
Общий	Этот формат принят по умолчанию
Числовой	Число отображается с заданным количеством десятичных знаков после запятой
Денежный	После изображения числа может помещаться знак валюты
Финансовый	Отличается от денежного вынесением знака минус для отрицательных чисел в крайнюю левую позицию ячейки
Дата	Эта категория позволяет вывести дату по одному из пятнадцати форматов
Время	Эта категория позволяет вывести время по одному из восьми форматов
Процентный	Этот формат позволяет выводить число в виде процентов со знаком % и определять количество выводимых знаков после десятичной точки
Дробный	Дробный формат используется для представления чисел в виде смешанного числа
Экспоненциальный	В этом формате число представляется в виде mEp, где m – мантисса числа, Е – символ, обозначающий основание десятичной ССЧ, p – порядок числа, например число 5,341025 записывается как 5.34Е+25
Текстовый	Применение этого формата к числовому значению позволяет рассматривать его как текст
Дополнительный	Дополнительный формат предназначен для работы с почтовыми индексами, телефонными номерами, адресами
Все форматы	Эта категория позволяет создавать пользовательские форматы

Вычисления в электронных таблицах

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

Примеры формул

Таблица 3

Формула	Описание
=В1+В2	Складывает содержимое ячеек В1 и В2
=А1^(1/3)	Возводит в степень (1/3) содержимое ячейки А1
=СУММ(А1:А5)	Возвращает сумму значений из диапазона А1:А5

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

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

Таблица 4

Ошибка	Описание
#Н/Д	Н/Д – является сокращением термина Неопределённые Данные. Помогает предотвратить использование ссылки на пустую ячейку
#ЧИСЛО!	Функция с числовым аргументом использует неприемлемый аргумент
#ИМЯ?	Ошибка в написании имени или используется несуществующее имя
#ССЫЛКА!	Используется ссылка на несуществующую ячейку
#ЗНАЧ!	Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных
#ДЕЛ/0!	В качестве делителя используется ссылка на ячейку, в которой содержится ненулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль)
#ПУСТО!	Используется ошибочная ссылка на ячейку
#############	Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца

   Ссылки на ячейки

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

ª  Ввести адрес ячейки с клавиатуры

ª  Выполнить щелчок на нужной ячейке или выделить мышью нужный диапазон ячеек.

 

Рисунок 4

В Excel при работе с формулами используется два типа адресации ячеек: относительная и абсолютная.

По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные. Это означает, что адреса ячеек при копировании формул из одной ячейки в другую изменяются автоматически, то есть меняются относительно исходной формулы. Как видно из примера на рис.5 при копировании формулы, которая находится в ячейке С1, происходит автоматическая переадресация ячеек.

Рисунок 5

При абсолютной адресации адреса ячеек при копировании формулы не изменяются. На рис. 6 показан пример из которого видно, что если копировать формулу =В2*В4, которая находится в ячейке С4 обычным способом с помощью маркера автозаполнения, то результат будет неверным, потому что адресация будет меняться относительно исходной формулы. А нам нужно, чтобы адрес ячейки B2 не изменялся. Для того чтобы создать абсолютную ссылку на ячейку, достаточно перед именем строки и столбца поставит знак $ (можно использовать клавишу F4). (рис.7)

Рисунок 6

Рисунок 7

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

ª  Абсолютная ссылка на строку. В этом случае знак $ ставится только перед номером строки. Например, С$4 – это абсолютная ссылка на четвёртую строку.

ª  Абсолютная ссылка на столбец. В этом случае знак $ ставится только перед именем столбца. Например, $С4 – это абсолютная ссылка на столбец С.

В качестве примера можно привести расчёт таблицы умножения (рис. 8). В ячейку В2 следует ввести формулу:   =$A2*B$1 и растянуть её за маркер заполнения на диапазон B2:J10.

Рисунок 8

   Копирование содержимого ячеек

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

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

С помощью команды Правка / Копировать и Правка / Вставить (копирование) или Правка / Вырезать и Правка / Вставить (перемещение). 

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

Рисунок 9

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

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

 Автоматизация процесса ввода

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

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

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

Чтобы точно сформулировать условия заполнения ячеек следует выполнить команду Правка / Заполнить / Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке Ок Excel автоматически заполняет ячейки в соответствии с заданными критериями.

 

Рисунок 10

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

Функции в Excel используются для выполнения стандартных вычислений. При вводе формулы обращение к встроенной функции можно осуществить с клавиатуры, либо вызвать Мастер функций. Для этого в Excel есть соответствующая кнопка на стандартной панели: Вставка функции, а также пункт меню Вставка / Функция. В появившемся окне Мастер функций - шаг 1 из 2 пользователь может обратиться к более 400 встроенным функциям, которые объединены в девять групп:

ª  Финансовые

ª  Дата и время

ª  Математические

ª  Статистические

ª  Ссылки и массивы

ª  Работа с базой данных

ª  Текстовые

ª  Логические

ª  Проверка свойств и значений

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

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

 

Рисунок 11

В таблице 5 приведён неполный список категории Математические.

 Таблица 5

Функция	Назначение
ABS	Возвращает модуль (абсолютную величину) числа
ACOS	Возвращает арккосинус числа. Если нужно преобразовать результат из радиан в градусы, то умножьте его на 180/ПИ()
ASIN	Возвращает арксинус числа
ATAN	Возвращает арктангенс числа
COS	Возвращает косинус заданного угла
EXP	Возвращает число е, возведённое в указанную степень
LN	Возвращает натуральный логарифм числа
LOG	Возвращает логарифм числа по заданному основанию. Если основание опущено, то оно полагается равным 10
LOG10	Возвращает десятичный логарифм числа
SIN	Возвращает синус заданного угла
TAN	Возвращает тангенс заданного угла
КОРЕНЬ	Возвращает положительное значение квадратного корня из неотрицательного числа
МОБР	Возвращает обратную матрицу
МОПРЕД	Возвращает определитель матрицы
МУМНОЖ	Возвращает произведение матриц
НЕЧЁТ	Возвращает число, округлённое до ближайшего нечётного целого
ОСТАТ	Возвращает остаток от деления
ОТБР	Усекает число до целого, отбрасывая дробную часть числа так, что остаётся целое число
ПИ()	Возвращает значение числа . У этой функции пустой список аргументов
ПРОИЗВЕД	Возвращает произведение чисел, заданных в качестве
	аргументов
РАДИАНЫ	Преобразует градусы в радианы
СТЕПЕНЬ	Возвращает результат возведения числа в степень
СУММ	Возвращает     сумму     всех     чисел,     входящих     в      список аргументов
ФАКТР	Возвращает факториал числа
ЧЁТН	Возвращает число, округлённое до ближайшего чётного целого

В таблице 6 приведён неполный список категории Дата и время.

Таблица 6

Функция	Назначение
ГОД	Возвращает год, соответствующий указанной дате
ДАТА	Возвращает порядковый номер указанной даты
ДЕНЬ	Возвращает день месяца указанной даты
ДЕНЬНЕД	Возвращает номер дня недели указанной даты
ДНЕЙ360	Возвращает количество дней между двумя датами на основе 360-дневного года
МЕСЯЦ	Возвращает месяц, соответствующий указанной дате
МИНУТЫ	Возвращает минуты, соответствующие указанной дате
СЕГОДНЯ	Возвращает текущую дату в числовом формате
СЕКУНДЫ	Возвращает секунды, соответствующие указанной дате
ТДАТА	Возвращает текущую дату и время в числовом формате
ЧАС	Возвращает час, соответствующий указанной дате

В таблице 7 приведён список категории Логические функции.

Таблица 7

Функция	Назначение
ЕСЛИ	Возвращает первое значение, если логическое выражение при вычислении даёт значение ИСТИНА, и второе значение, если ЛОЖЬ
И	Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ
ИЛИ	Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ
ИСТИНА	Возвращает логическое значение ИСТИНА
ЛОЖЬ	Возвращает логическое значение ЛОЖЬ
НЕ	Меняет на противоположное логическое значение аргумента

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

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

 Рисунок 12 

ТЕСТ

1. После запуска Excel в окне документа появляется незаполненная…. 

a)            рабочая книга 

b)            тетрадь 

c)            таблица 

d)            страница 

2. Запись ######## в ячейке говорит о том, что.. 

a)            непонятна формула

b)            столбец недостаточно широк

c)            неверна ссылка

d)            ссылка циклическая

3. Каждая книга состоит из: 

a)            нескольких листов 

b)            256 столбцов 

c)            нескольких строк (65536) 

d)            ячеек 

4. Группу ячеек, образующих прямоугольник называют: 

a)      прямоугольником ячеек 

b)      диапазоном ячеек 

c)      интервалом ячеек 

d)      ярлыком 

5.В ячейку электронной таблицы нельзя внести: 

a)      текст;

b)      формулу;

c)      иллюстрацию

d)      число

6.      Сколько максимально в каждом файле может размещаться рабочих листов:  a) 255; 

b)      3

c)      10

d)      1000; 

7.      В какую категорию входит функция СУММ: 

a)      математическую; 

b)      статистическую; 

c)      финансовую.

d)      Дата время

8.      С какого символа начинается формула в Excel? 

a)      =

b)      +

c)      пробел

d)      все равно с какого

9.      На основе чего строится любая диаграмма? 

a)      книги Excel

b)      графического файла

c)      текстового файла

d)      данных таблицы

10.  Какая из ссылок является абсолютной? 

a)      С22

b)      R1C2

c)      $A$5

d)      #A#5

11.  Результатом вычисления в ячейке С1 будет:

	А	В	С
1	10	=А1*2	=СУММ(А1:В1)/5

a)      100

b)      6

c)      20

d)      35

12 Результатом вычислений в ячейке С2 при копировании формулы ячейки С1 будет:

	А	В	С
1	2	3	=A$1*B1
2	4	5

a)      6

b)      20

c)      10

d)      12

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

Практическая работа 2. 

	A	B		D	E	F	G
	ООО "Аленький цветочек"
2	Прайс лист на		12.01.2011
3	Курс $=	29,98
4	№ поз.	Наименование товара	Цена ($)	Цена (руб)	Ед.изм.	Кол-во	Стоимость (руб)
5	1	Палочка волшебная	300		шт.	3
6	2	Зелье-приворотное	100		литр	10
	3	Вода живая	50		литр	0,5
8	4	Вода мертвая	400		литр	0,7
9	5	Ковер-самолет	1200		шт.	1
10	6	Скатерть-самобранка	700		шт.	8
11	7	Сапоги-скороходы	200		пар.	12
12	8	Лягушка-царевна	5000		шт.	6
13		ИТОГО:			-	-
14

16 ж к Courier New

12 ж Arial Black

10 ж Bookman Old Style

10 Arial Cyr

1     Создайте таблицу.

2     Номера позиций введите, используя автозаполнение.

3     Напишите, используя абсолютную адресацию, в ячейке D5 формулу для вычисления цены товара в рублях  =C5*$B$3, затем за маркер заполнения  скопируйте ее до D12.

4     Напишите, используя относительную адресацию, в ячейке G5 формулу для стоимости =D5*F5, затем за маркер заполнения  скопируйте ее до G12.

5     Используя автосуммирование  Σ , вычислите "ИТОГО" в ячейках C13, D13, G13.

6     Нанесите сетку таблицы там, где это необходимо.

7     Сохраните документ под именем Price.xls.

8     Измените курс доллара на 31,25. Посмотрите , что изменилосью

9     Поменяйте произвольно количество товара. Посмотрите, что изменилось.       

                                                                                                                                                                             20

 

Практическая работа 4. «Мои биоритмы».

.                                                     

Самочувствие человека зависит от циклов его биоритмов: физического – способность к физическим нагрузкам; эмоционального – настроение; интеллектуального – умственная деятельность.

Каждый биоритм изменяет свое значение во времени от самого благоприятного до минимального. Биоритмы изменяются циклически, но не синхронно. Цикл для физического биоритма составляет 23 дня, для эмоционального – 28, а для интеллектуального – 33 дня. 

Для построения графика биоритмов нужно задать дату рождения (ячейка В1) и дату начала расчета (ячейка В2) Вставка-Функция-Сегодня.

День- ввести ссылку на сегодняшнюю дату, для последующих дней +1.

Формула     для      вычисления    физического состояния:

=SIN(2*ПИ()*(A5-$B$1)/23)

Для расчета эмоционального состояния число 23 нужно заменить на 28, а для интеллектуального – на 33.

Практическая работа 3. Определение точки безубыточности.

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

Рисунок 1.

Создаем таблицу. Заполняем ячейки В5,С4,С5, в ячейке В9 вводим формулу

(см.рис.1) Из меню Сервис выбираем команду Подбор параметра . 

 

Выполнение команды Подбор параметра позволяет подобрать для формулы =C4*B8C5*B8-B5 конкретное значение – количество единиц товара.

Устанавливаем      параметры      (см.рис.2)       и нажимаем кнопку ОК.

                 Рисунок 2

Найденное значение – это тот результат,       который          необходимо получить, т.е. 0, поскольку для точки безубыточности          Общие            доходы- Общие расходы =0 ( см.рис.3)

                 Рисунок 3.

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

Создать и заполнить таблицу (см.рис.4).

 Функция для определения доходов (В10)= это Доходы на единицу (С4) * Количество единиц (В7). Функция для вычисления расходов (С10) – это фиксированные расходы(В5)+(Переменные расходы (С5)*Количество единиц (В7)).

Рисунок 5 

Для построения графика выделяем диапазон ячеек, содержащих полученные значения расходов и доходов (В11:С14). Из меню Вставка выбираем пункт Диаграмма и  выполняем все необходимые операции для получения графика (см.рис.6).

определение точки безубыточности

Доходы           Расходы

      Рисунок 6

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

Практическая работа 5. «База данных».

  .

1. На листе Справочники создать именованные блоки (см.рис.1): Порядок выполнения:

Переименовать Лист 1 в Справочники  (Щелкаем правой кнопкой мыши на подписи Лист 1 и выбираем Переименовать). Создаем и оформляем таблицы

                                              Рис.1.

Выделяем блок  ячеек А3:А8 Вставка –Имя- Присвоить- Наимен_услуг

Выделяем блок  ячеек B3:B8 Вставка –Имя- Присвоить- Цена

Выделяем блок  ячеек D3:D6 Вставка –Имя- Присвоить- Льгота Выделяем блок  ячеек Е3:Е6 Вставка –Имя- Присвоить- Процент_скидки

Создать и оформить таблицу. Ввести данные в столбцы А и С (заполнение остальных  столбцов рассмотрено ниже) .

•        Для ввода Наименования из списка выделяем ячейки В3:В9 . Данные –Проверка. Тип данных – Список, Источник нажимаем клавишу F3 и выбираем Наимен_услуг. Нажимаем ОК. (рис.3)

Заполняем Наименование через раскрывающийся список (данные на рис.2).        Аналогично создаем список Льгота.

•        Для подстановки Цены воспользуемся функцией ПРОСМОТР В ячейке Е3 Вставка-Функция- ПРОСМОТР

Просматриваемый_вектор и Вектор_результата выбираем имя  с помощью клавиши F3.

             Вычисляем Стоимость без скидки 

Стоимость без скидки=Цена*Количество (F3=E3*C3)  Копируем формулу на другие ячейки.

 Стоимость со скидкой= Стоимость без скидки* (1-Процент Скидки) Процент скидки задаем с помощью функции ПРОСМОТР

Стоимость со скидкой

Вставка- Функция- ПРОСМОТР 

Копируем функцию на другие ячейки.

3. На листе Итоги получить общую стоимость без скидки и стоимость со
скидкой услуг оптики по наименованию услуг.

Скопировать таблицу Учет заказов оптики из листа Учет в лист Итоги.

Отсортировать данные по полю Наименование:

Выделяем ячейки А2:G8 Данные- Сортировка

Подведем промежуточные итоги . 

Выделяем всю таблицу A1:G8  Данные – Итоги.

В итоге получается таблица

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

Выделяем итоговые значения Ст.без скид. и Ст. со скид. (F4:G14)

Вызываем мастер диаграмм:

Вставка-Диаграмма

Выбираем тип : Гистограмма. Нажимаем кнопку Далее.

На закладке Ряд в поле Подпись оси Х нажимаем на красную стрелку и в таблице выделяем Наименование(B4:B14). Здесь же даем имя рядам. Нажимаем Далее.

На 3 шаге указываем название диаграммы и подписываем оси.

Легенду размещаем внизу и подписываем значения.

Размещаем диаграмму на отдельном листе.

 

Получаем

диаграмму

Смешенная диаграмма: Щелкаем на одном из столбце

Диаграмма- Тип диаграммы

На закладке Нестандартные выбираем тип График

|гистограмма

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

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

Выделить всю таблицу

Данные- Сводная таблица

Открывается Мастер сводных таблиц и диаграмм. 

Перетаскиваем элементы 

(Льгота в А1, Наименование в А5,Ст.без скид. в В5, Ст.со скид. в В5)

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

Параметры поля - Операция: среднее

Получаем таблицу.

Выбрать любую льготу и просмотреть значения. 

6. Средствами MS Word создать и оформить документ Учет заказов
установив связь с таблицей листа Связывание.

             В Word оформим документ Учет заказов, используя на панели

Рисование инструмент WordArt, овал, градиентную заливку. (см.рис. 4)

   Из Excel скопировать таблицу Учет заказов оптики  в Excel на Листе Связывание выделяем таблицу Правка -Копировать 

Переходим в Word 

Правка- Специальная вставка

Рис.4.

Контрольная работа

1.      На листе Справочники создать 2  таблицы

Справочник распределения рабочих по цехам и разрядам				Справочник тарифов
Фамилия И.О.	Разряд	Цех		Разряд	Тариф, руб/час
Иванов	5	А		5	50,00р.
Петров	6	Б		6	60,00р.
Сидоров	5	Б		7	70,00р.
Петренко	6	Б
Иваненко	6	В
Сидоренко	7	А
Артеев	6	В
Прохоров	5	А
Мишин	7	Б
Арбузов	7	А
Котомин	5	В
Яшин	6	В
Егоров	6	А
Сименов	7	Б
Громов	7	Б

Отсортировать первую таблицу по ФИО

2.      На листе Ведомость 1 создать таблицу.

Фамилия И.О.

Отработанное время, час

ФИО скопировать из первой таблицы. Отработанное время заполнить самостоятельно.

3.      На листе Ведомость зарплаты создать таблицу

Фамилия И.О.

Начислено, руб.

Начислено, руб= Отработанное время, час* Тариф, руб/час.

Копируем Отработанное время , Разряд.

В следующем столбце вводим Тариф, руб/час. и заполняем значениями , используя функцию ПРОСМОТР.

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

5.      Привести столбиковую диаграмму распределения сумм зарплаты по цехам.

6.      Найти цех с максимальным ФЗП.