how to draw 3d in excel

How to create 3D charts and XYZ coordinates in Excel

Excel is a spreadsheet awarding that can render data calculated using 2D charts.The term 2nd graph I hateful the coordinate system x, y. Visualization of spatial data coordinates x, y, z  using a 3D graph does not permit fifty-fifty the latest version (written in 2016).What Excel is presented as a 3D graph is actually merely a small cosmetic changes when the data are not shown on the y-axis  through moniker merely as block certain height, which currently corresponds to the value y. Still, however, this is a graph of x, y  (Really 2nd).

3D graphs could be used another software, only information technology is not like shooting fish in a barrel to link calculations from Excel with some external programme.Oft it is a trouble that I'm non that computer ambassador, then you tin can not install boosted programs.On the other hand, Excel is now standard on every computer and fifty-fifty smartphone.In addition to work with Excel tin handle any boilerplate user.Data visualization in coordinates 10, y, z  can be washed using two elementary formulas.This method works well in any other spreadsheet that plots a graph x, y. Even in the old Excel from 1987.

3D objects

Remember how the 3D draws on a newspaper cube.Draw the foursquare that has all sides equal.Then nosotros draw angled lines directed at an bending of 45 ° at the top right.Their size is not equal to the side of the square, but slightly smaller.Nosotros connect the dots loose ends and we got an oblique cube in 3D view.

What you lot describe is non a 3D object.It's just a set of lines in a plane substantially only an optical illusion, which reminds us of the 3D cube.To make this a credible illusion, nosotros must follow when drawing according to certain rules.Those we explained in the previous paragraph.To explain this estimator, we need rules to describe mathematically.Display (project) of 3D objects on a 2nd plane describes axonometry.

Axonometry - projection onto the aeroplane of the screen

Consider a 3D right-handed coordinate organization.In it are placed in 3D infinite points.Each bespeak in the space of coordinates [x,y,z ].The 3D space, however, we want to brandish a 2D computer screen.Like the 3D object casts a shadow on a 2D plane.The image below is a 2D aeroplane of the project on the screen representation of the coordinates ten 'and y'. Us focus on a betoken P  which is reflected from the 3D space to 2D plane x ', y'. Cut down its project of a 2D 10-axis'  a particular value of p 1  nosotros know that y'all calculate using the cosine bending.  Similarly, we know calculate the other values P ane,P 2,Q i,Q ii,r 2d These numerical values ​​are called coefficients of projection.This could exist likened to the fact that when we change the angle of incidence of light and changes the method of projection onto the plane, changing the shape of the shadow.

The transformation equation coefficients and projection

Therefore necessary to specify points in 3D space to the 2d plane of the screen.To serve the general perspective project transformation equation:

The  axis is identical with the axis y,  so that the ratio r ane fail considering r 1 = 0th Coefficients projection need non exist calculated manually, because in practice, the most ordinarily used display already calculated and expressed as the nautical chart values:

view type	p 1	p 2	q 1	q two	r 2
angled left	-0.35	-0.35	1	0	1
footprint	-one	0	0	-1	0
draft	-one	0	0	0	1
summit	0	0	1	0	1
angled right	-1	0	0.35	-0.35	1
General ax.	-0.vii	-0.35	0.8	-0.two	0.eight

Nosotros cull, for example, the well-nigh widely used type of display called left oblique.Dosadímekonkrétne coefficients in transformation equations.Some expressions in parentheses are multiplied past zero or units, so nosotros become the simplified form:

How to explain to figurer

For instance, we desire to draw dimensional wireframe cube size pages 3  and we desire to view it in an oblique view.We outset by describe all vertices by coordinates [x, y, z].

For analogy, I called the coordinates of each color.We create three columns in which they will coordinate points x, y, z  describing the position of a bespeak in 3D space.So, we create two more than columns that will be represented in 2D projection of x 'and y'. The first line of columns x 'and y'  using the fill patterns for the simplified transformation equations for the ten, y,  populated with the values of the  cells of the start three columns.  Coated with the formula to be converted to the full range.At present you lot just portray the calculated values of the columns x 'and y'  using 2d 10, y  graph.Renders the projection of 3D points to a second plane of the screen.The reward of this solution is that if yous change something in 3D coordinates, using the transformation equations are converted second project that is automatically displayed in real time.For meliorate understanding you tin download directly sample file kocka.xls .

For a perfect display it needs to have both axis of the graph to scale and that the square area of ​​the graph.  Otherwise the epitome volition be flattened.Centrality plot the ten 'and y'  do not need to see and so they can exist later removed from the nautical chart, or hibernate.To describe a line must enter the start and end points.We skip the line and list the points of the other line segments.After selecting the line graph, the segment that volition create a project cubes. Image below is a preview of the file sample kocka-ciary.xls .

Block in 3D infinite

In another example, we draw the block size with the sides a, b, c. The procedure is similar, simply practice not enter the coordinates into the columns of 10, y, z  by hand, only the size ratio of the parallelepiped refer to the shaded cells, which are numerical values of a, b, c. The values of a, b and c  can vary, but for convenience I added a punch.  Click the arrow upward or downwardly to increase or subtract the value in the cell above the dial.These values ​​are populated with up to 3D coordinates, where after transformation into a second projection immediately displayed as rendered block.

For Spice up I added even calculate that diverts the vertical edges of the block certain angle. One internal angle cake so it is not genuine, but slightly smaller.This will provide monoclinic prism.With the cosine of an angle  of deflection, and the calculated new coordinates 10, y, z  and deployment of new peaks in the area of the prism. You tin can download the sample file kvader.xls , changing values and track changes.

For a more detailed description of a tutorial can picket a video in which the step by step added to the procedure illustrated input of coordinates, the downstream projection of the plane x ', y', and the finish coordinate calculation according to the ratio of the variable block sizes.

The cylindrical helix

In the following example, I added another column called t. Basically, it's the angle in radians, which is populated with a parametric equation in a cylindrical helix.Parametric equations determine the coordinates ten, y, z  according to the parameter t. It is a movement along a circular path, and with each revolution (one revolution is 6.28 radians) increases the value of  the coordinates to  a abiding, which is called pitch.Constants tin rewrite and apply absolute addressing, this constant populated with in each adding.Thus, we obtain a spatial curve that resembles a spiral.

3D spatial coordinates of the helix needs to be transformed into a 2d projection.  Now, however, I use a full general transformation equations, with coefficients screening using absolute addressing populated with the yellowish fields in the calculations x 'y'. Primetania coefficients P i,P 2,Q 1,Q 2,R 2  can be rewrite, changing the type of projection and thus display type.The fastest way is to re-create the relevant line coefficients from the table directly to the yellowish cells in Excel, which tin be changed views.Likewise, a alter in the 3D coordinates x, y, z  in existent time converted to 2nd and instantly displayed in the graph every bit projected. Coefficients of projection you can substitute in the masterfile skrutkovica.xls .

Conical helix

In another example, I created a trio of different projections: general axonometric, side  and program. For each type of projection is the corresponding coefficients are transformed into the coordinates x 'and y'. Of the three columns I had to portray three graphs that show exactly these three views.Coefficients of projection in this case are dosadené to fixed formulas.

Spice up the formula I has modified the parameter to be changed at each revolution not but the summit (pitch) merely also the distance from the center.This bend wound is non cylindrical, but conical (cone) surface. The coefficient of taper  using sliders and alter the value using absolute addressing transferred to the calculation, 3D coordinates.Each change of spatial coordinates after transformation in real time is displayed in 3 views.This will provide an object that looks similar a conical jump.If you do not know exactly how to adjust the general transformation equations for different views, you can download the sample Sobor for this case: pruzina.xls and put all the custom values in column x, y, z.

3D rotation

The example on the 3D coordinates are translated into various predefined 2d views using v coefficients projection (taken from the table). In some other case, we will evidence how to make a projection of a view in any arbitrary bending of rotation α, β, γ  about axes 10, y, z  . Now we coefficients projection. considered to be constant, merely their numerical values will be recalculated according to the angles α, β, γ  . Since the axis of in 3D space it is non the same as 10 '  of the 2D space, then neither r1 is not equal to zero, but to another account. And since it is not-aught, we tin can non neglect. Now therefore we consider it to v, but half dozen of transformation coefficients p 1 ,p 2 ,q 1 ,q two ,y one ,y two  , given by the following formulas:

Equally an example, I created a 3D object pyramid With a footprint the size of two and a height of 2, with the center of the base is the aforementioned as the eye of the 3D coordinate organization [ 10  , y  , z  ] as the base, and is divided in half 2/2 = 1st

Steering angles α, β, γ  is populated with formulas to screening coefficients. Excel must be replaced angles in radians. Since the whole circle has 360 degrees  which corresponds to 6.28  radians, so 1 radian is vi.28 / 360 = 0.0174 °  .

Coefficients calculated project using absolute addressing populated with transformation equations in the full general screening. By varying the angles by using the slider therefore it changes the direction of view from which we look at the 3D object. To illustrate zi Achieve sample file: 3D-rotation-ihlan.xls

But it is not rotation in the literal sense, considering 3D coordinates do not change, change simply the angle of project on a second airplane. Rotation of the torso in space depict the transformation matrix for rotation. These, however, in this case we do not need, thus considerably simplifying calculations.

A detailed clarification is in the video below, in which, step by step the process explained by their coordinates, the conversion coefficients of project according to the angle of the projection onto the plane x ', y'.

GPS

This screening method I used in the brandish and GPS coordinates.I left a few hours to enable GPS in one place, so I got effectually xx,000 points in space.The image below is their spatial representation, the tare as cloud points .Information technology is seen that the position determination is always to within a few meters of the centre value.More on the evaluation of this experiment can read a separate article on GPS accuracy .

Other articles:

Test the accuracy of GPS

Calculation of the buildings nether the shade



lauentil1987.blogspot.com

Source: https://www.maclab.sk/clanky/en-3d-graph-excel.php

Share this post