This book is intended to serve as a basic introduction to scientific computing by treating problems from various areas of physics - mechanics, optics, acoustics, and statistical reasoning in the context of the evaluation of measurements. After working through these examples, students are able to independently work on physical problems that they encounter during their studies. For every exercise, the author introduces the physical problem together with a data structure that serves as an interface to programming in Excel and Python. When a solution is achieved in one application, it can easily be translated into the other one and presumably any other platform for scientific computing. This is possible because the basic techniques of vector and matrix calculation and array broadcasting are also achieved with spreadsheet techniques, and logical queries and for-loops operate on spreadsheets from simple Visual Basic macros. So, starting to learn scientific calculation with Excel, e.g., at High School, is a targeted road to scientific computing. The primary target groups of this book are students with a major or minor subject in physics, who have interest in computational techniques and at the same time want to deepen their knowledge of physics. Math, physics and computer science teachers and Teacher Education students will also find a companion in this book to help them integrate computer techniques into their lessons. Even professional physicists who want to venture into Scientific Computing may appreciate this book.
Author(s): Dieter Mergel
Publisher: Springer
Year: 2023
Language: English
Pages: 492
City: Cham
Preface
Contents
About the Author
1 Introduction
1.1 A Two-Track Didactical Approach
1.2 What Can You Expect?
1.3 What Do You Need?
1.4 Tim, Alac, and Mag
1.5 Didactic Concept
1.6 Subject Matter
1.7 Getting Started with Excel
1.7.1 Start Menu
1.7.2 Spreadsheet Presentation
1.8 Getting Started with Python
1.9 Skills to Be Trained
2 Data Structures, Excel and Python Basics
2.1 Introduction: Named Ranges in Excel, Arrays in Numpy
2.2 Characteristics of a Parabola
2.2.1 Different Definitions of a Parabola
2.2.2 Data Structure and Nomenclature
2.3 Basic Exercise in Spreadsheet Calculation
2.3.1 Cell Addressing
2.3.2 Graphical Representation of a Function
2.3.3 Smart Legends in Figures
2.3.4 Scroll Bars
2.3.5 Summary: Cell References and Name Manager
2.3.6 What Have We Learned so Far, and How to Proceed Further?
2.3.7 Python Program
2.4 Python and NumPy Basics
2.4.1 Basic Exercise
2.4.2 Data Structures
2.4.3 Python Libraries
2.4.4 Numpy Constructions
2.4.5 Standard Plot Program
2.4.6 Formatted Output
2.5 Matrix Calculations in Excel and Python
2.5.1 Data Structure and Nomenclature
2.5.2 Operations on Arrays
2.5.3 Matrices in Spreadsheets
2.5.4 Matrices in Python
2.6 Four Parabolas and Their Upper Envelope
2.6.1 Graphical Representation
2.6.2 Data Structure and Nomenclature
2.6.3 Spreadsheet Calculation
2.6.4 Python Program
2.6.5 Extrema Along Different Axes
2.7 Sum of Four Cosine Functions
2.7.1 Sound and a Cosine Identity
2.7.2 Data Structure and Nomenclature
2.7.3 Spreadsheet Layout
2.7.4 Python Program
2.7.5 Producing Labels (as Strings) in Excel and Python
2.8 Questions
3 Formula Networks and Linked Diagrams
3.1 Introduction: Well-Structured Sheets and Programs
3.2 Image Construction for Focusing and Diverging Lenses
3.2.1 Straight Line Equation
3.2.2 Geometrical Image Construction for a Thin Focusing Lens
3.2.3 Imaging Equation with Correct Signs
3.2.4 Beam Through a Converging Lens that Really Contributes to the Image
3.2.5 Data Structure and Nomenclature
3.2.6 Spreadsheet Calculation
3.2.7 Python Program
3.3 Doppler Effect
3.3.1 A Formula for All Cases
3.3.2 A Sound Source Passes a Remote Receiver
3.3.3 Data Structure and Nomenclature
3.3.4 Spreadsheet Calculation “Remote Receiver”
3.3.5 Python Program “Remote Receiver”
3.4 Exponentials
3.4.1 Explosive Character of Exponentials
3.4.2 General Exponential Function
3.4.3 Representation in a Diagram
3.4.4 Diode Characteristics I(U)
3.4.5 Data Structure and Nomenclature
3.4.6 Spreadsheet Calculation
3.4.7 Python Program
3.5 Questions
4 Macros with Visual Basic and Their Correspondences in Python
4.1 Introduction: For, If, Sub/Def
4.2 Basic Exercise: For-Loops
4.2.1 Visual-Basic-Editor 1: Editing
4.2.2 Programming
4.3 Macro-Controlled Drawings with For, Sub, If
4.3.1 Macro Recorder
4.3.2 Visual-Basic Editor 2: Macro Recording, Debugging
4.3.3 Programming Elements
4.4 A Checkerboard Pattern (Excel)
4.4.1 Checkerboard, Same-Colored and Multi-colored
4.4.2 Global Variables
4.5 A Checkerboard Pattern (Python)
4.5.1 Turtle
4.5.2 Differences to Visual Basic
4.5.3 Checkerboard with Squares, Triangles, and Circles
4.6 Drawing Densely-Packed Atomic Layers; Crystal Physics
4.6.1 Program Structure and Geometry
4.6.2 Data Structure and Nomenclature
4.6.3 Excel
4.6.4 Python
4.7 Text Processing
4.7.1 Cutting and Joining Strings
4.7.2 Data Structure and Program Flow
4.7.3 Excel
4.7.4 Programming Step by Step
4.7.5 VBA Constructs
4.7.6 Python
4.8 Processing the Protocol of a Measuring Device
4.8.1 Protocol of a Measuring Device
4.8.2 Detection of Code Words
4.8.3 Data Structure and Nomenclature
4.8.4 Excel
4.8.5 Python
4.9 User-Defined Functions
4.9.1 User-Defined Functions as Add-In
4.9.2 Scalar Product and Vector Product
4.9.3 Python
4.10 Questions and Tasks
5 Basic Mathematical Techniques
5.1 Introduction: Calculus, Vectors, and Linear Algebra
5.2 Straight-Line Segment Under a Magnifying Glass
5.2.1 Under a Magnifying Glass
5.2.2 Data Structure and Nomenclature
5.2.3 Spreadsheet Calculation
5.2.4 Plotting Vectors with Python Matplotlib
5.3 Differentiation
5.3.1 First and Second Derivative
5.3.2 Data Structure and Nomenclature
5.3.3 Spreadsheet Layout
5.3.4 Python Program
5.4 Integration
5.4.1 Area Under a Curve
5.4.2 Length of a Curve
5.4.3 Data Structure and Nomenclature for the Arrays in the Integration
5.4.4 Python Program
5.4.5 Spreadsheet Solution
5.5 Vectors in the Plane
5.5.1 Vectors
5.5.2 Data Structure and Nomenclature
5.5.3 Spreadsheet Layout
5.5.4 Python Program
5.6 Tangents to and Perpendiculars on a Curve
5.6.1 At/On a Polynomial and an Ellipse
5.6.2 Data Structure and Nomenclature
5.6.3 Python Program
5.6.4 Spreadsheet Solution
5.7 Banked Curve
5.7.1 Cross-Section of the Road
5.7.2 Data Structure and Nomenclature
5.7.3 Python Program
5.7.4 Spreadsheet Solution
5.8 Weighted Average
5.8.1 A Mobile with Two Arms
5.8.2 Data Structure and Nomenclature
5.8.3 Python Program
5.8.4 Spreadsheet Calculation
5.9 Systems of Linear Equations
5.9.1 Polynomial and Electrical Network
5.9.2 Data Structure and Nomenclature
5.9.3 Spreadsheet Solutions
5.9.4 Python Programs
5.10 Some Mathematical Functions
5.11 Questions and Tasks
6 Superposition of Movements
6.1 Introduction: Translations and Rotations
6.2 Projectile Trajectory with Velocity Vectors (T-T)
6.2.1 Projectile Trajectory and Velocity Vectors
6.2.2 Data Structure and Nomenclature
6.2.3 Spreadsheet
6.2.4 Python
6.2.5 Animation of Figures with FuncAnimation
6.3 Cycloid, Rolling Curve (R-T)
6.3.1 Trace of a Writing Point Fixed at a Rolling Wheel
6.3.2 Data Structure and Nomenclature
6.3.3 Excel
6.3.4 Python
6.4 Foucault’s Pendulum (T-R)
6.4.1 A Lecture Experiment
6.4.2 Data Structure and Nomenclature
6.4.3 Excel
6.4.4 Python
6.5 Anchor, Deflected Out of Its Rest Position (R-R)
6.5.1 Deflected Anchor
6.5.2 Data Structure and Nomenclature
6.5.3 Excel
6.5.4 Python
6.6 Wavefronts, Sound Barriers, and Mach Cone (T-T)
6.6.1 Emitting Sound Waves
6.6.2 Data Structure and Nomenclature
6.6.3 Spreadsheet Solution
6.6.4 Python
6.7 Questions and Tasks
7 Integration of Newton’s Equation of Motion
7.1 Introduction: Approximated Mean Value Instead of Exact Integration
7.1.1 Newton’s Equation of Motion
7.1.2 Four Methods for Estimating the Average Acceleration in a Time Segment
7.1.3 Tactical Approaches in Python and Excel
7.2 Harmonic Oscillation with “Progress with Look-Ahead” and “Runge–Kutta”
7.2.1 Equation of Motion
7.2.2 Data Structure and Nomenclature
7.2.3 Spreadsheet Calculation
7.2.4 Python
7.3 Falling from a (Not Too) Great Height
7.3.1 Limiting Cases, Analytically Solved
7.3.2 Data Structure and Nomenclature
7.3.3 Spreadsheet
7.3.4 Python
7.4 Stratospheric Jump
7.4.1 Data Structure and Nomenclature
7.4.2 Spreadsheet Calculation
7.4.3 Python
7.5 A Car Drives with Variable Power
7.5.1 Various Types of Power
7.5.2 Data Structure and Nomenclature
7.5.3 Excel
7.5.4 Python
7.6 Bungee Jump
7.6.1 Simulation of the Motion
7.6.2 Analytical Calculations
7.6.3 Data Structure and Nomenclature
7.6.4 Excel
7.6.5 Python
7.7 Questions and Tasks
8 Random Numbers and Statistical Reasoning
8.1 Introduction: Statistical Experiments Instead of Theoretical Derivations
8.2 Equi-Distributed Random Numbers, Frequencies of Occurrence, Chi2 Test
8.2.1 A Spreadsheet Experiment with Random Numbers
8.2.2 Data Structure and Nomenclature
8.2.3 Python
8.3 Points Randomly Distributed in a Unit Square
8.3.1 Creation and Distribution of the Points
8.3.2 Data Structure and Nomenclature
8.3.3 Excel
8.3.4 Python
8.3.5 Why Calculate Twice?
8.4 Set Operations in Numpy
8.4.1 Sets
8.4.2 Data Structure and Nomenclature
8.4.3 Python
8.5 Normally Distributed Random Numbers
8.5.1 Normal Distribution, Probability Density and Distribution Function
8.5.2 Random-Number Generator and Frequencies of Occurrence
8.5.3 Where Do Observed and Theoretical Frequencies Fit Better Together?
8.5.4 Data Structure and Nomenclature
8.5.5 Python
8.5.6 Excel
8.6 Random-Number Generator, General Principle
8.7 Diffraction of Photons at a Double-Slit
8.7.1 Physical Background: Wave-Particle Dualism
8.7.2 Cos2 Distribution
8.7.3 Data Structure and Nomenclature
8.7.4 Python
8.7.5 Excel
8.7.6 Simulation in a Spreadsheet
8.8 Chi2 Distribution and Degrees of Freedom
8.8.1 Data Structure, Nomenclature
8.8.2 Python
8.9 Questions and Tasks
9 Evaluation of Measurements
9.1 Introduction: We Know Everything and Play Stupid
9.2 Weighing a Glass Substrate
9.2.1 Discussion on the Accuracy of a Balance
9.2.2 Data Structure and Nomenclature
9.2.3 Excel
9.2.4 Python
9.3 A Procedure for Rounding to Relevant Digits
9.3.1 Numerical Evaluations
9.3.2 Spreadsheet Calculation
9.3.3 Python Function
9.3.4 VBA Function
9.4 Increasing the Measuring Accuracy Through Repetition
9.4.1 Standard Deviation and Standard Error of the Mean Value of a Measurement Series
9.4.2 Data Structure and Nomenclature
9.4.3 Python Program
9.4.4 Spreadsheet Layout for This Task
9.4.5 How to Report a Measurement Result
9.5 The t Statistics Connects Confidence Interval with Confidence Level
9.5.1 Student's t Distribution
9.5.2 Data Structure and Nomenclature
9.5.3 Spreadsheet Calculation
9.5.4 Python Program
9.6 Combining Results from Several Measurement Series
9.6.1 Combining Two Measurement Results
9.6.2 Data Structure and Nomenclature
9.6.3 Spreadsheet Calculation
9.6.4 Python, Internally and Externally Consistent Error of the Combined Result
9.7 Propagation of Standard Deviations
9.7.1 Rules for Propagation of Standard Deviations
9.7.2 Data Structure and Nomenclature
9.7.3 Spreadsheet Calculation
9.7.4 Python Program
9.8 Propagation of Confidence Intervals
9.8.1 From Variance to Confidence
9.8.2 Sum and Product of Two Measurands
9.9 Mass of a Thin Film on a Glass Substrate
9.9.1 Instructions for Use for Accurate Measurements and Their Results
9.9.2 Data Structure and Nomenclature
9.9.3 Spreadsheet Solution
9.9.4 Python Program
9.10 Questions and Tasks
10 Fitting Trend Curves to Data Points
10.1 Introduction: Linear and Nonlinear Regression
10.1.1 Straight Line Through Data Points by Sight
10.1.2 Multilinear Regression
10.1.3 Nonlinear Regression
10.1.4 Coefficient of Determination R2
10.1.5 C-spec Error with Iterative t Adaptation
10.2 Linear Trend Line
10.2.1 Creating Data Points and Evaluating Them
10.2.2 Data Structure and Nomenclature
10.2.3 Spreadsheet Calculation with Linest
10.2.4 Python Program
10.3 Fitting a Polynomial Trend Line to Data Points with Multilinear Regression
10.3.1 Introduction
10.3.2 Data Structure and Nomenclature
10.3.3 Spreadsheet Solution
10.3.4 Python Solution
10.4 Exponential Trend Line
10.4.1 Exponential and Logarithm
10.4.2 Exponential or Polynomial?
10.4.3 Data Structure and Nomenclature
10.4.4 Python Program
10.4.5 Spreadsheet Solution
10.5 Solving Nonlinear Equations
10.5.1 Intersection of Straight Lines with a Parabola
10.5.2 Data Structure and Nomenclature
10.5.3 Spreadsheet Calculation
10.5.4 Python Program
10.6 Temperature Dependence of the Saturation Magnetization of a Ferromagnet
10.6.1 Langevin Function
10.6.2 Data Structure and Nomenclature
10.6.3 Spreadsheet Layout
10.6.4 Python
10.7 Fitting Gaussians to Spectral Lines with Nonlinear Regression
10.7.1 Fitting the Sum of Two Gaussians to Data Points
10.7.2 C-spec Errors of the Coefficients by a Statistical Simulation
10.7.3 Data Structure and Nomenclature
10.7.4 Python
10.7.5 Spreadsheet
10.7.6 C-spec Error of the Optimized Coefficients by Simulation-Based t Adaptation
10.8 Questions and Tasks
Index
Index
