How does Excel use formulas to generate 99 multiplication tables?

How does Excel use formulas to generate 99 multiplication tables?

Create a worksheet Sheet1 (Figure 1) and enter the 1~9,b2~j2 cell in the A3~a11 cell, followed by the 1~9.

Figure 1

Calculation results

The numeric results in the 99 multiplication table are all derived from the row value multiplied by the column value, so the values in the Excel cell should also be the row value multiplied by the column value, such as the result of the D6 value of A6 multiplied by D2, and the formula is: "=A6*D2".

Cell output format

Since the format of the "99 multiplication Table" is "4x3=12" (Fig. 2), you can see "4", "X", "3", and "=" as text in the output, that is, the middle is connected by "&&". But "4" is actually a value from column A, "3" is derived from the value of line 2nd, when the mouse drag to keep the column A and 1th line, should be preceded by the absolute reference symbol "$", so the corresponding change in the formula is: "= $A 6&&" x "&&D$2& & "= && $A 6*d$2" [Note: The quotation marks inside the formula are entered in the English State].

Figure 2

Overall output format

The "Lower triangle" form is one of the characteristics of "99 multiplication table", when we use the formula in peacetime, it is done by dragging the mouse. If you use the mouse to drag to be careful not to drag out the lower triangular range. In order to prevent "out of bounds", as in the form of "4x5=20", I took the "IF ()" function, when the value of a cell in column A is between 1~9 and the value of the cell in line 1th is not greater than the value of the cell in the corresponding column a 1~9 (such as $a6< > "", D$2 < > "", d$2<= $A 6) When output, otherwise output space. So the formula can be changed to read: "=if (and ($A 6<>" ",d$2<>" ", d$2<= $A 6), $A 6&&" x "&&D$2&&" = "& & $A 6*d$2, "") ". Now you can use the mouse to drag and try to produce the effect shown in Figure 3.

Figure 3

The above is done by generating a number of columns and a number of rows, if not the number of rows and columns can be completed? The answer, of course, is yes. The following is a small compilation. Use row functions row () and Column function columns () to make further improvements to the formula: "=if () and" Row () <11,column () ".