The title is:
How to design a table with a lot of rows and columns?
However, users:
...want to see what employee...worked how many hours on each workplace
...and the:
Main Goal is to get a good idea of who has worked a lot in a month, who has worked less, who had most weekend shifts and so on so the guy planning the shifts is able to distribute new shifts more fair.
There are two tasks identified here, each requiring different approaches and having different outcomes. The first is how to display as much discrete information as possible in as small a space as possible. The second is how to sort, filter, and aggregate information to reveal cumulative information.
A large amount of discrete data is impossible for most people to not only comprehend but even scan to locate the information they're looking for. Better to start with a broad view and present sorting and filtering controls up front so they can conform the data into customized chunks of information that match their current frame of mind.
Sorting and Filtering
...who has worked a lot in a month...
Start with the "big picture" and focus on the workers—initially present a list of workers ordered by total number of hours worked, in descending order. Other information, such as absences, job performance, and scheduling controls can be included in each row. This list is only as long a the total number of workers. It may require vertical scrolling but the workers with the most hours are "above the fold". Do not put the locations in columns here, potentially causing the need for horizontal scrolling, and more importantly increasing the complexity and therefore comprehensibility of the initial presentation.
Within the list of workers, for each row, include an expansion control to open a sub-list showing how the hours a particular worker worked are dis-aggregated, i.e., how many hours at each location, and how many weekend hours.
Adjacent, i.e., located near the list, provide a sorting control. The default, of course, is "total hours worked", but also include weekend hours, absences, etc. More complex sorting can be provided, if necessary, combining multiple sort subjects: first location, then hours worked, for example.
This list, being easy to comprehend and scan, gives managers the direction they need to filter and locate the information they need to make scheduling decisions.
One filter can be checkboxes at the beginning of each worker row in the initial list so sub-groups can be selected based on more or less hours worked. And/or an adjacent "number hours range" text field type filter can be provided to perform a similar function.
Another adjacent filter can be a location filter:
Choose one or more locations:
□ Kitchen
□ Garage
□ Garden
□ ...
...then the list of workers is limited to the locations selected.
Additionally, if user research demonstrates a need, provide two tabs: one, the default open tab, for a worker-sorted list, and two, a location-sorted list. Each tab focuses on its topic and provides appropriate sorts and filters.
Surely there are more sorts, filters, fields, and controls to provide, the point though is to guide the user's comprehension from broad to narrow by data aggregation married with sorting and filtering controls designed to match the user needs and information type.
