Решения Всероссийской олимпиадной задачи экологического типа в среде MS Excel «О контейнерах с тарой» 7 класс

Информация об авторе материала: " Решения Всероссийской олимпиадной задачи экологического типа для 7-ых классов в среде MS Excel "О контейнерах с тарой"

Петрашевич Галина Федоровна,

Учитель информатики и ИКТ,

Категория высшая,

МОУ СОШ № 21,

город Тверь, Россия,

mail: [e-mail]   [e-mail]

Сайт МОУ СОШ № 21 "school21tver.ru"

Тема урока: Электронные таблицы. Использование MS Excel в решении олимпиадных экологических задач.

Предмет: Информатика и ИКТ.

Класс/группа: 7 класс

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

Описание решения Всероссийской олимпиадной задачи

экологического типа для 7-ых классов в среде MS Excel

Задача А. Сортировка бытовых отходов (стеклянной тары)

Входной файл: Выходной файл: Время: Память:

input.txt output.txt не более 1 сек. не более 64 Мб

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

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

Вход

Во входном файле содержатся наборы чисел – по девять чисел на строке. Первые три числа в строке обозначают, соответственно, количество коричневых "B", зеленых "G" и прозрачных "C" бутылок в контейнере номер 1, следующие три числа обозначают количество коричневых, зеленых и прозрачных бутылок в контейнере номер 2, ну и, наконец, последние три числа подобным же образом задают распределение бутылок в третьем контейнере. Например, строка 10 15 20 30 12 8 15 8 31 обозначает, что в первом контейнере есть 20 прозрачных бутылок, во втором контейнере – 12 зеленых, а в третьем – 15 коричневых. Числа отделяются друг от друга одним или более пробелами. Ваша программа должна обработать все строки входного файла.

Выход

Для каждой строки из входного файла необходимо написать одну строку в выходной файл, описывающую, при каком распределении цветов по контейнерам, количество перемещений бутылок будет минимальным. Вы также должны вывести соответствующее количество перемещений. Строка должна содержать трехсимвольную комбинацию из букв верхнего регистра ‘G’, ‘B’, ‘C’ (обозначающих, соответственно, зеленый, коричневый и прозрачный цвета), при этом первая буква соответствует цвету бутылок, содержащихся в первом контейнере, вторая – цвету бутылок из второго контейнера, и, соответственно, третья буква – для третьего контейнера. Далее через пробел следует число, равное минимальному количеству перемещений бутылок между контейнерами. Если существует несколько конфигураций распределения бутылок по контейнерам с минимальным количеством перемещений бутылок, то вам необходимо выдать первую строку в алфавитном порядке.

Пример

input.txt	output.txt
1 2 3 4 5 6 7 8 9 5 10 5 20 10 5 10 20 10	BCG 30 CBG 50

Алгоритмизация задачи

Наряду с решением данной задачи средствами языков программирования, которые изучаются в общеобразовательных школах России, таких как  QBasic, Pascal, Delphi, VBA, C++ , возможно создание алгоритма решения задачи в среде Microsoft Excel.

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

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

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

Допускается ввод любого числа, в т.ч.  "0" (нуля).

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

На следующей странице  приведены формы ввода и вывода данных.

Описание алгоритма решения задачи

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

При разработке алгоритма решения задачи используется следующее утверждение:

Количество перемещенной тары равно общему количеству тары за минусом количества неперемещаемой тары.

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

И так, определяем первое максимальное значение количества тары в исходной таблице распределения тары. В нашем примере это 20 шт. тары вида "B" в контейнере № 2.

Данное количество тары вида "В", для минимизации перемещений, не будем перемещать. В результате, в контейнер № 2 будет находиться вся тара вида "В".

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

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

 В нашем примере это тара вида "G" контейнера 3. Это означает, что контейнер № 3 будет использоваться для тары вида "G". Обнуляем это максимальное значение и все значения в строках и колонках таблицы, на пересечении которых находилось это максимальное значение. Тем самым вывели эти значение из дальнейшей обработки.

Осталось одно значение. В нашем примере это 5 шт. тары вида "С" в контейнере № 1.

В результате:

1.     Определен вид тары по контейнерам.

2.      Определены максимальные значения неперемещаемой тары для каждого контейнера.

3.      Определено минимальное количество перемещений тары между контейнерами для сортировки тары и формирования тары одного вида в каждом контейнере.

В расчетах использовались некоторые функции MS Excel, а именно:

СУММ(F27:H29)-26

ЕСЛИ(B32=0;"";ЕСЛИ((СУММ(J27:L29)-1)=1;"B";ЕСЛИ(СУММ(J27:L29)-1=2;"G";"C")))

ЕСЛИ(ИЛИ(B43=0;B44=0; C42=0;D42 =0);"";B42)

ЕСЛИ(B27=B32;СТРОКА(B27);"")

ЕСЛИ(СЧЁТ(F27;G27;H27;F28;G28;H28)=0;ЕСЛИ(B29=B32;СТРОКА(B29);"");"")

ЕСЛИ(B52=B56;СТОЛБЕЦ(B52);"")

ЕСЛИ(СЧЁТ(J52;K52;L52;J53;K53;L53;J54;K54)=0;ЕСЛИ(D54=B56;СТОЛБЕЦ(D54);"");"")

МАКС(B27:D29)

Таблицы MS Excel

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

A

B

C

D

F

G

H

J

K

L

Коричневые

Зеленые

Прозрачные

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

B

G

C

для определение № контейнера

для определения цвета тары

Контейнер 1

5

10

5

Контейнер 2

20

10

5

28

2

Контейнер 3

10

20

10

Сумма

95

Максимум

20

Контейнер

2

Вид тары

B

По данным таблицы F27:H29 обнуляю значение  клетки таблицы B27:D29 с первым максимальным значением.             В данном примере это клетка B28, значение 20.

A

B

C

D

Вид тары

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

5

10

5

Контейнер 2

0

10

5

Контейнер 3

10

20

10

Чистка значений клеток, на пересечении которых значение равно "0"

Определение максимального значения в оставшихся значениях таблицы  B52:D54

A

B

C

D

F

G

H

J

K

L

Вид тары

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

Коричневые

Зеленые

Прозрачные

B

G

C

Номер строки

Номер столбца

Контейнер 1

10

5

Контейнер 2

0

Контейнер 3

20

10

54

3

Максимум

20

Контейнер

3

Вид тары

G

По данным таблицы F52:H54 обнуляю значение клетки таблицы B52:D54 с очередным первым максимальным значением.  В нашем примере это клетка C54, значение 20.

A

B

C

D

Вид тары

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

10

5

Контейнер 2

0

Контейнер 3

0

10

Далее, обнуление (чистка) значений клеток, на пересечении которых значение равно "0".

Определение максимального значения в оставшихся значениях таблицы B76:D78

A

B

C

D

F

G

H

J

K

L

Вид тары

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

Коричневые

Зеленые

Прозрачные

B

G

C

Номер строки

Номер столбца

Контейнер 1

0

0

5

76

4

Контейнер 2

0

0

0

Контейнер 3

0

0

0

Максимум

5

Контейнер

1

Вид тары

C

Результат :

Цвета тары в контейнерах

Количество перемещений

Контейнер 1

Контейнер 2

Контейнер 3

C

B

G

50

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

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

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

=ЕСЛИ(B6=0;"";B6)

=ЕСЛИ(C6=0;"";C6)

=ЕСЛИ(D6=0;"";D6)

Контейнер 2

=ЕСЛИ(B7=0;"";B7)

=ЕСЛИ(C7=0;"";C7)

=ЕСЛИ(D7=0;"";D7)

Контейнер 3

=ЕСЛИ(B8=0;"";B8)

=ЕСЛИ(C8=0;"";C8)

=ЕСЛИ(D8=0;"";D8)

Сумма

=СУММ(B26:D28)

Максимум

=МАКС(B26:D28)

Контейнер

=СУММ(F27:H29)-26

Вид тары

=ЕСЛИ(B32=0;"";ЕСЛИ((СУММ(J27:L29)-1)=1;"B";ЕСЛИ(СУММ(J27:L29)-1=2;"G";"C")))

По данным таблицы F27:H29 (приведена ниже) обнуляю значение  клетки таблицы B27:D29  с первым максимальным значением

Вид тары

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

=ЕСЛИ(F26="";B26;0)

=ЕСЛИ(G26="";C26;0)

=ЕСЛИ(H26="";D26;0)

Контейнер 2

=ЕСЛИ(F27="";B27;0)

=ЕСЛИ(G27="";C27;0)

=ЕСЛИ(H27="";D27;0)

Контейнер 3

=ЕСЛИ(F28="";B28;0)

=ЕСЛИ(G28="";C28;0)

=ЕСЛИ(H28="";D28;0)

"Чистка" значений клеток, на пересечении которых значение равно "0"

Определение максимального значения в оставшихся значениях таблицы  B52:D54

Вид тары

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

=ЕСЛИ(ИЛИ(B42=0;B43=0; C41=0;D41 =0);"";B41)

=ЕСЛИ(ИЛИ(B41=0;D41=0;C42=0;C43=0);"";C41)

=ЕСЛИ(ИЛИ(B41=0;C41=0;D42=0;D43=0);"";D41)

Контейнер 2

=ЕСЛИ(ИЛИ(B41=0;B43=0; C42=0;D42 =0);"";B42)

=ЕСЛИ(ИЛИ(B42=0;C41=0;C43=0;D42=0;);"";C42)

=ЕСЛИ(ИЛИ(B42=0;C42=0;D41=0;D43=0);"";D42)

Контейнер 3

=ЕСЛИ(ИЛИ(B41=0;B42=0; C43=0;D43 =0);"";B43)

=ЕСЛИ(ИЛИ(B43=0;D43=0;C41=0;C42=0);"";C43)

=ЕСЛИ(ИЛИ(B43=0;C43=0;D41=0;D42=0);"";D43)

Максимум

=МАКС(B51:D53)

Контейнер

=СУММ(F52:H54)-51

Вид тары

=ЕСЛИ((B56=0);"";ЕСЛИ((СУММ(J52:L54)-1)=1;"B";ЕСЛИ(СУММ(J52:L54)-1=2;"G";"C")))

По данным таблицы F52:H54 (приведена ниже) обнуление значения клетки таблицы B52:D54 с первым максимальным значением

Вид тары

Коричневые

Зеленые

Прозрачные

B

G

C

Контейнер 1

=ЕСЛИ(F51="";B51;0)

=ЕСЛИ(G51="";C51;0)

=ЕСЛИ(H51="";D51;0)

Контейнер 2

=ЕСЛИ(F52="";B52;0)

=ЕСЛИ(G52="";C52;0)

=ЕСЛИ(H52="";D52;0)

Контейнер 3

=ЕСЛИ(F53="";B53;0)

=ЕСЛИ(G53="";C53;0)

=ЕСЛИ(H53="";D53;0)

	Обнуление значений клеток, на пересечении которых значение равно "0".
	Определение максимального значения в оставшихся значениях таблицы B76:D78
	Вид тары
	Коричневые	Зеленые		Прозрачные
	B	G		C
Контейнер 1	=ЕСЛИ(ИЛИ(B66=0;B67=0; C65=0;D65 =0);0;B65)	=ЕСЛИ(ИЛИ(B65=0;D65=0;C66=0;C67=0);0;C65)		=ЕСЛИ(ИЛИ(B65=0;C65=0;D66=0;D67=0);0;D65)
Контейнер 2	=ЕСЛИ(ИЛИ(B65=0;B67=0; C66=0;D66 =0);0;B66)	=ЕСЛИ(ИЛИ(B66=0;C65=0;C67=0;D66=0;);0;C66)		=ЕСЛИ(ИЛИ(B66=0;C66=0;D65=0;D67=0);0;D66)
Контейнер 3	=ЕСЛИ(ИЛИ(B65=0;B66=0; C67=0;D67 =0);0;B67)	=ЕСЛИ(ИЛИ(B67=0;D67=0;C65=0;C66=0);0;C67)		=ЕСЛИ(ИЛИ(B67=0;C67=0;D65=0;D66=0);0;D67)
Максимум	=МАКС(B75:D77)
Контейнер	=СУММ(F76:H78)-75
Вид тары	=ЕСЛИ((B80=0);"";ЕСЛИ((СУММ(J76:L78)-1)=1;"B";ЕСЛИ(СУММ(J76:L78)-1=2;"G";"C")))

Формулы для расчета цвета тары для каждого контейнера:

Результат :
Цвета тары в контейнерах			Количество перемещений
Контейнер 1	Контейнер 2	Контейнер 3
=ЕСЛИ(B34=1;B35;ЕСЛИ(B58=1;B59;ЕСЛИ(B82=1;B83;"")))	=ЕСЛИ(B34=2;B35;ЕСЛИ(B58=2;B59;ЕСЛИ(B82=2;B83;"")))	=ЕСЛИ(B34=3;B35;ЕСЛИ(B58=3;B59;ЕСЛИ(B82=3;B83;"")))	=B31-B32-B56-B80

Формулы для расчета количества тары в каждом контейнере:

Для контейнера № 1 :

=ЕСЛИ(B18=B6;СУММ(B7:B9);ЕСЛИ(B18=C6;СУММ(C7:C9);ЕСЛИ(B18=D6;СУММ(D7:D9);"")))

Для контейнера № 2 :

                                                         =ЕСЛИ(C18=C6;СУММ(C7:C9);ЕСЛИ(C18=D6;СУММ(D7:D9);ЕСЛИ(C18=B6;СУММ(B7:B9);"")))

Для контейнера № 3 :

                                                                                                      =ЕСЛИ(D18=D6;СУММ(D7:D9);ЕСЛИ(D18=B6;СУММ(B7:B9);ЕСЛИ(D18=C6;СУММ(C7:C9);"")))

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

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

Отладка сложных формул с использованием метода пошагового вычисления (для MS Excel 2007).

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

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

Для переключения таблицы между значениями формул и формулами  нужно:

- на вкладке Формулы,

- в группе Зависимости формул,

- кликнуть команду-переключатель "Показать формулы"    (переключение между: значения – формулы). Для показа и пошагового вычисления формул можно использовать команду:

Вычислить формулу

Заключение

Предложенное решение задачи в среде MS Excel призвано продемонстрировать широкие возможности редактора "Электронная таблица" по алгоритмизации и программированию.

Считаю возможным и допустимым использовать не только языки программирования, но и MS Excel при решении олимпиадных задач.

Решение задач в MS Excel требует такого же алгоритмического мышления, как и языки программирования.

MS Excel является мощным инструментом для развития математических и логических способностей одаренных детей – участников олимпиад.

Ссылка на решение задачи в среде MS Excel

Данный материал был скачан с сайта www.metod-kopilka.ru

============================================================

!!!!!!!!Орфография и форматирование автора материала!!!!!!!!!!

========================================

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

учащихся и всех-всех, кто интересуется ИТ:

http://www.metod-kopilka.ru Методическая копилка учителя информатики

Организационные, методические и нормативные документы,

лабораторно-практические работы (комплекс занятий по MS Word, MS Excel,

MS Access, MS PowerPaint, Paint, Move Maker и др. прикладным программам),

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

экзамен, проектная деятельность, презентации.

Все в свободном доступе! Без регистрации!

=========================================

P.S. Единственным источником дохода сайта является переход по рекламным ссылкам.

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

Заранее благодарна.

Краткое описание документа:

Задача А. Сортировка бытовых отходов (стеклянной тары) Входной файл:Выходной файл:Время:Память: input.txt output.txt не более 1 сек. не более 64 Мб Для переработки стекла требуется, чтобы стеклотара была разделена по цвету на три категории: коричневое стекло, зеленое стекло и прозрачное стекло. Вам даются три контейнера, каждый из которых уже содержит определенное количество коричневых, зеленых и прозрачных бутылок. Требуется перераспределить бутылки в контейнерах таким образом, чтобы каждый контейнер содержал стекло только одного цвета. Проблема заключается в том, что вам надо минимизировать количество перекладываний бутылок из одного контейнера в другой. Можете считать, что в каждом контейнере может поместиться сколь угодно большое количество бутылок, причем общее количество бутылок во всех трех контейнерах никогда не превзойдет 231.Вход Во входном файле содержатся наборы чисел – по девять чисел на строке. Первые три числа в строке обозначают, соответственно, количество коричневых "B", зеленых "G" и прозрачных "C" бутылок в контейнере номер 1, следующие три числа обозначают количество коричневых, зеленых и прозрачных бутылок в контейнере номер 2, ну и, наконец, последние три числа подобным же образом задают распределение бутылок в третьем контейнере. Например, строка 10 15 20 30 12 8 15 8 31 обозначает, что в первом контейнере есть 20 прозрачных бутылок, во втором контейнере – 12 зеленых, а в третьем – 15 коричневых. Числа отделяются друг от друга одним или более пробелами. Ваша программа должна обработать все строки входного файла.Выход Для каждой строки из входного файла необходимо написать одну строку в выходной файл, описывающую, при каком распределении цветов по контейнерам, количество перемещений бутылок будет минимальным. Вы также должны вывести соответствующее количество перемещений. Строка должна содержать трехсимвольную комбинацию из букв верхнего регистра ‘G’, ‘B’, ‘C’ (обозначающих, соответственно, зеленый, коричневый и прозрачный цвета), при этом первая буква соответствует цвету бутылок, содержащихся в первом контейнере, вторая – цвету бутылок из второго контейнера, и, соответственно, третья буква – для третьего контейнера. Далее через пробел следует число, равное минимальному количеству перемещений бутылок между контейнерами. Если существует несколько конфигураций распределения бутылок по контейнерам с минимальным количеством перемещений бутылок, то вам необходимо выдать первую строку в алфавитном порядке.