《基于Excel的高斯克吕格投影坐标正反算实训》教案（大学本科测绘工程专业三年级）

《基于Excel的高斯-克吕格投影坐标正反算实训》教案（大学本科测绘工程专业三年级）

  一、课程基本信息与设计理念

  本课程隶属于测绘工程专业核心课程《大地测量学基础》的实践教学模块，面向本科三年级学生开设。学生已先行修完《测量学》、《数字地形测量学》、《地图学》及《高等数学》（含空间解析几何）、《计算机程序设计基础》等课程，具备了必要的测绘理论、数学基础与初步的计算机数据处理能力。高斯-克吕格投影是我国国家基本比例尺地形图统一采用的地图投影，其坐标换算（正算：大地坐标B,L到平面直角坐标x,y；反算：平面直角坐标x,y到大地坐标B,L）是测绘数据处理、GIS空间分析、工程勘测设计与施工放样等工作的基石。传统的教学多侧重于公式推导与理论讲解，学生往往“听得懂、推得动、算不对”，面对实际数据束手无策，理论与实践存在显著鸿沟。

  本教学设计秉持“工程教育认证”OBE（成果导向教育）理念与“新工科”建设思想，打破传统以知识传授为主的线性教学模式，重构为以“复杂工程问题解决能力”培养为核心的沉浸式、项目化实训。其核心创新在于：1.工具平民化：摒弃专业商用软件（如ArcGIS、CASS）的“黑箱”操作，选择普遍掌握的Excel作为计算平台，迫使学生深入理解算法每一步的物理与数学意义，实现“从原理到代码”的深度转化。2.教学高阶性：任务不再局限于单一的正算或反算，而是设计为包含数据校验、批量处理、精度评估、可视化分析的完整数据处理流程，培养学生系统性思维。3.能力复合化：深度融合大地测量学、数值分析、计算机编程（VBA）、数据可视化及工程规范，体现跨学科知识整合与应用能力。4.评价过程化：通过阶梯任务、分组研讨、报告答辩等多维度手段，全过程评估学生的知识掌握、技能运用与创新思维。

  本教案旨在通过8学时（理论铺垫2学时，上机实训6学时）的密集训练，使学生不仅精通高斯投影换算的核心算法，更能驾驭Excel这一通用工具解决专业复杂计算问题，成为既懂理论又善工具的复合型测绘工程预备人才。

  二、学情分析与教学目标

  （一）深入学情分析

  认知基础方面，学生已掌握椭球体基本参数、大地坐标系与空间直角坐标系的概念，理解地图投影变形的基本类型，并初步学习了高斯投影的变形规律、分带方法和坐标构成。技能层面，学生能熟练使用Excel进行基本公式计算、函数应用和图表绘制，部分学生接触过VBA但缺乏系统性工程应用。思维特点上，大三学生正处于专业思维形成的关键期，已不满足于“是什么”，渴望探究“为什么”和“怎么用”，但对如何将离散的理论知识点串联成解决实际问题的完整工作流存在困难，缺乏误差传播意识与算法稳健性设计思维。常见误区包括：混淆不同基准椭球参数、忽视换带计算与投影带号的关系、对迭代计算收敛性缺乏控制、对计算结果缺乏有效的检核手段。

  （二）三维教学目标

  基于上述分析，确立如下教学目标：

  1.知识与技能目标：

   （1）准确复述并解释高斯-克吕格投影正反算的基本原理、公式体系及适用条件，能辨析克拉索夫斯基椭球、CGCS2000椭球等不同基准下的参数差异。

   （2）独立在Excel环境中，利用公式、函数及VBA编程，完整实现单个点及批量数据的高斯投影坐标正算（B,L→x,y）与反算（x,y→B,L）计算流程。

   （3）能够设计并实施计算结果的有效检核方案（如正反算闭合差、与已知成果比对、可视化校验），评估计算精度，并对异常结果进行诊断与调试。

  2.过程与方法目标：

   （1）经历“问题定义—算法分解—工具实现—验证优化”的完整工程问题解决过程，掌握将复杂数学模型转化为可执行计算步骤的方法论。

   （2）通过小组协作，完成一个包含数据预处理、核心计算、成果整理与可视化分析的小型项目报告，提升团队协作与工程文档撰写能力。

   （3）学会利用Excel高级功能（数据透视表、条件格式、图表动态关联）进行测绘数据的探索性分析与成果表达。

  3.情感、态度与价值观目标：

   （1）在攻克迭代计算、程序调试等难关中，培养严谨求实、精益求精的“工匠精神”和科学探索精神。

   （2）认识到通用工具在解决专业问题中的强大潜力，破除对专业软件的依赖与神秘感，树立自主创新的工具应用意识。

   （3）通过理解高斯投影在国家坐标系中的统一性作用，深化国家坐标系统一对于国家经济建设与国防安全重要性的认识，增强专业使命感。

  三、教学重点与难点

  教学重点：

  1.高斯投影正反算公式的Excel实现逻辑：重点不在于公式的复杂推导，而在于如何将公式分解为Excel中可顺序执行的单元格计算链。包括：角度与弧度的转换、子午线弧长公式的级数展开计算、迭代初值的选定与循环引用（或VBA循环）的实现。

  2.计算过程的健壮性与检核设计：确保计算表格能处理不同投影带、不同象限（含带号与自然值转换）、不同椭球参数的数据，并内置正反算闭合检核、坐标反算迭代收敛判断等质量控制环节。

  教学难点：

  1.高斯投影反算的迭代算法实现：反算公式本身是隐函数形式，需要迭代求解底点纬度Bf。难点在于如何在Excel中构造迭代循环。解决方案有两种：一是利用Excel“迭代计算”选项配合循环引用公式；二是编写VBA函数进行更灵活的控制。需要引导学生比较两种方案的优劣。

  2.跨带计算与坐标通用值的理解与应用：学生容易混淆坐标的自然值（去掉带号）和通用值（含带号），在处理跨越两个投影带的数据或进行坐标换带计算时易出错。需要通过可视化手段（如用Excel条件格式标记不同带的数据点）和实际算例强化理解。

  3.误差意识与数值稳定性培养：在Excel中进行级数展开计算时，截断误差的影响；迭代计算的收敛精度设置。需要引导学生思考并实验“计算到小数点后多少位才足够？”、“如何判断迭代是否收敛？”，培养其数值计算敏感性。

  四、教学策略与资源准备

  （一）教学策略

  本课程采用“翻转课堂+PBL（项目驱动）+支架式教学”混合模式。

  1.课前翻转：将投影理论、公式体系、Excel高级函数（如RADIANS,DEGREES,SUMPRODUCT用于级数求和）等基础知识制作成微视频与预习手册，学生课前自学并完成在线小测，腾出课堂时间用于高阶应用与问题解决。

  2.课中PBL驱动：围绕一个真实情境的测绘项目任务书展开。任务书要求：对某区域（横跨两个3度带）的20个GPS控制点（WGS-84经纬度）进行计算，转换为CGCS2000坐标系下的高斯平面坐标（6度带），并反算回经纬度进行检核，最终提交成果表与精度分析报告。

  3.支架式教学：提供分层学习资源：(1)基础支架：构建好的Excel计算模板，关键单元格已标注公式用途；(2)核心支架：分步骤实现的VBA代码模块，带有详细注释；(3)挑战支架：仅提供算法流程图和关键函数说明，供学有余力者自主编程实现。教师角色从讲解者转变为教练和协作者，针对各组遇到的个性化问题提供指导。

  （二）资源准备

  1.硬件环境：高性能计算机机房，确保Excel运行流畅，支持VBA宏。

  2.软件环境：统一安装Office365（或Excel2016以上版本），启用“迭代计算”功能。准备数据模拟生成器（用于生成测试数据）。

  3.学习材料：

   （1）《高斯投影坐标换算Excel实现任务书》及配套的原始数据文件（.csv格式）。

   （2）《高斯投影计算核心公式与Excel实现指南》手册。

   （3）《ExcelVBA在测绘计算中应用实例》代码库。

   （4）参考国家规范：《GB/T17798-2007地理空间数据交换格式》、《CH/T2009全球定位系统实时动态测量(RTK)技术规范》中关于坐标转换的部分。

  4.评价工具：结构化评分量规（Rubric），涵盖计算准确性、表格设计规范性、代码效率与注释、报告完整性与分析深度、团队协作与答辩表现等维度。

  五、教学实施过程（8学时详案）

  第一阶段：理论聚焦与任务导引（2学时）

  环节一：情境创设，问题提出（15分钟）

  教师活动：播放一段新闻报道，内容涉及某重大线性工程（如高铁、输油管道）因不同施工段使用的坐标系不一致（如分别采用了北京54坐标系和西安80坐标系的不同带号坐标），导致衔接处出现重大偏差，造成经济损失。引出核心问题：“作为测绘工程师，如何确保海量空间坐标在统一基准下的精确转换与无缝衔接？”

  学生活动：观看案例，参与讨论，认识到坐标系统一与精确换算的实际工程价值与严肃性。

  设计意图：以真实工程事故开场，制造认知冲突，瞬间激发学生的学习兴趣与专业责任感，将课程主题从“知识点”提升到“工程问题”高度。

  环节二：知识回顾与公式体系结构化（45分钟）

  教师活动：不进行平铺直叙的讲解，而是采用“概念图”或“思维导图”的形式，与学生共同回顾和梳理高斯投影的知识网络。重点聚焦几个关键衔接点：

  1.从“椭球面”到“平面”的保角映射条件，引出高斯投影的微分几何基础。

  2.强调“中央子午线投影为直线且长度不变”这一特性，推导出长度比公式、子午线收敛角公式的由来。

  3.将正反算公式组，按“准备量计算→主项计算→改正项计算”的逻辑链条进行分解展示。例如，正算的核心是计算“赤道至纬度B的子午线弧长X”和“经差l的幂级数展开”，将复杂的公式拆解为可逐项计算的模块。

  4.对比展示克拉索夫斯基椭球、IAG75椭球、CGCS2000椭球的核心参数（长半轴a、扁率f），强调参数代入的准确性是计算的前提。

  学生活动：跟随教师引导，填补自己预习中的知识盲区，在笔记本或电子文档上构建个人化的高斯投影知识结构图，重点记录公式的“输入-输出”接口和中间关键量。

  设计意图：帮助学生将零散的知识点整合成有逻辑关联的知识体系，理解公式背后的几何与物理意义，而非死记硬背，为后续的算法实现奠定坚实的认知基础。

  环节三：项目任务发布与实现路径研讨（30分钟）

  教师活动：下发《项目任务书》，详细解读任务要求、输入数据格式、预期输出成果及评价标准。随后，引导学生以小组为单位，对“如何用Excel完成这个任务”进行初步研讨，画出实现路径的粗略框图。

  提出启发性问题链：

  -“输入数据是WGS-84经纬度，目标输出是CGCS2000高斯坐标，中间需要经过什么步骤？”（引导出空间直角坐标转换或仅考虑参考椭球差异的简化处理）。

  -“批量处理20个点，是逐个点手动计算，还是设计一个可以下拉填充的表格？”

  -“如何用Excel判断一个点位于哪个投影带？如何自动添加带号？”

  -“反算需要迭代，Excel中实现迭代有哪些方法？各自的优缺点是什么？”

  学生活动：小组热烈讨论，尝试勾勒计算流程图，识别出任务的技术关键点（如批量处理、迭代、带号处理），并初步形成分工意向。

  设计意图：将宏观任务分解为具体的技术问题，驱动学生主动思考解决方案，明确后续实训的探索方向，实现从“被动听讲”到“主动规划”的转变。

  第二阶段：分项突破与技能建构（3学时）

  本阶段在机房进行，采用“讲解-演示-模仿-变式”循环。

  环节四：正算模块的Excel实现（90分钟）

  1.基础模板搭建（20分钟）：教师演示创建一个结构清晰的Excel工作表。包括：参数区（输入椭球a,f等常数）、数据输入区（点号、B,L）、中间计算区（分解公式）、成果输出区（x,y,带号）。强调单元格命名、区域定义和格式规范的重要性。

  2.核心公式拆解与输入（40分钟）：教师分步演示一个点的计算。关键步骤：

   -将度分秒格式的B,L转换为十进制度，再转换为弧度。

   -计算辅助量：t=tan(B),η²=e‘²cos²(B)等。

   -实现子午线弧长X的公式：将其视为关于sin(B)多项式的求和，利用SUMPRODUCT函数对应系数数组进行计算，展示比手动逐项相加的优越性。

   -计算经差l（弧度），然后构建l的2,4,6次幂项，分别乘以相应的系数（这些系数是B的函数），求和得到x的改正项和y值。

   -组合得到x=X+Δx，y值加上500公里和带号构成通用坐标。

  3.批量处理与函数封装（30分钟）：演示如何将单个点的计算行，通过相对引用和绝对引用的巧妙搭配，向下拖动填充柄，一次性完成20个点的计算。进而，引导学生将复杂的计算步骤（如弧度转换、子午线弧长）封装成自定义的ExcelLAMBDA函数（Office365新特性），实现更高层次的模块化和公式简化。

  学生活动：跟随演示，在自己的Excel文件中逐步构建正算模块。期间，教师巡视，重点解决学生因引用错误、角度格式混淆导致的#VALUE!等常见错误。鼓励学生互相检查公式。

  设计意图：手把手教学，确保所有学生都能搭建起可运行的正算框架，掌握Excel处理复杂公式的核心技巧（分解、引用、函数组合），并引入LAMBDA函数这一先进工具，提升计算表格的优雅性和可维护性。

  环节五：反算模块与迭代技术探索（90分钟）

  1.问题引入与迭代思想讲解（20分钟）：提问：“已知（x,y），如何求（B,L）？直接有公式吗？”指出反算需迭代。讲解“底点纬度”Bf的概念及其物理意义：在中央子午线上，纵坐标x对应的纬度。通过动画演示迭代逼近Bf的过程。

  2.方案对比与选择（25分钟）：教师展示两种实现方案。

   -方案A（循环引用）：在Excel选项启用“迭代计算”，设置一个单元格（如Bf初值）的公式为包含其自身的函数（例如，Bf_new=f(x,Bf_old)），通过有限次迭代（如设置最多100次，变化小于1e-12）自动收敛。演示设置方法。

   -方案B（VBA自定义函数）：打开VBA编辑器，编写一个FunctionBL2XY(ByValxAsDouble,ByValyAsDouble)AsVariant的函数，内部使用DoWhile循环实现迭代，返回一个包含B和L的数组。演示代码结构、变量定义、循环控制和精度判断。

   组织学生讨论两种方案的优缺点：方案A无需编程，但控制逻辑弱，不直观，易导致表格计算缓慢或循环引用警告；方案B控制灵活、代码清晰、可移植性强，但需要编程基础。鼓励大部分学生至少实现方案A，学有余力者挑战方案B。

  3.分步实现与调试（45分钟）：学生根据选择进行实现。教师提供详细的步骤指南或代码片段作为支架。关键点指导：

   -如何从通用坐标y中分离带号、减去500公里得到自然值。

   -迭代初值Bf0的合理设置（例如用x/a的弧度值近似）。

   -如何设置迭代终止条件（abs(Bf_new-Bf_old)<1e-12）。

   -VBA方案中，如何让函数返回两个值（使用数组或ByRef参数）。

  学生活动：动手实现反算模块。这是本课的“攻坚”阶段，学生会遇到各种计算错误（不收敛、结果NaN等）。教师鼓励学生利用“公式求值”、“调试-本地窗口”（VBA）等工具进行排查，培养调试能力。小组内开展“找bug”互助活动。

  设计意图：通过面对和解决迭代这个核心难点，让学生深刻理解数值计算方法的思想，并在对比与选择中提升技术决策能力。调试过程是培养计算思维和严谨态度的绝佳时机。

  第三阶段：整合应用与创新拓展（3学时）

  环节六：系统整合、检核与可视化（60分钟）

  1.系统整合与自动化（20分钟）：指导学生将正算、反算模块整合在一个工作簿的不同工作表，并通过“数据输入表”统一驱动。利用数据验证功能规范输入（如纬度范围-90~90），使用条件格式自动高亮显示超限或异常的计算结果。

  2.设计检核方案（20分钟）：引导学生设计多种检核方法：(1)正反算闭合差：将正算得到的(x,y)立即用反算模块计算回(B‘,L’)，与原(B,L)比较，计算点位差Δ=sqrt((B-B‘)²+(L-L’)²)，要求小于阈值（如1e-9度）。(2)与权威成果比对：教师提供2-3个点的已知精确转换成果作为“控制点”，让学生用自己的程序计算并比对。(3)可视化检核：将所有计算出的平面坐标（x,y）用Excel的散点图绘制出来，观察其相对位置关系是否符合从经纬度数据可以想象的区域形状（长条形？）。

  3.数据可视化进阶（20分钟）：演示如何利用PowerQuery导入原始csv数据，连接至计算模型，实现数据刷新自动重算。展示如何用三维地图（Excel3DMaps）将原始的经纬度点与计算后的高斯坐标点在同一地球模型上可视化，直观感受投影变形。

  学生活动：完善自己的计算表格，实现一键化计算与检核。运行检核程序，验证自己计算结果的正确性。尝试制作可视化图表，从图形角度理解坐标变换。

  设计意图：培养学生工程化的系统思维和质量控制意识。让他们明白，一个可靠的计算程序必须包含自检和验证环节。可视化将抽象的数字转化为直观的图形，深化对投影变形的空间理解。

  环节七：拓展探究与项目报告撰写（70分钟）

  1.拓展任务发布（10分钟）：教师提出高阶挑战问题，供已完成基础任务的小组选择研究：

   -挑战一（算法优化）：分析现有反算迭代公式的收敛速度，尝试采用牛顿迭代法或其他更快的算法进行改进，并比较迭代次数。

   -挑战二（跨带计算）：如果某点的实际坐标超出了其所在投影带的边缘（例如，距中央子午线经差接近甚至超过±3°），直接计算变形过大。如何实现相邻带坐标的自动换带计算？

   -挑战三（误差分析）：假设输入经纬度存在0.1秒的随机误差，利用蒙特卡洛模拟（在Excel中通过RAND函数生成大量随机扰动样本），分析这对最终平面坐标（x,y）的误差传播影响。

  2.项目报告协作撰写（40分钟）：各小组根据任务书要求，整理计算过程、核心公式截图（或VBA代码）、计算结果表格、检核报告、可视化图表以及遇到的问题与解决方案，形成结构完整的项目报告。报告需用专业的测绘报告语言撰写。

  3.成果展示与答辩预演（20分钟）：小组内部进行模拟答辩，准备5分钟的汇报PPT或直接在Excel中演示，重点阐述技术路线、创新点、遇到的问题及解决之道。

  学生活动：部分小组选择挑战任务进行深入探究。所有小组投入报告撰写与成果整理，这是对学习过程的深度反思与升华。

  设计意图：满足不同层次学生的学习需求，提供“跳一跳够得着”的挑战。项目报告的撰写是知识内化、能力外显的关键环节，综合锻炼了学生的总结、表达与协作能力。

  环节八：总结评价与反思提升（20分钟）

  教师活动：不再进行长篇总结，而是邀请1-2个小组进行简要的亮点分享。然后，教师展示利用本课程方法开发的、一个更完善的“高斯投影坐标换算工具箱”Excel文件（可能包含GUI界面、更多椭球参数、批量文件处理等功能），激发学生课后继续优化自己作品的兴趣。最后，明确课后任务：完善个人报告，并思考“如果用Python或MATLAB来实现，会有何不同？各有什么优劣？”

  学生活动：聆听同伴分享，欣赏更高级的作品，思考课程的延伸方向，明确课后任务。

  设计意图：通过同伴互学和更高阶作品的展示，开拓学生视野，将课程的学习从课堂延伸到课外，激发持续学习和工具迁移的兴趣。

  六、教学评价设计

  采用“过程性评价为主，终结性评价为辅”的多元评价体系。

  1.过程性评价（占总评60%）：

   -课堂参与与协作（15%）：观察记录学生在研讨、提问、互助中的表现。

   -分阶段任务完成度（30%）：通过课堂巡视、提交阶段性的Excel文件副本，评估正算模块、反算模块的实现质量与规范性。

   -挑战任务完成情况（15%）：对选择并完成拓展挑战的小组给予额外加分，鼓励创新。

  2.终结性评价（占总评40%）：

   -项目报告与成果（25%）：依据评分量规，对报告的完整性、准确性、规范性、分析深度及可视化质量进行评分。

   -答辩或成果展示（15%）：评估小组的表达能力、问题回答的逻辑性与专业性。

  七、板书设计（主屏投屏内容规划）

  板书（投屏）并非线性记录，而是作为思维导图和关键信息锚点。

  -核心区：始终展示高斯投影正反算的“输入-处理