Description:
In the "June Attendance Consolidation" tab:
The 1 sheet is the attendance data derived from OA or machine;
The second table is the result of manual registration combined with OA data;
The third form is manual data registration, that is, leave adjustment, leave, etc.
Prerequisite:
1.3 table headers should be consistent; If there are 30 days in June, there are 30 columns;
2.3 the column position of the form should be consistent;
Instructions for use:
1. Replace the data in table 1 according to the format of our company;
2. Replace the data in the third table according to your company's format; The number of columns is also consistent with table 1;
3. Copy the "department", "name" and "week" columns in the third table and replace the "department", "name" and "week" columns in the second table;
4. In the column to the right of the "Week" column of the second table, start dragging again to get the combined result of 1 and the second table;
Description of formula: Take E90 cells as an example.
If E 166 is empty, the value of $B90 (that is, Ouyang Yong) is used to search only within a certain range (that is, the range is $B$ 1:$AI$84), and the value of the column number (COLUMN(D 1) is returned as 4. Finally, E 166 is not empty, and the value of E 166 is displayed;
Formula:
=IF(ISBLANK(E 166)，VLOOKUP($B90，$B$ 1:$AI$84，COLUMN(D 1)，0)，E 166)
Functional analysis:
IF: judge whether a certain condition is met.
ISBLANK: Check whether an empty cell is referenced.
VLOOKUP: Search the elements that meet the conditions in the first column of the table area (1 parameter is the first column, the second parameter is the data area, the third parameter is the column number to be returned, and the fourth parameter is exact search or approximate matching).
COLUMN: Returns the referenced column number.
$: indicates absolute positioning, even if the formula is dragged, it will not change;
$B$ 1: indicates that columns and rows are absolutely positioned;
If the columns of two tables are the same, you can refer to this example. Download link:/download/hezhenfei8/9955012.