Заглавная страница
Избранные статьи
Случайная статья
Познавательные статьи
Новые добавления
Обратная связь

ТОП 10 на сайте

Приготовление дезинфицирующих растворов различной концентрации

Техника нижней прямой подачи мяча.

Франко-прусская война (причины и последствия)

Организация работы процедурного кабинета

Смысловое и механическое запоминание, их место и роль в усвоении знаний

Коммуникативные барьеры и пути их преодоления

Обработка изделий медицинского назначения многократного применения

Образцы текста публицистического стиля

Четыре типа изменения баланса

Задачи с ответами для Всероссийской олимпиады по праву

Мы поможем в написании ваших работ!

ЗНАЕТЕ ЛИ ВЫ?

Влияние общества на человека

Приготовление дезинфицирующих растворов различной концентрации

Практические работы по географии для 6 класса

Организация работы процедурного кабинета

Изменения в неживой природе осенью

Уборка процедурного кабинета

Сольфеджио. Все правила по сольфеджио

Балочные системы. Определение реакций опор и моментов защемления

Главная Избранные Случайная статья Познавательные Новые добавления Обратная связь FAQ

Применение текстовых и календарных функций.

⇐ ПредыдущаяСтр 6 из 6

Задание 1.

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

Выполнение.

1. Составьте таблицу сотрудников фирмы, содержащий следующие данные:

Список сотрудников фирмы
№ п/п	Фамилия	Имя	Отчество	Дата рождения	Дата зачисления
1.	Макаров	Сергей	Петрович	23.05.40	05.09.90
...	...	...	...	...	...

2. Изучите календарные функции СЕГОДНЯ(), ГОД(), ДОЛЯГОДА(), МЕСЯЦ().

3. Постройте другую таблицу

Список сотрудников фирмы
№ п/п	Фамилия И.О.	Возраст	Стаж
1.	Макаров С.П.	58	8
...	...	...	...

4. Для получения данных в графе “Фамилия И.О.” можно применить формулу

=Фамилия&" "&ЛЕВСИМВ(Имя;1)&"."&ЛЕВСИМВ(Отчество;1)&"."

В приведенной формуле Фамилия, Имя, Отчество – это имена соответствующих столбцов или адреса ячеек с соответствующей информацией.

Для получения данных в графе “Возраст” можно применить формулу

=ГОД(СЕГОДНЯ())-ГОД(Дата_рождения)

Для получения данных в графе “Стаж” можно применить формулу

=ОТБР(ДОЛЯГОДА(Дата_зачисления;СЕГОДНЯ();1))

Для определения числа месяцев можно применить функцию МЕСЯЦ.

Для определения возраста в днях можно применить формулу

=СЕГОДНЯ()-Дата_рождения+1.

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

Выполнение.

Изучите функции ВПР(), ОСТАТ), ГОД().

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

	A	B	C
1	Дата рождения	14 Апрель, 1949
2		0	"обезъяны"
3		1	"петуха"
4		2	"собаки"
5		3	"свиньи"
6		4	"крысы"
7		5	"быка"
8		6	"тигра"
9		7	"кролика"
10		8	"дракона"
11		9	"змеи"
12		10	"лошади"
13		11	"козы"
14	Вы родились в год	"быка"

В клетку B 1 введите дату рождения, например, 14 апреля 1949 года, в клетку B 14, в которой должно быть получено название года по восточному календарю, запишите формулу

=ВПР(ОСТАТ(ГОД(B1);12);B2:C13;2)

Задание 3.

Задание 2 выполните при помощи функций ПРОСМОТР, ИНДЕКС и/или ВЫБОР.

Задания для самостоятельной работы.

Задание 1С.

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

Задание 2С.

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

Задание 3С.

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

Задание 4С.

Подсчитать число повторений символа ‘a’ в строке символов из ячейки A3.

Задание 5С.

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

Л АБОРАТОРНАЯ РАБОТА № 7

ПРИНЯТИЕ РЕШЕНИЙ

Задание 1. Задача о оптимальном ассортименте

Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.

Продукция

Запасы сырья

1-й вид продукции

2-й вид продукции

1,2

1,9

2,3

1,8

57,6

0,1

0,7

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

Выполнение.

1. Такие задачи решаются при помощи инструмента Excel «Поиск решения» (вкладка Данные / Анализ / Поиск решения).

2. Математическая модель задачи.

Пусть продукция производится в количестве:

1-й вид – x₁ единиц, 2-й вид – x₂ единиц.

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

f(x₁,x₂)=25000 x₁+50000x₂,

для которой необходимо найти максимум.

При этом следует учесть ограничения по запасам сырья:

1,2 x₁ +1,9 x₂ £ 37,

2,3 x ₁ +1,8 x ₂ £ 57,6,

0,1 x ₁ +0,7 x ₂ £ 7

и по смыслу задачи x₁, x₂ должны быть неотрицательными и целыми:

x₁ ³0, x₂ ³0.

3. Ввод исходных данных в компьютер.

3.1. Введем целевую функцию и ограничения.

Для переменных x₁,x₂ определим соответственно ячейки С2:D2, и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции и нормы расхода сырья расположим под неизвестными в ячейках С3:D3 и С6:D8 соответственно. Запасы сырья расположим справа от матрицы норм расхода в ячейках G6:G8. В ячейке F2 вычислим значение целевой функции, а в ячейках F6:F8 ‑ реальный расход сырья.

Ячейка	Формула
F2	= СУММПРОИЗВ(C2:D2;C3:D3)
F6	= СУММПРОИЗВ($C$2:$D$2;C6:D6)
F7	= СУММПРОИЗВ($C$2:$D$2;C7:D7)
F8	= СУММПРОИЗВ($C$2:$D$2;C8:D8)

3.2. Задание параметров для диалогового окна «Поиск решения».

Выполнить команду Данные / Анализ / Поиск решения.

В диалоговом окне «Поиск решения» нужно указать:

· адрес ячейки, в которой находится формула, вычисляющая значение целевой функция;

· цель вычислений (задать критерий для нахождения экстремального значение целевой функции);

· адреса ячеек, в которых находятся значения изменяемых переменных х₁, х ₂;

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

· параметры решения задачи, для чего нажимается кнопка «Параметры».

Диалоговое окно «Поиск решения» и схема расположения исходных данных приведены ниже. Информация в этом окне соответствует решаемой задаче.

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

Задание 2. Сетевая транспортная задача

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

Склады	Склад 1	Склад 2	Склад 3
Наличие груза на складе	18	75	31

Этот груз необходимо перевезти в пункты назначения в соответствии с таблицей:

Пункты Назначения	Пункт 1	Пункт 2
Потребность груза	45	79

Стоимость перевозок определяется таблицей:

	Пункт 1	Пункт 2
Склад 1	17	6
Склад 2	12	13
Склад 3	9	8

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

Задание 3. Балансовые модели

Имеется трехотраслевая балансовая модель экономики с матрицей коэффициентов затрат:

Производственные мощности отраслей ограничивают возможности ее валового выпуска числами 300, 200. 500. Определить оптимальный валовой выпуск всех отраслей, максимизирующий стоимость суммарного конечного продукта, если задан вектор цен на конечный продукт (2, 5, 1).

a) Решить эту же задачу, если на конечный продукт накладываются следующие ограничения: валовый выпуск продукции первой и третьей отрасли относятся как 2:1 и конечный выпуск второй отрасли не должен превосходить 100.

b) К данным задачи заданы коэффициенты прямых затрат труда на выпуск продукции каждой отрасли: 0,2, 0,3, 0,15. Определить максимально возможный выпуск конечного продукта в стоимостном выражении, если суммарные затраты труда не должны превышать 70 ед.

Задание 4. Задача о смесях

Фирма «Корма» имеет возможность покупать 4 различных вида зерна(компонентов смеси) и изготавливать различные виды кормов. Разные зерновые культуры содержат разное количество питательных ингредиентов. Произведенный комбикорм должен удовлетворять некоторым минимальным требованиям с точки зрения питательности. Требуется определить, какая из возможных смесей является самой дешевой. Исходные данные приведены в следующей таблице:

Единица веса

Минимальные потребности на планируемый период

зерна 1 зерна 2 зерна 3 зерна 4 Ингредиент A 2 3 7 1 1250 Ингредиент B 1 0,7 0 2,3 450 Ингредиент C 5 2 0,2 1 900 Ингредиент D 0,6 0,7 0,5 1 350 Ингредиент E 1,2 0,8 0,3 0 600 Затраты в расчете на ед. веса (цена) 41 35 48 42 Минимизировать

Л АБОРАТОРНАЯ РАБОТА № 1

КОНСОЛИДАЦИЯ РАБОЧИХ ТАБЛИЦ

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

Задание 1.

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

Филиал № 1

Название товара	Январь	Февраль	Март
А–995	110	10	20
В–123	10	10	20
А143	20	20	40
В–123	30	30	60
С–070	40	40	80
Д–060	60	60	120
Е–130	50	50	100
Ф–270	70	70	140
Т–234	120	20	20
М–235	11	11	24

Филиал № 2

Название товара	Январь	Февраль	Март
Т–234	10	10	20
В–123	10	10	20
Р–234	20	20	20
А143	20	40	40
В–123	30	30	60
С–070	40	40	80
Д–060	60	60	120
Е–130	50	20	100
Ф–270	70	70	140
У–111	40	40	45
К–254	30	20	45

Филиал № 3

Название товара	Январь	Февраль	Март
А–995	10	10	20
В–123	10	10	20
А143	20	20	40
Р–234	100	100	100
В–123	30	30	60
С–070	40	40	80
Д–060	60	60	120
Е–130	50	50	100
Ф–270	70	70	140
К–254	10	10	10

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

Выполнение.

Для выполнения данного задания необходимо:

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

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

3. Задание параметров для диалогового окна «Консолидация»

3.1. В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

3.2. В поле «Ссылка» введите ссылку на диапазон первой рабочей таблицы, которые должны быть консолидированы. Если нужная книга закрыта, щелкните по кнопке «Обзор», чтобы найти нужный файл на диске. Ссылка может задавать диапазон больший, по числу строк, чем нужно консолидировать, но в случае добавления новых строк, параметры консолидации не нужно будет изменять. Когда в поле «Ссылка» будет введена нужная ссылка, щелкните по кнопке «Добавить», чтобы добавить ее к списку диапазонов.

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

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

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

4. Построить требуемую диаграмму.

Задание 2.

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

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

=[Имя_рабочей_книги]Имя_листа!Адрес_ячейки

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

=’[Бюджет на 2001 год]Лист1’!A1

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

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

- с помощью команд Данные / Консолидация.