Типовые технологические операции при создании и редактировании таблиц Excel

Основные операторы вычисления, сравнения и адресные операторы, используемые в формулах

Адресные операторы
Знак	Назначение
:	Оператор выбора диапазона ячеек. Пример А1:С8 – все ячейки диапазона с А1 по С8 включительно Пример 5:5 – все ячейки пятой строки Пример Н:Н – все ячейки столбца Н
,	Оператор объединения (перечисления ячеек). Пример А2, А6, С13 – ячейки А2, А6 и С13 Пример А1,В6,С2:С5 – ячейки А1 и В6 и диапазона с С2 по С5
Арифметические и текстовые операции
Знак	Действие
()	Заключение в скобки для изменения порядка математических действий
+	Сложение
-	Вычитание
*	Умножение
^	Возведение в степень. Пример 4^2.
/	Деление
&	Слияние текстовых полей. Пример “я и”&” ты ” результат: «я и ты»
Операции сравнения
Знак	Выполняемое сравнение
<	Меньше
>	Больше
<=	Меньше или равно
>=	Больше или равно
<>	Не равно
=	Равно

 

Вычислительные операции

Пусть в ячейке С4 находится число. Нужно, чтобы в ячейке F4 было число, в 2 раза большее. Формула, записываемая в ячейку F4, будет иметь вид =C4*2.

Текстовые операции

Пусть в ячейке B4 находится фамилия, а в ячейке С4 имя сотрудника. Нужно в ячейку F4 поместить фамилию и имя сотрудника. Формула будет иметь вид =В4&С4. Результат выполнения этой формулы таков: если в ячейке B4 находится текст Петров, а в ячейке С4 текст Иван, то в ячейке F4 будет текст ПетровИван. Для того, чтобы между фамилией и именем поместить пробел, формулу нужно записать так: =В4&” “&С4.

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

Пусть в ячейках с А1 по А10 находятся числа. В ячейки с В1 по В10 нужно записать величины, равные 10% от этих чисел. В первую ячейку блока В1:В10 – ячейку В1 записываем формулу =А1*10%. Копируем эту формулу в блок ячеек В2:В10. При копировании формул выполняется правило относительной ориентации ячеек, т.е. если в формуле использовались относительные адреса ячеек, Excel автоматически смещает адреса ячеек в копиях формулы. В ячейке В2 после копирования будет содержаться формула =А2*10%, в ячейке В3 формула =А3*10%, …, а в ячейке В10 формула =А10*10% соответственно.

 

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

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

Пусть в ячейке С12 находится множитель. В блоке ячеек В1:В10 находятся числа. В блок ячеек С1:С10 нужно поместить произведения чисел из соответствующих ячеек блока В1:В10 на множитель из ячейки С12. Тогда формула, записываемая в ячейку С1, будет такова =В1*$C$12. После копирования ее в другие ячейки блока С2:С10 получим следующее – в ячейке С2 находится формула =В2*$C$12, в ячейке С3 формула =В3*$C$12,…, а в ячейке С10 формула =В10*$C$12. Таким образом, при копировании формулы относительный адрес ячейки (В1) будет изменяться, а абсолютный адрес ($С$12) останется неизменным во всех копиях.

 

2.2.6. Использование в формулах трехмерных адресов ячеек

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

Пусть необходимо сложить данные, находящиеся в ячейках А10 листов с именами Экзамен1, Экзамен2 и Экзамен3 и поместить их в ячейку С15 листа Итоги. Формула, помещаемая в ячейку С15 листа Итоги, будет выглядеть так:

=Экзамен1!А10+Экзамен2!А10+Экзамен3!А10

 

2.2.7. Использование в формулах функций

2.2.7.1. Перечень часто используемых функций
Функция	Пример
1. Арифметические и тригонометрические функции
=СУММ(аргумент1;аргумент2;…) суммирование до 30 аргументов. Каждый аргумент может являться блоком ячеек	=СУММ(А2;С9;D4:D14) суммируются числа, находящиеся в ячейках А2 и С9 и числа из диапазона ячеек D4:D14
=ПРОИЗВЕД(аргумент1;аргумент2;…) произведение до 30 аргументов. Каждый аргумент может являться блоком ячеек	=ПРОИЗВЕД(D4:D14) находится произведение чисел из диапазона ячеек D4:D14
=СЧЕТЕСЛИ(интервал;условие) подсчитывается количество ячеек из заданного интервала, удовлетворяющих заданному условию;	=СЧЕТЕСЛИ(F1:F15;”=0”) подсчитывается количество ячеек с нулями в диапазоне с F1 F15
=СУММЕСЛИ(диапазон1;условие;диапазон2) диапазон1 указывает диапазон ячеек, значения в которых проверяются на выполнение указанного условия; диапазон2 указывает диапазон ячеек, значения из которых суммируются при выполнении условия Примечание: если диапазон2 не указан, суммируются значения ячеек из диапазона1	=СУММЕСЛИ(В1:В10;”группа1”;A1:A10) если в ячейке столбца В находится слово «группа1», число из соответствующей ей ячейки столбца А суммируется
=СУММЕСЛИ(В1:В10;”>50”) суммируются все числа из диапазона ячеек с В1 по В10, большие 50
2. Логические функции
=ЕСЛИ(лог.выражение;значение1;значение2) лог.выражение – любое логическое выражение (условие), принимающие значение ИСТИНА или ЛОЖЬ значение1 возвращается, если логическое выражение – ИСТИНА значение2 возвращается, если логическое выражение – ЛОЖЬ	=ЕСЛИ(А10>5;1;0) если в ячейке А10 находится число, большее 5, результат равен 1, в противном случае 0
=ЕСЛИ(В3=”да”;А3;А3*2) если в ячейке В3 находится слово «да», результат равен числу из ячейки А3, в противном случае – числу из ячейки А3, умноженному на 2
=ИЛИ(лог.выражение1;лог.выражение2;…) проверка до 30 логических выражений (условий), каждое из которых имеет значение ИСТИНА или ЛОЖЬ. Достаточно выполнение хотя бы одного из перечисленных условий для общего результата ИСТИНА	=ИЛИ(А1=0;А1=1;А1=2;А1=5) проверяется, равно ли число из ячейки А1 одному из чисел: нулю, единице, двойке или пятерке
=И(лог.выражение1;лог.выражение2;…) проверка до 30 логических выражений (условий), каждое из которых имеет значение ИСТИНА или ЛОЖЬ. Обязательно одновременно е выполнение всех перечисленных условий для общего результата ИСТИНА	=И(А1=0;В1=”да”) проверяется, равно ли число из ячейки А1 единице, а текст в ячейке В1 слову «да»
Использование вложенных логических и математических функций	=ЕСЛИ(ИЛИ(А1>0;В1>0);СРЗНАЧ(А1;В1);СУММ(А1;В1)) если хотя бы в одной из ячеек А1 или В1 находится положительное число, то результат равен среднему арифметическому этих чисел, а в противном случае - их сумме
3. Статистические функции
=СРЗНАЧ(аргумент1;аргумент2;…) подсчитывает среднее значение для аргументов (до 30 аргументов). Каждый аргумент может быть блоком ячеек	=СРЗНАЧ(А1:А10;Н1:Н10) подсчитывает среднее значение для всех ячеек из блоков с А1 по А10 и с Н1 по Н10
4. Функции обработки текста
=ДЛСТР(текст) возвращает количество символов текстовой строки (пробелы считаются)	=ДЛСТР(А1&В1&С1) если в А1 слово «Петров», в В1 - «Иван», в С1 – «Игоревич», то результат равен 18
=ПРОПИСН(текст) делает все символы в тексте прописными =СТРОЧН(текст) делает все символы в тексте строчными	Эти функции не меняют символов, которые не являются буквами. Если в А10 был текст «Мама 21», то результатом функции =СТРОЧН(А10) будет текст «мама 21»
5. Функции работы с датами и временем
=СЕГОДНЯ()	Возвращает текущую дату (значение даты будет меняться ежедневно в соответствии без изменения формулы – автоматически)
=ГОД(дата) =МЕСЯЦ(дата) =ДЕНЬ(дата) возвращают год, месяц и день соответственно Формат исходных ячеек должен быть дата, а ячеек с результатом числовой или общий	Если в А10 находится дата 15-апр-2004 =ГОД(А10) результат 2004 =МЕСЯЦ(А10) результат 4 =ДЕНЬ(А10) результат 15
=ДАТА(год;месяц;день) формирует дату из отдельных составляющих. Формат исходных ячеек должен быть числовой или общий, а ячейки с результатом – дата	Если в ячейках: А1 находится число 1998, в В1 число 3, в С1 число 18, то =ДАТА(А1;В1;С1) даст результат 18.03.1998

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

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

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

Для получения текущей даты нужно использовать функцию СЕГОДНЯ. Формула в ячейке D6 будет иметь вид: =СЕГОДНЯ()-В5

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

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

Построение диаграммы по данным таблицы Еxcel выполняется в несколько этапов:

1-й этап. Выбор типа диаграммы и ее вида.

2-й этап. Указание диапазона данных для диаграммы.

3-й этап. Задание подписей к осям диаграммы, ее названия и, если, необходимо, Легенды (легенда – надпись, поясняющая смысловое содержание разноцветных столбиков или секторов диаграммы).

4-й этап. Задание места расположения диаграммы.

 

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

1-й способ. Сначала выделить диапазон данных, по которым стоится диаграмма, затем вызвать Мастер диаграмм;

2-й способ. Сначала вызвать Мастер диаграмм, а затем, в процессе построения, выбирать из таблицы данные, по которым строится диаграмма.

 

2.3.1. Построение диаграммы по данным таблицы Excel

Пусть создана таблица, приведенная ниже:

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

Выделить мышью блок ячеек с данными, включая заголовки столбцов А2:D7

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

Нажать кнопку (диаграмма) на панели инструментов или выполнить команду меню Вставка – Диаграмма.

В появившемся диалоговом окне на вкладке Стандартные выбрать тип Гистограмма, вид – первая в верхнем ряду; щелкнуть кнопку Далее

Появится диалоговое окно Мастера диаграмм с изображением диаграммы:

 

 

Полученная диаграмма нас устраивает. Нажмем кнопку Далее.

 

В новом диалоговом окне перейдем на вкладку Заголовки и введем текст «Распределение учебных часов» в строку Название диаграммы и названия осей: «группы» в строку Ось Х (категорий) и «часы (в семестр)» в строку Ось У (значений).

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

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

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

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

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

2.3.2. Редактирование диаграммы

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

При выборе пункта меню Формат диаграммы … можно выполнить форматирование диаграммы (см. п.2.3.3).

При выборе пункта меню Тип диаграммы… можно изменить тип и вид имеющейся диаграммы.

При выборе пункта меню Параметры диаграммы… можно: на вкладке Заголовки диалогового окна изменить (добавить, удалить) заголовок диаграммы и названия осей диаграммы, а на вкладке Легенда добавить или удалить Легенду с диаграммы.

При выборе пункта меню Исходные данные… можно изменить, добавить, удалить данные, используемые для построения диаграммы, а также поменять расположение данных по осям (например, в трехмерной диаграмме поменять местами данные, отображаемые по оси Х, с данными по оси Z).

Рассмотрим подробнее редактирование диаграмм на примере гистограммы, построенной в п. 2.3.1. Для редактирования вызовем контекстное меню в области диаграммы, выберем пункт меню Исходные данные… и перейдем на вкладку Ряд диалогового окна.

Ниже приведены диалоговое окно Исходные данные и измененная таблица данных:

 

 

Ряды данных, для которых построена диаграмма (выделен ряд «история») Адрес ячейки с названием выделенного ряда данных (ячейка В2) Адреса ячеек, в которых находятся данные, использованные для построения выделенного ряда (блок ячеек В3:В7) Адреса ячеек, в которых содержатся текстовые или числовые значения, взятые в качестве подписей по оси Х на диаграмме (блок ячеек А3:А7)

Задание. Добавить в имеющуюся диаграмму данные по иностранному языку, которые были добавлены в таблицу «Часы (в семестр)» в ячейки Е2:Е7. Ход выполнения работы:

Необходимо добавить новый ряд данных на диаграмму, поэтому щелкнем по кнопке Добавить под списком Ряд (Примечание: если же необходимо убрать данные с диаграммы, то лишний ряд данных в списке Ряд выделяется и нажимается кнопка Удалить).

Будет добавлен новый ряд с именем Ряд4 (т.к. это четвертый по счету ряд). Для изменения этого названия на «ин. язык» установим курсор в строке Имя и либо введем название с клавиатуры, либо щелкнем мышью в ячейке Е2 таблицы данных. Затем установим курсор в строке Значения, сотрем отображенную там информацию и выделим мышью нужный диапазон ячеек в таблице данных Е3:Е7. На диаграмме сразу отразятся сделанные изменения.

Можно изменить вид диаграммы так, чтобы по оси Х отражались названия предметов (а не номера групп, как в нашем случае), а номера групп фигурировали в Легенде. Для этого перейдем на вкладку Диапазон данных и проверим, отражен ли в строке Диапазон блок данных таблицы, по которым строилась диаграмма (в нашем случае это ячейки А2:Е7, с указанием имени листа). Если строка Диапазон пуста, выделим мышью этот блок ячеек и убедимся, что он отразился в строке. Изменим установку переключателя Ряды в с позиции столбцах на позицию строках. Вид диаграммы изменится. Ниже приведены различные виды одной и той же диаграммы:

Расположение Ряды в столбцах

Расположение Ряды в строках

2.3.3. Форматирование (изменение размеров и расположения) всей диаграммы в целом и отдельных ее частей

Рассмотрим форматирование диаграмм на примере трехмерной гистограммы

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

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

 

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

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

 

Основные составляющие диаграммы и их месторасположение:

 

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

 

В диалоговом окне форматирования области диаграммы и области построения диаграммы (на вкладке Вид) можно выбрать тип линии рамки и цвет закрашивания (заливки) области.

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

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

В диалоговом окне форматирования оси можно изменить размер, начертание и цвет шрифта (вкладка Шрифт), угол наклона подписей по оси (вкладка Выравнивание, область Ориентация), начертание делений по оси (вкладка Вид). На вкладке Шкала можно изменить: для осей с текстовыми подписями - число подписей между делениями, например, подписывать каждое деление, а не через одно как на приведенной выше диаграмме; а для осей с числовыми значениями – изменить цену основных и промежуточных делений, например, установить цену делений 30, а не 50, как на приведенной выше диаграмме.

Диаграмма после форматирования.

 

Примечание. В трехмерных диаграммах можно также изменять угол наклона диаграммы.

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

 

2.3.4. Построение тренда

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

Примечание: тренды могут быть построены только на плоских (не объемных) видах диаграмм.

Задание. Для приведенной гистограммы построить линейный и полиномиальный тренды

Ход работы:

 

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

.

 

 

Сначала выполним линейную аппроксимацию. В диалоговом окне на вкладке Тип выбираем вид линии тренда (в нашем случае линейная). На вкладке Параметры можно ввести название аппроксимирующей линии или оставить автоматически предложенное Excel (название линии выводится в области Легенды, а в нашем случае Легенды на диаграмме нет, значит, нет смысла указывать название линии); установить переключатель показывать уравнение на диаграмме (в нашем случае переключатель нужно установить); при необходимости прогнозирования оговорить, на сколько периодов сделать Прогноз (в нашем случае прогнозировать не нужно). Нажмем ОК.

Можно произвести форматирование линии тренда, наведя на нее указатель мыши, вызвав контекстное меню и выбрав из него команду Формат линии тренда… Воспользуемся этим и на вкладке Вид изменим тип линии на пунктирный. Нажмем ОК. “Захватив” мышью уравнение линии при нажатой левой клавише мыши перенесем его (уравнение) на свободное место диаграммы.

Теперь произведем аппроксимацию гистограммы полиномом третьей степени. Опять, наведя указатель мыши на любой столбик диаграммы, вызовем контекстное меню и выберем Добавить линию тренда. На вкладке Тип выберем вид линии тренда (полиномиальная, степень 3). На вкладке Параметры установим переключатель показывать уравнение на диаграмме и нажмем ОК. Переместимуравнение полинома на свободное место в области диаграммы. Получим:

Можно рассмотреть несколько типов трендов и выбрать ту функцию, которая наиболее точно аппроксимирует искомую диаграмму. Для оценки точности аппроксимации нужно навести указатель мыши на линию тренда, вызвать контекстное меню, выбрать команду Формат линии тренда… и на вкладке Параметры установить переключатель поместить на диаграмму величину достоверности аппроксимации (R^2).

 

Создание таблиц подстановки

 

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

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

Коэффициент = (1+процентная ставка)^{срок вклада}

 

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

Задание 1. Определить влияние изменения срока вклада на сумму возврата вклада.

 

 

Переменной величиной в нашем случае является срок вклада (ячейка В4 в этом случае называется ячейкой ввода). Возможные значения срока вклада (с 5 до 10 лет) поместим в смежные ячейки одной строки или одного столбца, например, ячейки С10:Н10

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

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

 

Выделяем диапазон ячеек В10:Н11, содержащий подставляемые значения срока вклада, расчетную формулу и ячейки, в которые будут помещены результаты расчета и затем выполняем команду Данные – Таблица подстановки. (В приведенном примере в ячейках А10 и А11 находятся поясняющие надписи, их выделять не нужно). В диалоговом окне Таблица подстановки в строке Подставлять значения по столбцам в: нужно ввести абсолютный адрес ячейки ввода, (в нашем случае это ячейка $В$4), затем нажать ОК.

Получим результат:

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

 

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

Задание 2. Определить влияние величины процентной ставки и изменения срока вклада на сумму возврата вклада.

Переменными величинами в этом случае являются срок вклада (ячейка В4) и процентная ставка (ячейка В5). Возможные значения срока вклада (с 5 до 10 лет) поместим в ячейки строки, например, ячейки С10:Н10, а возможные значения процентной ставки (от 3% до 10%) – в ячейки столбца В11:В18. Формула для определения значений должна находиться в месте пересечения столбца и строки с подставляемыми значениями, поэтому в угловую ячейку, находящуюся на пересечении столбца и строки (в нашем случае В10), поместим расчетную формулу. Получим:

Выделяем диапазон ячеек В10:Н18, содержащий формулу и оба набора данных для подстановки. (В ячейках А10 и В9 размещены поясняющие надписи, их выделять не нужно). Выполняем команду Данные – Таблица подстановки.. В диалоговом окне в строке Подставлять значения по столбцам в: указываем адрес параметра, значения которого расположены в строке (в нашем случае это параметр срок вклада, ячейка ввода $В$4), а в строке Подставлять значения по строкам в: указываем адрес ячейки ввода для параметра, значения которого расположены в столбце (в нашем случае это параметр процентная ставка, ячейка ввода $В$5). Нажимаем ОК. Получим результат:

 

Задачи оптимизации

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

 

Задача. На выпуск 1 тыс. изделий А затрачивается 3 т. металла и 3 тыс. кВтч электроэнергии, а на выпуск 1 тыс. изделий В затрачивается 1 т. металла и 6 тыс. кВтч электроэнергии. На рынке можно реализовать не более 5 тыс. изделий А и не более 7 тыс. изделий В. От реализации 1 тыс. изделий А фирма получает прибыль 400 тыс. руб., а от реализации 1 тыс. изделий В – прибыль 200 тыс. руб. Выделенные ресурсы на производство всех видов изделий: 25 т металла и 45 тыс. кВтч электроэнергии. Составить модель и определить, выпуск какого количества изделий А и В обеспечит максимум прибыли при выполнении оговоренных ограничений.

Ход выполнения работы:

Произвольные ячейки, например, А1 и А2 выделим для хранения количества изделий вида А и В соответственно (в тыс. шт.). Запишем в них начальные значения, например, 5 и 7. В ячейку А4 введем формулу вычисления целевой функции (функции вычисления прибыли): =А1*400+А2*200. В ячейку В1 введем формулу вычисления затрат электроэнергии: =3*А1+6*А2. В ячейку B2 введем формулу вычисления затрат металла: =3*А1+ А2

 

Выполняем команду меню Сервис – Поиск решения. В появившемся диалоговом окне Поиск решения в строке Установить целевую ячейку введем адрес ячейки с целевой функцией (в нашем случае это $А$4) и установим переключатель в положение максимальному значению. В строке изменяя значения установим адреса ячеек, значения которых будут изменяться в процессе поиска решения (в нашем случае это ячейки с количеством изделий $А$1 и $А$2):

В поле Ограничения нужно оговорить накладываемые по условию задачи ограничения. Таких ограничений в нашем случае четыре: 1. на количество изделий А (не более 5 тыс.); 2. на количество изделий В (не более 7 тыс.); 3. на общие затраты металла (не более 25 т); 4. на общие затраты электроэнергии (не более 45 кВтч). Ввод каждого нового ограниче-ния производится после нажатия кнопку Добавить.

 

Нажмем кнопку Выполнить и увидим результаты проведенной оптимизации:

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

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

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

 

Рассмотрим базу данных Студенты вида:

 

3.2.1. Сортировка по одному полю

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

 

3.2.2. Сортировка по нескольким полям

Щелкнуть мышью в любой ячейке внутри списка. Выбрать пункт меню Данные – Сортировка. В диалоговом окне Сортировка данных в выпадающем списке Сортировать по выбрать поле (столбец) для первичной сортировки; в выпадающем списке Затем по выбрать поле (столбец) вторичной сортировки, а в выпадающем списке В последнюю очередь по выбрать поле (столбец) следующей сортировки.

Например, если поля для сортировки выбраны так: 1. Номер группы; 2. Год рождения; 3. Фамилия; то данные сначала будут отсортированы по номеру группы; внутри группы по году рождения и, наконец, при одинаковых номере группы и годе рождения, по фамилиям в алфавитном порядке. Результат такой сортировки приведен ниже:

3.3. Фильтрация (выборка) данных списка

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

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

 

Рассмотрим таблицу базы данных Студенты вида:

3.3.1. Фильтрация данных с помощью автофильтра

Задание 1. Пусть необходимо из списка Студенты п. 3.3 выбрать студентов 12-й группы, получающих стипендию 800 рублей и более. Ход выполнения работы:

Щелкнуть мышью в любой ячейке внутри списка

Выбрать пункт меню Данные – Фильтр – Автофильтр. В заголовках столбцов списка появятся кнопки раскрывающегося списка ().

В столбце Группа щелкнем по такой кнопке и выберем из списка 12. Фильтрация по данному полю сразу будет произведена.

В столбце Стипендия щелкнем по кнопке и выберем из раскрывающегося списка Условие, поскольку необходимо выбрать студентов не с конкретной стипендией, а всех с размером стипендии ≥800. В появившемся диалоговом окне Пользовательский автофильтр в верхней левой строке щелкнем по кнопке и выберем из списка больше или равно, а в правой верхней строке выберем из раскрывающегося списка или введем с клавиатуры 800. Таким образом, сформировано условие «больше или равно 800». Поскольку на столбец Стипендия накладывается только одно условие, нажмем ОК. Фильтрация по заданным условиям будет выполнена.

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

 

Задание 2. Пусть из списка Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Предварительно отменив результаты предыдущей фильтрации, выполним следующее:

В столбце Группа щелкнем по кнопке и выберем из списка Условие; затем в диалоговом окне Пользовательский автофильтр в верхних строках описанным в Задании 1 способом сформируем условие «равно 11».Далее установим переключатель ИЛИ. В нижних строках сформируем условие «равно 12».

Пояснение: условия – студенты 11-й и 12-й групп – связаны логическим ИЛИ (а не логическим И), т.к. номер группы 11 или 12, а не 11 и 12 одновременно.

В столбце Год рождения щелкнем по кнопке и выберем из раскрывающегося списка 1987.

Фильтр покажет записи, удовлетворяющие таким условиям.

 

3.3.2. Фильтрация данных с помощью расширенного фильтра

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

 

ПРАВИЛА формирования диапазона условий для расширенного фильтра:

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