Problem I. 374. (April 2015)
I. 374. A certain car manufacturing company has arrived in Hungary. They want to establish marketing offices and service centers in some Hungarian towns outside the capital, mostly in county towns or in some bigger towns in each county. The tabulator-separated and UTF-8 encoded text file motelep.csv contains the town names in the country, the \(\displaystyle X\) and \(\displaystyle Y\) coordinates (in meters) to locate the towns on the map, the name of the county of each town, and the name of the service center if such center will be established in that town. By using a spreadsheet application, you should solve the following tasks. You should save your solution i374 in the default application file format. You can also use the image momegyek.png containing a county map of Hungary. Both files can be downloaded from our webpage.
1. Open the file motelep.csv in a sheet starting from cell A1, then rename the sheet to városok (= towns). The sheet heading should contain the titles Város (= town), TérképX (= map X), TérképY (= map Y), Megye (= county) and Telephely (= service center), displayed in bold with gray background and horizontally aligned in the center.
2. Round the \(\displaystyle X\) and \(\displaystyle Y\) coordinates to integers, replace the original data with these, and use the new data in the following. Non-empty cells of the sheet should be bordered with a thin border. Data should be sorted according to county names, then, within each county, according to town names.
3. Create a diagram to display the towns in a sheet named városok diagram (= town diagram). Town locations should be represented by disks without their boundaries and filled with RGB \(\displaystyle (132,60,12)\) color.
4. The background image of the drawing area of the diagram should be the momegyek.png file. The diagram axes should be set such that no towns should appear outside the country, and settlements close to the capital Budapest should appear close to the capital boundary. The westernmost town Szentgotthárd, moreover, Balassagyarmat (located to the north of the capital) should be just on the boundary lines.
5. The diagram should have no legend. The diagram title ``Magyarország városai és megyéi'' should appear in the upper left corner, outside the country, having the same text color and with orange RGB \(\displaystyle (255,230,153)\) background color. Axes and grids should not be visible.
6. On a different sheet you should collect all the data corresponding to towns in which a service center will be established. The name of this new sheet should be telephely, and it should be formatted as the városok sheet. You should create a sheet telephely diagram having the same format as the previous diagram and containing the towns with service centers. The diagram title should be ``Magyarországi telephelyek'' (= Hungarian service centers).
7. Create a sheet távolságok (= distances) that gives the distance between an arbitrary town and a town with a service center. Distances are measured by using the map \(\displaystyle (X,Y)\) coordinates and the Pythagorean theorem. Starting from cell A2 downwards, all town names should appear by using a reference, and all town names with a service center should appear to the right of cell B1.
8. Distances between towns in the first column and towns in the first row should appear in the corresponding cell in the column-row intersection by using a formula that can be copied, having km as a distance unit, and using only integer values. Auxiliary cells for computations can be placed to the right of column CA or below row 350.
9. Create a sheet keresés (= search) to display service centers within a given distance from a given town. Cell A1 should contain the text ``Város'', cell A2 should contain the name of a Hungarian town, cell B1 should have the text ``Telephely'', and cell C1 should contain ``Távolság''. In cell B2 the closest town with a service center should appear, and cell C2 should contain their distances in km units. In this cell the numerical value should be followed by the text ``km''.
A short description of your solution (i374.pdf) containing the name and version of the spreadsheet application and your actual solution sheet (i374.xlsx, i374.ods, ...) should be submitted in a compressed file i374.zip.
Downloadable files: momegyek.png, motelep.csv.
(10 pont)
Deadline expired on May 11, 2015.
Statistics:
9 students sent a solution. 10 points: Dombai Tamás, Fényes Balázs, Kelkó Balázs, Mócsy Miklós, Olexó Gergely. 9 points: Szemerédi Levente. 8 points: 1 student. 7 points: 2 students.
Problems in Information Technology of KöMaL, April 2015