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

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

Документы в архиве:

29.5 КБ Автор.doc
43 КБ Контейнеры с тарой 2010.xls
1.07 МБ Описание задачи о контейнерах с тарой.doc

Название документа Автор.doc

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



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

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

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

МОУ СОШ № 21,

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

mail: obraztver21@yandex.ru pgftver@mail.ru

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

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

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

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

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



Название документа Описание задачи о контейнерах с тарой.doc

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

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



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

Входной файл:

Выходной файл:

Время:

Память:

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’ (обозначающих, соответственно, зеленый, коричневый и прозрачный цвета), при этом первая буква соответствует цвету бутылок, содержащихся в первом контейнере, вторая – цвету бутылок из второго контейнера, и, соответственно, третья буква – для третьего контейнера. Далее через пробел следует число, равное минимальному количеству перемещений бутылок между контейнерами. Если существует несколько конфигураций распределения бутылок по контейнерам с минимальным количеством перемещений бутылок, то вам необходимо выдать первую строку в алфавитном порядке.

Пример

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" (нуля).

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



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



hello_html_me7d8b00.png



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


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

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

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

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

И так, определяем первое максимальное значение количества тары в исходной таблице распределения тары. В нашем примере это 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" используется для замены первого максимального значения.











27

28

29


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.










27

28

29


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


 






52

53

54


Вид тары


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


 

Коричневые

Зеленые

Прозрачные






 

B

G

C


Номер строки


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


Контейнер 1

 

10

5


 

 

 


 

 

 


Контейнер 2

0

 

 


 

 

 


 

 

 


Контейнер 3

 

20

10


 

54

 


 

3

 















Максимум

20
























Контейнер

3











Вид тары

G




















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











52

53

54


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


 






76

77

78


Вид тары


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


 

Коричневые

Зеленые

Прозрачные






 

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);"")))







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

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





hello_html_m4cfe49c2.png

hello_html_m5913100c.png

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

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

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


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

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

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

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


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

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





Заключение



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

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

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

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



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

5


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

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


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

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’ (обозначающих, соответственно, зеленый, коричневый и прозрачный цвета), при этом первая буква соответствует цвету бутылок, содержащихся в первом контейнере, вторая – цвету бутылок из второго контейнера, и, соответственно, третья буква – для третьего контейнера. Далее через пробел следует число, равное минимальному количеству перемещений бутылок между контейнерами. Если существует несколько конфигураций распределения бутылок по контейнерам с минимальным количеством перемещений бутылок, то вам необходимо выдать первую строку в алфавитном порядке.

Автор Петрашевич Галина Федоровна
Дата добавления 28.04.2011
Раздел Информатика
Подраздел
Просмотров 1051
Номер материала 1445
Скачать свидетельство о публикации

Оставьте свой комментарий:

Введите символы, которые изображены на картинке:

Получить новый код
* Обязательные для заполнения.


Комментарии:

↓ Показать еще коментарии ↓