#execl

প্রিয় দর্শক, (Dear viewers)

আমরা এখন দেখবো যে কিভাবে এক্সেল শিট ব্যবহার করে অনেক জটিল কাজ সহজেই সারা যায়. (We will now see how using Excel Sheets can easily solve many complex tasks.) 

তন্মধ্যে একটি হলো ইনভেন্টরি ম্যানেজমেন্ট ( One of them is Inventory Management)

1. First open one MS Excel Worksheet.

2. Rename it- Factory stock software in MS Excel.

 3. Insert worksheet (Shift+F11) up to five & rename as need (1. Items, 2. Received, 3. Supplied to customer, 4. Returned & 5. Stock)

4. In Item worksheet select first 4 column or more (Item code, Item Description, Units, Types E/M/C.)

5. you can use Item code or sl no in first column. Then in other page you just put this item no & automatically Item discretion will fill. Let see how it happen-

6. Go to received page, make table like image- ( Date, Item code, Item Description, Units, Qty, Supplier name, etc.....)

7. Then put & click cursor on 

2#3#c3 cell (page no or received page#row no#cell no). 

8. Law for link one page to another page

=VLOOKUP(B3,Itemslist,2,FALSE)

Items list means select first pages or Items page table. 

2 means 1st pages column no

9.After complete it you will just input item code, then Item description will automatic fill & same law for 2#4#c4. 

10. same we can make “supplied to customer” page in same procedure.

11. “Return page” also same.

12. Now come In last page “Stock”. No need any input. All result will make by Law.

13. Column Receive qty law: 

first click & put cursor on stock D3 cell, then 

=SUMIFS(Received!$E$3:$E$10006,Received!$C$3:$C$10006,Stock!B3)

14. Column Supply qty law:

first click & put cursor on stock E3 cell, then

=SUMIFS(Supply!$E$3:$E$10008,Supply!$C$3:$C$10008,Stock!B3)

15, Column Return qty law:

first click & put cursor on stock D3 cell, then

=SUMIFS(Returened!$E$3:$E$31,Returened!$C$3:$C$31,Stock!B3)

16. Now the balance stock

=D3-E3+F3

If you want you can use conditional formatting  (as cursor position) 

Process for if your balance become low from your safety Quantity:

Select first cell, where you want see the result then  click on conditional formatting - highlight cells rules- less then (then set your stranded value)

一个比较笨的手工方法，就是先利用excel生成sql语句，然后再到mysql中运行，这种方法适用于excel表格导入到各类sql数据库：

一、假设你的表格有A、B、C三列数据，希望导入到你的数据库中表格table，对应的字段分别是col1、col2、col3

二、在你的表格中增加一列，利用excel的公式自动生成sql语句，具体方法如下：

1、增加一列（假设是D列）

2、在第一行的D列，就是D1中输入公式： =CONCATENATE("insert into table (col1,col2,col3) values ('",A1,"','",B1,"','",C1,"');")

3、此时D1已经生成了如下的sql语句： insert into table (col1,col2,col3) values ('a','11','33');

4、将D1的公式复制到所有行的D列（就是用鼠标点住D1单元格的右下角一直拖拽下去啦）

5、此时D列已经生成了所有的sql语句

6、把D列复制到一个纯文本文件中，假设为sql.txt