Surface charts in Excel deserve special attention. They allow to see your data in three dimensions and can be particularly useful in engineering applications (design and control of processes) as well as in the art of drawing and painting.
Typical surface chart shows how the dependent variable (z) behaves as a function of two independent variables (x & y). In other words, the chart illustrates outcome of any nonlinear function involving two variables. E.g. we can plot speed of some technological process (reaction) as a function of pressure and temperature.
In this post I'll show how you can prepare a template for creating surface charts based on equations including variety of math functions (algebraic, transcendental, periodic). Such template can be very useful in all kinds of design, as it allows you to visualize plenty of solutions in minutes.
Let's start with just one example of surface chart I've created. This is 3D plot of equation shown in the title of this chart:
How can you create similar charts based on your own equations?
Here's the way to follow:
- First, create kind of a small table in exactly the same location on your worksheet as shown below. You can enter even the same numbers and equation, to start with.
- Now, manually prepare base for your data table. Enter the following formulas:
The range O3:AI23 will be filled later with the help of VBA macro. For now, just format the table range to your liking, e.g. as shown in this worksheet fragment:
- Next, go to VBE by pressing ALT+F11 shortcut on your keyboard, then - in View menu - select Project Explorer and double-click on Sheet1 (or whatever name of your worksheet). Copy the following VBA macro code and function code (which is used by the macro) and Paste it in the open window :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MinX = Range("A9").Value
MaxX = Range("B9").Value
nPoints = Range("E9").Value 'Default is 20, but can be changed
For Each P In Target
If P.Row = 5 And P.Column = 2 Then
If Range("B5").Value <> "" Then
aSt = Range("B5").Text
bSt = ChngStr(aSt, "x", "$N3")
aSt = ChngStr(bSt, "y", "O$2")
Range("O3").Formula = "=" & aSt
Range("O3").AutoFill Destination:=Range(Cells(3, 15), _
Cells(3 + nPoints, 15)), Type:=xlFillDefault
Range(Cells(3, 15), Cells(3 + nPoints, 15)).AutoFill _
Destination:=Range(Cells(3, 15), Cells(3 + nPoints, 15 + nPoints)), _
Type:=xlFillDefault
Else
Range(Cells(3, 15), Cells(3 + nPoints, 15 + nPoints)).Formula = ""
End If
Exit For
End If
Next P
End Sub
Function ChngStr(aStr, bStr, wStr) As String
dStr = ""
i = Len(aStr)
j = Len(bStr)
For k = 1 To i
If Mid(aStr, k, j) = bStr Then
dStr = dStr + wStr
k = k + j - 1
Else
dStr = dStr + Mid(aStr, k, 1)
End If
Next k
ChngStr = dStr
End Function
That procedure will be used to fill data table for your chart with formulas, when you enter correctly your equation into cell B5. Here is a fragment of the data table with the formulas shown as already entered by the macro:
- Return to your worksheet now by pressing AT+F11 again. Enter your equation into cell B5. The data table should get filled immediately with numbers. Next, select the range O3:AI23 and go to Insert>Charts>Surface charts and select 3D Surface chart. Your chart appears instantly. Move it where you want it to display and format to your liking.
- There are plenty of options/choices available. Now you can try to get some preferable views of your chart, including e.g. a wireframe surface chart.
Whatever nonlinear equation you want to plot, just enter it (remember, no equal sign) into cell B5. You can also enter or change the Min and Max values of X & Y in A9:D9 range. That's essentially it. Save your work. The values in the table and the chart will get updated automatically for you after entering any new equation into cell B5.
The following charts, I've created with my template, will give you some idea about what you can do using the template and your creativity.
No comments:
Post a Comment
All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.