设为首页收藏本站|繁體中文

Excel 技巧网

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 16435|bwin官网地址: 34

[物流管理] 利用SQL创建动态数据透视表统计查询进销存状况

  [复制链接]
发表于 2011-7-10 11:15:31 | 显示全部楼层 |阅读模式
  • 署名作者: lrlxxqxa
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 2003以前版本 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    本帖最后由 lrlxxqxa 于 2011-11-13 12:31 编辑

    一、背景简述:

    随着信息技术的飞速发展,对企业的进销存动态管理也不断提出高度个性化的要求,在商品的购销链各个环节,经常需要利用Excel统计查询进销存状况,但源数据往往放置在多张工作表中,且结构和字段名称都有所差异;快捷的创建动态查询,即时依据源数据变动反映进销存状况,不但能够帮助业务人员提高工作效率,而且能满足管理者的个性化需求,做出及时准确的决策。

    本文就结合实例来介绍几种库存管理查询的数据处理方法,并对各种方法进行对比阐述优缺点及需要注意的事项。

    二、提出问题:

    数据源工作簿中包含期初库存、本期收入和本期发出3张工作表,分别记录了各经销商和物料的进销存明细,但各张工作表结构不完全一致,且字段名称不同,如何在此基础上准确的对多字段下各项数据进行分类并统计呢?

    三、分析问题:

    首先观察源数据结构,各源表结构如下:

    期初库存表:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    本期收入表:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    本期发出表:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    明确目的是统计各物料的收发、结存情况(期初库存、本期收入、本期发出、期末结存)后,联想到两点:

    1、SQL语句中的查询、更新和管理数据功能,联合和嵌套查询使其具有极大的灵活性和强大功能;

    2、数据透视表的友好交互界面,依据需求动态的改变版面布局,按照不同方式分析处理数据,运算快捷且便于更新。

    基于此两点,确定解决问题的思路是双剑合璧:利用SQL语句创建动态数据透视表。

    四、解决问题:

    本文给出3种解法,概述如下:

    1、方法一、利用SQL语句调取少量字段并创建新字段“标识”,将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值,然后创建动态数据透视表,最后在透视表中创建计算项进行计算;

    2、方法二、利用SQL语句调取全部字段并创建新字段“标识”,将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值,然后创建动态数据透视表,依据需求拖入需要的字段进入行标签,最后在透视表中创建计算项进行计算;

    3、方法三、利用SQL语句创建独立的“期初库存”、“本期收入”和“本期发出”字段,然后创建动态数据透视表,拖入全部字段进入行标签,最后在透视表中创建计算字段进行计算;

    下面分别在2至4楼详细解析各种方法,5楼对比分析并总结。




                                   
    登录/注册后可看大图
    本主题已经同步到 lrlxxqxa的微博

    库存表(原始).rar

    0 Bytes, 下载次数: 451, 下载积分: 消费券 -5 Ti币

    利用SQL创建动态数据透视表统计查询进销存状况

    评分

    参与人数 1魅力值 +5 收起 理由
    she
    + 5
    耐心解答:)

    查看全部评分

     楼主| 发表于 2011-7-12 12:32:41 | 显示全部楼层
    本帖最后由 lrlxxqxa 于 2011-11-13 12:32 编辑

    方法一、利用SQL语句调取少量字段并创建新字段“标识”,将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值,然后创建动态数据透视表,最后在透视表中创建计算项进行计算;

    需要注意的是SQL语句中的各字段名称要和源表中一致,若字段中含有空格要用[]括起来,如本期收入工作表中的“ 数量 ”需要写为[ 数量 ]
    1. select 供應商,物料名稱,數量, "期初库存" as 标识 from [期初庫存$] union all
    2. select 供應商,貨物名稱,[ 數量 ], "本期入库" as 标识 from [本期收入$] union all
    3. select 供慶商,物料名稱,數量, "本期出库" as 标识 from [本期發出$]]
    复制代码
    依次点击“数据”--》“现有连接”--》“浏览更多”--》选择位置和工作簿

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    创建数据透视表后,建立计算项“期末库存”
    1. =期初库存 +本期入库 -本期出库
    复制代码
    2010版本下操作过程如下:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    2003版本下操作过程如下:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况

    库存表.rar

    0 Bytes, 下载次数: 175, 下载积分: 消费券 -5 Ti币

    利用SQL创建动态数据透视表统计查询进销存状况

     楼主| 发表于 2011-7-12 12:33:13 | 显示全部楼层
    本帖最后由 lrlxxqxa 于 2011-7-12 14:43 编辑

    方法二、利用SQL语句调取全部字段并创建新字段“标识”,将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值,然后创建动态数据透视表,依据需求拖入需要的字段进入行标签,最后在透视表中创建计算项进行计算;
    1. select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 数量, "期初库存" as 标识 from [期初庫存$]  union all
    2. select 供應商,倉別,類別,貨物名稱 as 物料名稱,規格,顏色,單位,[ 數量 ] as 数量, "本期入库" as 标识  from [本期收入$]  union all
    3. select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 数量, "本期出库" as 标识 from [本期發出$]

    复制代码
    创建数据透视表后,建立计算项“期末库存”
    1. =期初库存+本期入库 -本期出库
    复制代码


    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    注意:

    基于这个SQL语句创建的数据结构,当添加行标签少的时候运算很快,但当不断填入行标签时,就会使右边的数据区记录条数猛增,虽然数据源表只有几条数据,但添加5个字段进入行标签的时候已超过2万数据,当添加第6个行标签时,会出现“数据透视表在工作表上安排不下。是否要尽可能显示?”的提示(如下)

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    继续确定依然可以执行运算,但因为重复的字段一一比对运算产生了笛卡尔乘积。当加入第7个行标签的时候会出现卡死的现象,导致运算无法完成。所以此种方法只适用于要求显示少数行标签时使用。布局如下:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    关于此方法的操作及遇到的提示动画如下:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况
     楼主| 发表于 2011-7-12 12:34:27 | 显示全部楼层
    本帖最后由 lrlxxqxa 于 2011-11-13 12:34 编辑

    方法三、利用sql语句创建独立的“期初库存”、“本期收入”和“本期发出”字段,然后创建动态数据透视表,拖入全部字段进入行标签,最后在透视表中创建计算字段进行计算;
    1. select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 期初库存,0 as 本期入库,0 as 本期出库  from [期初庫存
    2. $] union all
    3. select 供應商,倉別,類別,貨物名稱,規格,顏色,單位,0 as 期初库存,[ 數量 ] as 本期入库,0 as 本期出库 from [本期收
    4. 入$] union all
    5. select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,0 as 期初库存,0 as 本期入库,數量 as 本期出库 from [本期發出$]
    复制代码
    创建数据透视表后,添加计算字段“期末库存”
    1. =期初库存+本期入库 -本期出库
    复制代码

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    这样创建的数据透视表更加灵活,布局如下:

    利用SQL创建动态数据透视表统计查询进销存状况

    利用SQL创建动态数据透视表统计查询进销存状况


    上述代码可简化为如下形式。sql中结构相同的多语句并联,缺省字段名称时默认对应前面的字段,也就是会顺序使用第一个select语句中的相应位置的字段名。
    1. select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 期初库存,0 as 本期入库,0 as 本期出库  from [期初庫存$] union all
    2. select 供應商,倉別,類別,貨物名稱,規格,顏色,單位,0,[ 數量 ] as 本期入库,0 from [本期收入$] union all
    3. select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,0,0,數量 as 本期出库 from [本期發出$]
    复制代码

    库存表(改进).rar

    0 Bytes, 下载次数: 355, 下载积分: 消费券 -5 Ti币

    利用SQL创建动态数据透视表统计查询进销存状况

    评分

    参与人数 1魅力值 +5 收起 理由
    + 5
    感谢帮助:)

    查看全部评分

     楼主| 发表于 2011-7-12 13:12:04 | 显示全部楼层
    本帖最后由 lrlxxqxa 于 2011-7-18 17:16 编辑

    对比分析

    方法1和方法2都是通过新建一个“标识”字段,并将每种库存信息作为该字段的内容填充,最后通过创建数据透视表计算项来计算期末库存。方法1只利用SQL调取必要字段创建数据透视表,无法呈现其他字段信息;方法2虽然利用SQL调取了所有的字段创建数据透视表,但行标签的数量受限制。

    方法3是将每种库存信息提取出来并新建一个字段,最后通过创建数据透视表计算字段来计算期末库存。此方法创建出来的数据透视表布局不受限制,能够更加灵活的根据需求变换数据展现方式。
    发表于 2011-8-15 12:32:56 | 显示全部楼层
    努力学习,谢谢楼主.
    发表于 2011-10-9 16:57:09 | 显示全部楼层
    谢谢分享,学习学习
    发表于 2011-10-27 01:20:44 | 显示全部楼层
    lrlxxqxa 发表于 2011-7-12 12:34
    方法三、利用sql语句创建独立的“期初库存”、“本期收入”和“本期发出”字段,然后创建动态数据透视表,拖 ...

    不错,支持下**
    发表于 2011-11-24 19:59:38 | 显示全部楼层
    谢谢分享,好好学习
    发表于 2012-1-24 14:52:50 | 显示全部楼层
    收藏了。
    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Powered by Discuz! X3.4

    © 2001-2017 Comsenz Inc.

    快速bwin官网地址 返回顶部 返回列表