Excel 基礎教學 01：Excel 入門

Aug 14, 2023

Excel 基礎教學 01：Excel 入門

0:00 開場白
0:47 資料輸入
2:30 欄寬、列高調整
3:24 跨欄置中
4:17 表格設計
5:15 自動產生數列
\r6:20 日期格式設定
8:22 從下拉式清單挑選\r
9:09 簡易公式建立

Content

4.3 -> Hello everyone

6 -> Welcome to Papaya's Computer Classroom Channel

8.166 -> Then I start a new queue teaching today

10.433 -> Is the Excel software that everyone uses

13.466 -> In this queue of videos

15.166 -> I will put all the commonly used functions of Excel

17.633 -> Include table design

20.033 -> data analysis

21.833 -> Formula setting

23.4 -> Charts, functions, etc.

25.333 -> Introduce you step by step

28.133 -> You do n’t need any paperwork to watch this queue.

31.466 -> I will try to introduce these useful features in a simple way.

35.466 -> In addition, these instructional videos have Chinese subtitles

38.066 -> So there are friends who need to watch subtitles

39.933 -> You can directly open the subtitle function of Youtube

43.133 -> OK, so what we are seeing now is the interface of Excel

46.733 -> Excel files are basically composed of

48.5 -> Composed of these small squares on the screen

50.733 -> And each of these squares is generally called a "cell"

54.3 -> Cells can be used to enter data

56.133 -> Or set formula

57.4 -> In addition it has an important feature

59.4 -> Is that each cell is like the coordinates on the map

62.633 -> Has a specific reference position

65.233 -> The reference position is displayed in the upper left corner of the interface

68.1 -> For example, the reference position of this grid is D5

71.033 -> And the reference position of this grid is F7

74.566 -> OK, let's try to create a simple data form

78.3 -> I place my mouse cursor in cell A1

81.266 -> And enter a title

83.333 -> Example: ABC Food Order

86.433 -> You will find that the title text is beyond the cell border of A1

90.266 -> But we can fix this later

93.533 -> After typing

94.4 -> We can press Enter on the keyboard

96.466 -> Then the selected box will move to the lower cell.

100.5 -> We can then enter the field headers of the form

103.333 -> Like "order number"

105.8 -> After the title of this field is typed

107.533 -> I want the cursor to move to the "right" cell

110.7 -> To make it easier for me to enter the title of the next field

113.333 -> At this time, we are not pressing Enter.

115.766 -> But the Tab key of the keyboard

117.466 -> Then the marquee can be moved to the right

120.166 -> By the way, if you want the marquee to move to the left

124.166 -> You can click the keyboard's Shift + Tab keys

127.766 -> So do you remember that?

129.5 -> After entering the information

130.733 -> Tab to move the marquee to the right

133.733 -> Shift + Tab to return to the left

136.3 -> Press Enter to go down

138.1 -> In fact, you can also use directly

139.866 -> Up, down, left, and right arrow keys of the keyboard

142.533 -> To control marquee movement

144.6 -> Then I spend a little time

146.4 -> To complete the entry of all field titles

150.2 -> OK, after we created these titles

152.7 -> You will definitely feel crowded with these headline text

155.966 -> So let ’s take a look at how to adjust the width and height

160.066 -> It's easy to adjust single column width and column height in Excel

164.066 -> You just drag and drop the title line.

166.9 -> Just pull the field to the height and width you want.

171.2 -> And here is a practical tip

173.566 -> Is that Excel can match the text length of the field

176.733 -> To automatically adjust the column width

178.8 -> For example, I want the width of column F to be

180.8 -> Just fit the width of the words "total amount"

184.233 -> I can point directly to the dividers in columns F and G

186.733 -> Left-click twice

188.5 -> Column width can be automatically reduced

191.266 -> The same

192.333 -> Text like column A1 goes beyond the boundaries of column A

195.766 -> Then I just need to face the gap between columns A and B.

198.466 -> Same double click left mouse button

200.466 -> The width of column A will be widened automatically

203.066 -> However, we usually do n’t put such a heading in the corner of the table.

207.1 -> But will be placed in the center of the entire form

209.666 -> If you are a little bit conceptual about Word

212.9 -> You might think of the "center alignment" button

215.633 -> Indeed, we can use the mouse to select the title field

219.333 -> Press the Center button to align the text in the center of the cell

223.566 -> But there is no way to do this

225.366 -> Move the title to where I want it

228.566 -> So in Excel spreadsheet design

230.766 -> We often use a feature called "centre in the hurdle"

234.2 -> That is, after combining several cells,

237.233 -> Place the title text in the middle of the merged cell

240.566 -> And this operation is very simple

242.566 -> You just need to select the fields you want to merge first.

245.366 -> Then click `` Center across columns '' in the upper ribbon.

249.566 -> Also, if you want to remove the effect

252.666 -> You only need to press the same button again to restore the cells

256.933 -> Of course, we can also target the title text

259.033 -> Do some simple beautification

260.666 -> For example, I can slightly enlarge the text

263.5 -> Bold text

265.466 -> Change the background color of a cell

268 -> Change text color

270.7 -> Of course we can also change the font of the title

274.833 -> Excel even provides some

276.466 -> Simple design templates for quick application

279.133 -> For example, I can frame the title of the second column

281.833 -> And click "Cell Style" to apply my favorite design

286.366 -> There are many Excel beginners who often ask a question

289.633 -> Is how to adjust multiple fields to the same width at once

294.166 -> I think the easiest way

295.6 -> Is that you can directly put these

297.1 -> Boxes with inconsistent widths are selected first

299.6 -> Then move the mouse cursor to

301.566 -> Drag at any of the spacing lines

305.066 -> And when you release the mouse

307.533 -> All field widths will become consistent

310.533 -> And this operation technique is also applicable to the operation of "column height".

315.466 -> Then let ’s take a look

316.866 -> Tips for filling in numbers or numbers in Excel

320.1 -> I enter a random number under the "Order Number" field

323.266 -> Such as T001

324.833 -> And hit the keyboard's Enter key to jump to the bottom field

328.433 -> I can choose at this time

329.666 -> Continue to "manually" enter the second number T002

332.766 -> Or you can use a more efficient way

335.266 -> Just mouse over

336.966 -> Move to the bottom right corner of the number field

339.133 -> The cursor will turn into a small black cross

341.966 -> Then you can hold down the mouse and drag down

345.366 -> Excel can automatically generate a series for you 啰

348.766 -> And the most interesting thing is

350.133 -> Excel can also be based on a set of numbers you enter

353.2 -> To automatically generate sequences with the same gap

355.966 -> for example

356.766 -> If I want the order number to increase in an odd way

359.666 -> Like T001 followed by T003

362.5 -> Then T005 and so on

364.433 -> Then I can enter T001 and T003 in the field first

368.166 -> Then use mouse to select both fields

371.066 -> Let Excel determine the difference between these two numbers

373.9 -> Then we also drag the control point in the bottom right corner of the field

377.2 -> You can smoothly generate an odd number of increasing sequence

380.166 -> As for the date input

381.6 -> Excel also has some very useful tips

383.966 -> And first I would recommend beginners in Excel when entering dates

387.5 -> Able to follow the date format recognized by Excel

390.7 -> That is, use "slash" to separate the year, month, and day

393.966 -> Do n’t use other symbols

395.666 -> Such as commas, decimal points, or using Chinese characters

398.4 -> There is a benefit to using Excel's date format

400.833 -> Is that these dates can be calculated with time functions

405.066 -> At the same time, you can switch the date format

409.8 -> Then I take the conversion date format as an example

411.7 -> If I want to display the date

413.566 -> If it is displayed in Chinese or English

416.033 -> I can right click in the field

418.3 -> And run the "Cell Format"

420.566 -> Then click on the date category on the left side of the dialog

423.633 -> So we can be in the field on the right

425.666 -> Pick the desired date format

429.033 -> After setting, press the "OK" button at the bottom

432.3 -> Then there is a very common problem here

434.466 -> All the data in the field suddenly changed to #

437.666 -> The reason is that the text in the field is too long

440.966 -> This exceeds the width of the field itself

444.366 -> The solution is also very simple

446.233 -> All you have to do is move the mouse over the title line above the field

449.833 -> And double click left mouse button to adjust column width automatically

452.8 -> Can solve this problem

455.366 -> So here I introduce a very useful shortcut

458 -> Is if you need to enter today ’s date in the field

461.533 -> You can directly press Ctrl + on your keyboard;

464.6 -> Press the Enter key again and it will be OK 啰

467.333 -> In addition, the date can be used like a normal value

469.8 -> You can drag the control point in the bottom right corner of the field

472.166 -> To automatically fill in other dates

474.533 -> Even inside this little button next to

476.2 -> We can set different filling options

478.433 -> Like "fill up with the month"

482 -> "Filled with years" and so on

485.733 -> Then I reverted to filling up with a series

488 -> And copy all the date fields

491.366 -> At the same time, I used the previous "Format Cell" command

494.2 -> To unify the date format

501.333 -> Then I plan to enter some information in the product field

505.833 -> In addition, I will introduce a convenient small function

508.733 -> I will temporarily hide the upper ribbon

511.533 -> To add space to some tables

514.366 -> Then when you are building information

516.4 -> If there is some information that is repetitive

519.3 -> For example, the name I want to enter now

521.233 -> It just happens to be one of "tea, sesame, corn"

524.6 -> Then I can right-click in the field

528.166 -> And click "Select from drop-down list"

531.233 -> So that you can select from the pop-up menu

533.366 -> Select the item you want

534.7 -> Save some data input time

542.2 -> OK, after completing the product field

544.533 -> I took some time to enter the unit price and quantity of the product

548.533 -> Then let's set up a simple Excel formula

551.766 -> Let ’s take the total amount on June 1 as an example

554.2 -> If here is using a traditional computer

556.533 -> Then everyone should press 45 directly

559.133 -> Multiply by 30

560.4 -> Then press an "=" button

562.166 -> You can figure out the answer

563.766 -> The Excel formula is very close to this formula

566.733 -> The only difference is that we want the "=" sign

569.2 -> Change to the top of the value

571.7 -> In addition, we have to change the "unit price"

573.4 -> And "quantity"

574.9 -> Change to the "reference position" where they are located

576.966 -> That is, the unit price field is 45

578.933 -> Replace with D3

580.7 -> Change the number of fields to 30

582.4 -> Replace with E3

584 -> So that we can see later

585.733 -> Excel's magical computing power

587.866 -> Maybe the explanation just now is a bit complicated for beginners

591.133 -> But it ’s actually very simple

593.333 -> Let's see it in action

595.233 -> I first click on the first blank field under "Total Amount"

598.466 -> Which is F3.

600.466 -> Then click the "=" sign on the keyboard

602.6 -> On behalf of us ready to build formulas

604.466 -> Then we do n’t really need

605.933 -> Enter any reference position using the keyboard

607.966 -> Just click with your mouse

609.666 -> The "Unit Price" field we want to calculate

611.266 -> Excel will automatically bring in its reference position

614.266 -> Then we click the "multiplication sign" on the keyboard

616.5 -> Which is the "asterisk" of the numeric keyboard

618.2 -> Click the "Quantity" field

620.566 -> Finally, we press Enter to complete the establishment of this formula.

624.733 -> So here we have to remember

626.466 -> When we build the formula in the table

628.733 -> Basically the table will be displayed directly

630.6 -> Calculated answer

632.666 -> If you want to modify the formula

634.233 -> You can click on the cell

635.833 -> Modify formulas in the "Data Editing Bar" above

639.166 -> Or, you can also face the field you want to edit

641.466 -> Left click twice to edit the formula in the cell

645.1 -> Excel's formula is just like text

647.266 -> Can be copied and pasted

649.133 -> And this feature is the most important point of Excel

652.266 -> We might as well try

653.566 -> Right-click on the F3 field, copy

657.566 -> Then click the F4 field below

659.466 -> To execute the right mouse button, paste

662.766 -> Then you will find it very interesting here

664.633 -> Excel does not directly add the amount of 1350

667.3 -> Copy the bottom cell

669.333 -> But copied the "formula" of this field

672.166 -> And more importantly

673.7 -> Excel is not foolish using the original D3 x E3 formula

677.866 -> But very cleverly with the movement of the field

680.033 -> Automatically correct formula to D4 x E4

683.5 -> We do n’t need the fields below

685.333 -> Copy and paste one by one

687.166 -> We can refer to the previous method of copying series and dates

690.366 -> Drag the control point in the bottom right corner of the cell straight down

693.633 -> You can do all the field calculations at once

696.966 -> Finally, I checked all the "Unit Price" fields

699.7 -> And click the "Format" drop-down menu above

702.733 -> Apply "currency symbol"

704.933 -> If you don't need extra decimal places

706.933 -> You can click the "Decrease Decimal Places" button

709.166 -> To remove these decimals

711.466 -> The "Total amount" field can also be applied

713.4 -> Same currency format

714.866 -> So we can select the "Unit Price" field

717.8 -> Click the "Copy Format" button in the upper left corner

720.766 -> Then use the mouse to select the "Total Amount" field

723.8 -> This way you can copy the currency format over

727.066 -> OK, that's it for today's introduction to Excel

730.133 -> I hope to help some friends who are new to Excel

733.033 -> Then we will see you next time, bye

Source: https://www.youtube.com/watch?v=wg3R5LdQ56k