Problem I. 329. (October 2013)
I. 329. In a certain section of a city, a truck transfers products between some stores. The city is Manhattan-like, because it consists of streets that are perpendicular to each other. The store addresses and a sample daily path of the truck are contained in the file szallitolevel.txt, downloadable from our homepage. The address list contains for each store its number (``Üzlet sorszáma''), its name (``Üzlet neve'') and its coordinates on the map expressed in meters (``x (m)'' and ``y (m)''). The daily path contains the number of stores to be visited in the given order (``Napi sorrend''). The truck can visit a particular store more than once a day, or it can return to the company premises (``Telephely'') several times. By using a spreadsheet application, you should solve the following tasks showing some statistics about the daily transfers.
During the solution, you should take into account the following.
- Auxiliary computations can be performed only to the right of column M.
- If possible, use formulae, functions and links.
1. Load the UTF-8 encoded and tabulator separated text file szallitolevel.txt into the sheet beginning with cell A1. Save your work as i329 in the default file format of the application.
The range A1:D34 contains the store address list. Cells below cell F2 can contain the store numbers to be visited by the truck in the given order during the actual day. This list can change daily. The number of stores to be visited each day is at most 30.
2. To help the truck driver, you should determine the store names (``Nevek'') from the address list in the range G2:G31, and the distances (``Táv (m)'') between consecutive stores in the range H2:H30. The distance between two stores with coordinates (x1,y1) and (x2,y2) is the Manhattan distance |x1-x2|+|y1-y2|. If there are empty cells in the F column under ``Napi sorrend'' (because the number of stores to be visited is less than 30 that day), then the corresponding cells in ``Nevek'' and ``Táv (m)'' should also remain empty.
3. In cell K2, compute the total distance covered by the truck during the actual day (``Napi úthossz''). This distance measured in km should be rounded up to the nearest integer, and a ``km'' unit should be displayed.
4. In cell K3, you should determine the distance between the two most distant stores (``Legnagyobb táv''), rounded up in km. Cells K4 and L4 should contain the name of these two stores (``Üzletek között'').
5. Cell K5 should contain the number of the longest path between the above two stores in the daily plan (``Hányadik'').
6. Format the spreadsheet cells according to the sample.
7. The daily path of the truck should be visualized in an XY diagram such that the turning points should also be visible. The path of the truck between two stores always begins with a horizontal segment (i.e., it starts in the x direction), then a left or right turn follows, and ends in a vertical segment. As a preparation for displaying the diagram, you should perform auxiliary computations or introduce auxiliary coordinates.
8. The above XY diagram should be placed on a separate sheet. The points representing stores and the turning points should be joined. Some other requirements are described below.
- No legend should be displayed in the diagram.
- The title of the diagram should be ``Daily path'' (``Napi útvonal'').
- The horizontal and vertical values on the axes should be displayed in the range between -11000 and +11000 m.
- Locate the point on the diagram representing the company premises and change its color to one different from the others.
The spreadsheet (i329.xls, i329.ods, ...) together with a short documentation (i329.txt, i329.pdf, ...) - also containing the name and version number of the spreadsheet application - should be submitted.
Scoring: proper solution of the first 6 tasks is worth 7 points, while solution of the last two problems (exceeding the difficulty level of a secondary school final exam) is worth 3 points.
Downloadable file: szallito.txt
(10 pont)
Deadline expired on November 11, 2013.
Sorry, the solution is available only in Hungarian. Google translation
Megoldásokról:
A feladat első 6 része nem jelentett gondot a versenyzőknek. A napi úthossz meghatározásánál, figyelmetlenség miatt, néhányan a kilométerre történő átváltásról elfelejtkeztek. Ezt észrevehették volna, hiszen a km mértékegységet még a számok után meg is jelenítették. Ha a feladat tartalmán, a megoldás eredményén egy kicsit elgondolkoznak, akkor gyanús lehetett volna, hogy Föld egyenlítői hosszának hatszorosát talán egy teherautó egy nap nem teszi meg.
Ismét előkerült, hogy az egészre kerekítés és az egészre formázás nem ugyanaz. Az előbbit függvénnyel kell végezni (KEREK.FEL()), a másik megjelenítési beállítás.
A feladat újszerű része a diagramkészítésnél volt. A teherautók fordulási helyének koordinátáit segédcellákban meg kellett előre határozni. Észrevehetjük, hogy az üzletek x és y koordinátái felváltva változnak. A kiindulási Telephely után a páros sorokban változik az x és a páratlanokban az y. Például az 1. sor a kiindulási helyet jelöli, akkor a 2. sorban x irányban halad a teherautó és a 3.-ban y irányban, és így tovább.
Minta megoldás:
Kiss Ádám, 11. évfolyamos tanuló (Szeged, Radnóti Miklós Kísérleti Gimnázium és Általános Iskola) munkája: i329.ods
Statistics:
19 students sent a solution. 10 points: Fehér Balázs, Fényes Balázs, Kiss 107 Ádám, Kovács Balázs Marcell. 9 points: Csahók Tímea, Gercsó Márk, Mócsy Miklós, Németh 123 Balázs. 8 points: 4 students. 7 points: 2 students. 6 points: 2 students. 5 points: 1 student. 3 points: 1 student. Unfair, not evaluated: 1 solutions.
Problems in Information Technology of KöMaL, October 2013