Цель: Изучить основные возможности форматирования электронных таблиц и логические функции, используемые в MS Excel 

Оборудование: ПК, программа MS Excel

Теоретические сведения

Теоретический материал по теме электронных таблиц представлен в лекции Excel. Возможности динамических таблиц. 

Сведения о статистике 

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

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

Статистика имеет многовековую историю, но как наука стала развиваться в середине 17 века. Статистика имеет происхождение от латинского слова «status» - что означает положение, определение вещей. В научный обиход слово статистика было введено нем. ученым Ахенвалем в 1749 году. Достоверные текстовые данные тогда использовались очень мало.

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

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

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

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

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

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

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

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

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

Автозаполнение

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

Если выделить одну ячейку и нажать на «+» и удерживая потянуть в необходимую сторону, то выполнится автозаполнение ячеек. Например, если ячейка А1 содержит значение 1, то при автозаполнении ячеек А2:А5 результат будет как на рисунке ниже.

Если ввести в ячейку А1 значение 1, а в ячейку А2 значение 2, то при выделении двух данных ячеек и выполнении автозаполнения, остальные ячейки заполнятся на основе двух предыдущих

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

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

Границы ячеек. Заливка

Границы ячеек и цвет заливки позволяют создать четкие и определенные границы разделов листа Excel.  

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

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

2. На вкладке "Главная" нажмите на стрелку рядом с командой "Границы". Появится выпадающее меню с различными типами границ.

3. Выберите необходимый тип. 

4. Границы отобразятся в соответствии с выбранным типом.

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

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

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

2. На вкладке "Главная" нажать на стрелку рядом с командой "Цвет заливки". Появится меню с цветовой палитрой.

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

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

Некоторые функции в Excel

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

Функция ЕСЛИ имеет всего три аргумента:

=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)

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

В данном примере функция ЕСЛИ в первую очередь проверят условие A1>25. Если это так, то формула возвратит текстовую строку "больше 25", в любом другом случае - "меньше или равно 25".

Функция СРЗНАЧ

Возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон А1: A20 содержат числа, формула =СРЗНАЧ (А1: A20) Возвращает среднее арифметическое этих чисел. 

СРЗНАЧ(число1;[число2];…)

Аргументы функции СРЗНАЧ описаны ниже.

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

• Число2...    Необязательный. Дополнительные номера, ссылки на ячейки или диапазоны, для которых вычисляется среднее значение максимум в 255.

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

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

Наиболее часто используемые функции

СУММ Суммирует все числа в интервале ячеек  Синтаксис: СУММ(число1;число2) Пример: =СУММ(3;2) или =СУММ(A2:A4)

СЧЁТ Подсчитывает количество всех непустых значений указанных ячеек Синтаксис: СЧЁТ(значение1, [значение2],…) Пример: =СЧЁТ(A5:A8) 

СРЗНАЧ Возвращает среднее значение всех непустых значений указанных ячеек Синтаксис: СРЕДНЕЕ(число1, [число2],…) Пример:=СРЗНАЧ(A2:A6) 

ОКРУГЛ – округлит число до заданного количества десятичных разрядов. В поле «Число» – укажите адрес нужной ячейки, в поле «Число разрядов» – укажите количество знаков после запятой. Если написать «0», округление произойдет до целого числа.

МАКС Возвращает наибольшее значение из набора значений Синтаксис: МАКС(число1;число2; …) Пример:=МАКС(A2:A6) 

МИН Возвращает наименьшее значение из набора значений Синтаксис: МИН(число1;число2; …) Пример:=МИН(A2:A6) 

ЕСЛИ Проверка условия. Функция с тремя аргументами: первый аргумент — логическое выражение; если значение первого аргумента — истина, то результатом выполнения функции является второй аргумент. Если ложно — третий аргумент.
Синтаксис: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Пример:=ЕСЛИ(A2>B2;"Превышение";"ОК") 

СЧЁТЕСЛИ  Количество непустых ячеек в указанном диапазоне, удовлетворяющих заданному условию. Синтаксис: СЧЁТЕСЛИ(диапазон, критерий) Пример:=СЧЁТЕСЛИ(A2:A5;"яблоки") 

СУММЕСЛИ Сумма непустых ячеек в указанном диапазоне, удовлетворяющих заданному условию Синтаксис: СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования]) Пример:=СУММЕСЛИ(B2:B25;">5") 

МОДА Поиск повторяющихся либо наиболее часто встречающихся значений в диапазоне и возвращает эти значения Синтаксис: МОДА(число1;[число2];...) Пример:=МОДА(A2:A7) 

Вычитание Для простого вычитания используется арифметический оператор - (минус). Пример: если ввести в ячейку =10-5, то результат: 5. =A1-C4 (функции ВЫЧЕСТЬ в Excel не существует) 

Практическая часть

Часть 1

Для ячеек с числами применить тип данных «Числовой» Для этого выделяем необходимый диапазон, переходим на вкладку "Главная" – Число – "Общий" меняем на "Числовой".

Для столбца "Изменение цены в %" указать формул для расчета:
Изменение цены равно разности цены на 01.04.20 и цены на 01.06.20. (например, =D3-B3). Для данного столбца необходимо выбрать тип «Процентный».

Для каждого столбца рассчитать среднее значение используя мастер функции. Функция СРЗНАЧ находится в разделе «Статистические»

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

В строке «Лог_выражение» задайте условие План расхода > Фактически израсходовано (вместо названий столбцов необходимо указать ячейки которые содержат значения, например C4>D4).

В строке «Значение_если_истина» укажите формулу Базовая ставка*0,25. (например, Е4*0,25) 

В строке «Значение_если_ложь» напишите 0.

Произвести сортировку по столбцу с премиями по убыванию (Главная - Редактирование - Сортировка и фильтр).

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

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

Формулы для расчета:  

1. Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделия  (например: =C6*$E$3)  

2. Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия  (например: =C7*C6) 

3.  Прибыль от реализации продукции = Выпуск продукции - Себестоимость выпускаемой продукции   (например: =C8-C9) 

4. Рентабельность продукции = Прибыль от реализации продукции / Себестоимость выпускаемой продукции   (например: =C10 / С9). Примените Процентный формат чисел (Главная – Число – Процентный). 

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

Контрольные вопросы части 1

1. В чем состоит особенность статистики?

2. Дайте определение понятию «Статистика».

3. Примеры функций в Excel

4. Как работает функция ЕСЛИ

5. Структура функции ЕСЛИ

6. Что такое автозаполнение?

7. Какие бывают типы данных

8. Чем отличается абсолютная адресация от относительной? Примеры

9. Задание. Преобразовать формулу (выдается преподавателем)

Часть 2

Пример решения заданий из 2 части

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

Решение

Необходимо найти количество ячеек с температурой больше 25. То есть условие значение ячейки >25

За подсчет количества отвечает функция Счет, а за условия функция ЕСЛИ. Есть функция, которая объединяет необходимые функции в одну СЧЕТЕСЛИ

Функция СЧЁТЕСЛИ имеет два аргумента: диапазон и критерий.

Диапазон: все ячейки с температурой

Критерий (условие) >25

Итого формула: =СЧЁТЕСЛИ(B2:Y92;">25")

Пример закрепления области 

Цель: изучить принципы построения 2-D и 3-D диаграмм в MS Excel, а также средств их форматирования. 

Оборудование: ПК, MS Excel

Теоретические сведения

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

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

Элементы диаграммы представлены на рисунке ниже

В зависимости от выбранного типа диаграммы можно получить различное отображение данных:

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

• график отражает тенденции изменения данных за определенные промежутки времени;

• круговые диаграммы предназначены для наглядного отображения соотношения частей и целого

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

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

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

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

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

• пузырьковая диаграмма представляет разновидность точечной диаграммы, где два значения определяют положение пузырька, а третье – его размер;

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

Сделаем подпись для вертикальной оси. Вкладка «Макет» - «Подписи» - «Названия осей». Выбираем вертикальную ось и вид названия для нее.

Вводим «Сумма». Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.

Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).

После нажатия на кнопку «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.

Перемещение осей

Для того, чтобы изменить оси местами нужно нажать ПКМ по диаграмме и выбрать пункт «Выбрать данные».

В открывшемся меню нажимаем кнопку «Строка/столбец». Значения для рядов и категорий поменяются местами автоматически.

Закрепление элементов управления на диаграмме

Если очень часто приходится добавлять в гистограмму новые данные, каждый раз менять диапазон неудобно. Оптимальный вариант – сделать динамическую диаграмму, которая будет обновляться автоматически. А чтобы закрепить элементы управления, область данных преобразуем в «умную таблицу».

Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».

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

Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» - «Таблица».

Диаграмма в процентах

Представлять информацию в процентах лучше всего с помощью круговых диаграмм. Выделяем данные A1:B8. «Вставка»-«Круговая» - «Объемная круговая».

Вкладка «Конструктор» - «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.

Автоматически созданный вариант не решает задачу. После нажатия ПКМ по любому сектору появятся точки-границы. Выбираем пункт меню «Формат ряда данных».

Практическая часть

Часть 1

Задание №2. 

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

Округлите с помощью функции оценку за год до целого числа.

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

Примеры оформления результатов:

Задание №3. 

Построение рисунка «ЗОНТИК»
Приведены функции, графики которых участвуют в этом изображении:

y1= -1/18x^2 + 12, x∈[-12;12]

y2=-1/8x^2 +6, x∈[-4;4]

y3=-1/8(x+8)^2 + 6, x∈[-12; -4]

y4= -1/8(x-8)^2 + 6, x∈[4; 12]

y5= 2(x+3)^2 - 9, x∈[-4;0]

у6=1,5(x+3)^2 - 10, х∈[-4;0]

Порядок выполнения

1. Запустить MS EXCEL

2. В ячейке Внести обозначение переменной х

3. Заполнить диапазон ячеек А2:А26 числами с -12 до 12.

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

Для у1= -1/18x2 + 12, x∈[-12;12]

Порядок выполнения действий:

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

2.В ячейку В2 вводим формулу =(-1/18)*А2^2 +12

3. Нажимаем Enter на клавиатуре

4. Автоматически происходит подсчет значения функции.

5.Растягиваем формулу до ячейки В26 так как диапазон -12 до 12

6. Аналогично в ячейку C10 (т.к значение функции находим только на отрезке х∈[-4;4]) вводим формулу для графика функции у2=-1/8x^2+6: 
= (-1/8)*A10^2+6

Задание №4.  

Дан фрагмент электронной таблицы. Какое целое число должно быть записано в ячейке C1, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек А2:С2 соответствовала рисунку?

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

Часть 2

Задание №1.  

Ввести данные в электронные таблицы Excel, высчитать стоимость и построить по данным стоимости диаграмму.

Задание №4.  

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

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

Контрольные вопросы

1. Что такое лист диаграммы?

2. Что такое внедренная диаграмма?

3. Перечислить элементы диаграммы

4. Для представления чего можно использовать линейчатые диаграммы и гистограммы?

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

6. Для представления чего можно использовать графики?

7. Что такое легенда для диаграммы?

8. Как добавить данные в диаграмму?

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

Задание 1. Продажа билетов в театре

Оформите схему зала на Листе 1 программы в соответствии с файлом «Образец Задания 1 в MS Excel». Необходимо просто перенести данные, цветовое оформление не требуется на данном этапе. 

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

1. Введите стоимость мест в театре 400, 500, 600, 800, 1000 во все ячейки. При вводе стоимости ячейка должна автоматически заливаться соответствующим цветом. Для этого воспользуйтесь функцией «Условное форматирование» на вкладке «Главное»

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

Далее появляется окно для ввода параметра (цена) и действия, что сделать с ячейкой, которая удовлетворяет параметру.

Предложенные варианты действий не подходят, поэтому выбираем «Пользовательский формат…» и в параметрах заливки выбираем необходимый цвет. Нажимаем «ОК». Аналогично делаем для всех цен.

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

2. Посчитать ячейки с количеством мест (указать формулу) Посмотреть формулы из теории 14 лабораторной работы.

3. При изменении цены должен автоматически быть произведен перерасчет количества мест, прибыли и выручки. Правило для расчета прибыли указано на картинке. При выручке более 150000 р прибыль составит 25% от выручки, в другом случае лишь 20%. 

4. Произведите 5 изменений цен у различных по стоимости мест. Проверить работоспособность всех формул.

Применив условное форматирование, выделите:

• жёлтым цветом, ячейки всех строк «Internet», в которых, сумма оплаты свыше 1000 руб.

• светло – зелёным цветом, ячейки  всех строк «Итого»,  в которых, сумма оплаты за месяц составляет более 1400 руб.

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

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

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

Задание 3. 

Создайте таблицу по примеру. Заполните группу на 15 ч. Введите оценки по дисциплинам и вычислите итоговую оценку.

Придумайте самостоятельно 5 правил условного форматирования.  

Формулировку правил укажите под таблицей (например: оценки 5 за год по информатике закрашиваются красным цветом и выделяются курсивом)