Generate 99 multiplication tables with Excel

Source: Internet
Author: User

"99 multiplication Table" for everyone to be familiar with, but to let you use the mouse gently drag can produce it, but it is not an easy thing. Below I use the formula in Excel to try, but also ask teachers to give advice.

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: "=a6xd2".

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.