I am looking for a solution to simplify the translation of the weekly teachers ’calendar into the monthly calendar, preferably through spreadsheets (Excel, OO Calc, etc.) I imagine the result as follows: The first sheet contains the weekly load distribution schedule (entered manually):

Дни недели ФИО Понедельник Вторник Среда Четверг Пятница Суббота Иванов 5 4 3 4 Петров 4 8 2 5 3 Сидоров 4 3 2 7 6

And on the following pages - graphs of the distribution of the monthly load over the numbers calculated on the basis of the weekly load schedule, with the data for each number taken on the corresponding days of the week. For example, for May 2016 (starts from Sunday):

  Числа ФИО 1 2 3 4 5 6 7 8 9 10 … Иванов 5 4 3 4 5 … Петров 4 8 2 5 3 4 … Сидоров 4 3 2 7 6 4 3 …

How can you achieve this result? Algorithmically, the task is not difficult, but I don’t know from which side to approach it in spreadsheets.

  • Need to use a scripting language. For Excel, this is Visual Basic, for Libre Office, other tables will have something similar. - AivanF.
  • can and formulas, only cumbersome too - rdorn
  • @AivanF, And the list of teachers from a weekly workload can be reproduced by a formula on each sheet of monthly workload? If so, which one? (Essentially - copy the entire value in the column, how many there are). - Doge
  • @rdorn, but through formulas - how? - Doge
  • Well, they made me remember long forgotten - rdorn

1 answer 1

Excel is only Russian at hand, so the function names are written in Russian. Comparison of Russian and English names is not difficult to google. So, let's begin.

The following functions will be needed:

MATCH (search, column or search string) —determines the relative position of the element

DATE (year, month, day) - returns the numeric value of the date

DAY (date in numeric format; format) - returns the number of the day of the week, format = 2, in our case

PEM (search; search range; row number) is a cool search function that searches for the specified value in the first row of the search range and returns the value from the cell in the specified row and the same column as the found value.

Detailed descriptions of functions are in the built-in help Excel.

As a result, I got the following formula:

 =ГПР(ДЕНЬНЕД(ДАТА(год; месяц; день); 2); диапазон поиска; ПОИСКПОЗ(преподаватель; столбец преподавателей в таблице недельной нагрузки)+смещение))

A few notes: the days of the week should be written in numbers (Mon = 1 ... Sun = 7). the offset in the formula must be hand-picked based on the placement of the search range for the ГПР() function ГПР()

Well, for clarity:

enter image description here

I hope to correct the links to the cells, accordingly, it will not be difficult.