Problem I. 365. (January 2015)

I. 365. There are five gates to the depot of a trading company. The company owns 10 trucks. Due to the length of the loading and unloading process, a truck can pass through a gate at most once in every hour. A truck can use any of the gates to enter or exit the depot, but they have to stop briefly there to electronically register their number plates (``Rendszám'' in the example below), the time (1-100 in the ``Óra'' column) they pass through the gate, their direction (in or out, that is, ``Be'' or ``Ki'' in the ``Irány'' column), the gate number (1-5, ``Kapu'') and the truck weight (0,0-100,0 in tons in the ``Súly'' column - notice that here a comma is used instead of a decimal dot).

The file i365.zip (downloadable from our web page) contains the data recorded at the five gates (kapu1.txt, kapu2.txt, ..., kapu5.txt) and the data corresponding to the trucks (teherautok.txt, a tabulator-separated and UTF-8 encoded text file).

In your solution you

- may want to use formulae, functions or links whenever possible;

- can perform auxiliary computations only to the right of column E;

- should not use macros or user-defined functions.

1. In your spreadsheet application open the files containing the data recorded at the gates, then copy their content into a single table in a new sheet Raktár. Make sure that this new table has only one header (unnecessary headers should be deleted). You should save the table in the default application file format with name i365.

2. Place the truck data in a sheet Teherautók so that the first data piece is put into cell A1.

3. The data on the Raktár sheet should now be sorted according to the truck number plates, then, for the same truck, according to when they passed the gate.

Answers to the following questions should be displayed in the Teherautók sheet; a short text describing the actual cell content should precede the cells or should be put in the column headers.

4. For each truck you should determine how many tons of goods they transported in the depot and out of the depot, respectively, in the given period. The amount of transported goods is the difference between the full truck weight and empty truck weight measured at the gate.

5. Give the number plate of the truck that delivered the largest amount of goods into the depot.

6. Display for each truck whether they were in the depot or outside the depot before passing through Gate 1.

7. Read a time value in the given period and display the number of trucks in the depot at that time. Trucks entering or exiting the depot in that hour should not be taken into account.

8. Display, based on the given data, at least how many trucks broke the rules and did not stop at the gates to register their data.

9. Determine the peak hour with respect to truck traffic.

Your sheet (i365.xlsx, i365.ods,...) containing a short documentation (i365.txt, i365.pdf, ...) and also describing the name and version number of the spreadsheet application, should be submitted in a compressed file (i365.zip).

Downloadable file: i365.zip

(10 pont)

Deadline expired on February 10, 2015.

Statistics:

14 students sent a solution.
10 points:	Bognár 2012 Balázs, Fényes Balázs, Gercsó Márk, Kovács Balázs Marcell.
9 points:	Kelkó Balázs, Kovács 246 Benedek, Mócsy Miklós, Radnai Bálint.
8 points:	3 students.
7 points:	2 students.
5 points:	1 student.

Problems in Information Technology of KöMaL, January 2015