Purpose of a formula Calculation Calculation by formulas is the main purpose of creating a document in a spreadsheet environment. FormulaFormula is the main data processing tool. Formula A formula links the data contained in different cells and allows you to get a new calculated value from this data.
Rules for writing formulas A formula is a mathematical expression written according to the rules established in the spreadsheet environment. The formula may include: - constants (values that do not change during the calculation), - variables, - signs of arithmetic operations ("+", "-", "*", "/"), - brackets, - functions.
Example formula with constant C2=A2+B2+5 ABCDEFG
MATHEMATICAL functions Type of notation Purpose ROOT(...) Calculation of the square root ABS(...) Calculation of the absolute value (modulus) of the number INTEGER(...) Rounding the number or the result of the expression indicated in brackets to the nearest integer PI () The value of the mathematical constant "PI" (3 , …) GCD(…) Greatest Common Divisor of Multiple Numbers RAND() Compute a random number between 0 and 1
Functions DATE AND TIME Record type Appointment TODAY() The value of today's date as a date in numerical format MONTH(date) specified date DAY(date) Calculation of the ordinal number of the day in the month from the specified date YEAR(date) Calculation of the year from the specified date
Logical functions AND(condition1;condition2;…) - calculates the values (TRUE, FALSE) of the logical operation "AND" OR(condition1;condition2;...) - calculates the values (TRUE, FALSE) of the logical operation "OR" IF(condition; value_True; value_false) – calculates values depending on the condition
Reference properties NameRecordWhen copyingInput technology RelativeC3 Changes according to the new position of the cell Click in the cell Absolute$C$3 Does not change Click in the cell, press F4 until the address is converted to the right kind Mixed C$3 Row number does not change $C3 Column number does not change
Rule for copying formulas When copying formulas, the program will automatically change the relative references in accordance with the new position of the calculated cell. The program will leave absolute references unchanged. For a mixed link, only one part (not marked with $) changes.
The lesson is devoted to the analysis of task 9 of the exam in computer science
Topic 9 - "Information coding, volume and transmission of information" - is characterized as tasks of a basic level of complexity, the execution time is approximately 5 minutes, maximum score — 1
Encoding of text information
Graphic information encoding
Let's consider some concepts and formulas necessary for solving the exam in computer science of this topic.
- Pixel is the smallest element of a bitmap that has a specific color.
- Permission is the number of pixels per inch of the image size.
- Color depth is the number of bits required to encode the color of a pixel.
- If the encoding depth is i bits per pixel, each pixel code is selected from 2 i options, so you can use at most 2 i different colors.
- N- number of colors
- i- color depth
- In the RGB color model(red (R), green (G), blue (B)): R (0..255) G (0..255) B (0..255) -> get 2 8 options for each of the three colors.
- R G B: 24 bits = 3 bytes - true color mode(true color)
- I- the amount of memory required to store the image
- M- image width in pixels
- N- image height in pixels
- i- color encoding depth or resolution
- where N is the number of pixels (M * N) and i– color encoding depth (coding bit depth)
- You should also remember the conversion formulas:
Formula for finding the number of colors in the palette used:
Let's find the formula for the amount of memory to store a bitmap:
Or you can write the formula like this:
I = N * i bits
* to indicate the amount of allocated memory, there are different designations ( V or I).
1 MB = 2 20 bytes = 2 23 bits,
1 KB = 2 10 bytes = 2 13 bits
Audio encoding
Let's get acquainted with the concepts and formulas necessary for solving tasks 9 of the Unified State Exam in computer science.
Example: at ƒ=8 kHz, coding depth 16 bit on the countdown and duration of the sound 128 s. would need:
✍ Solution:
I = 8000*16*128 = 16384000 bits
I = 8000*16*128/8 = 2 3 * 1000 * 2 4 * 2 7 / 2 3 = 2 14 / 2 3 = 2 11 =
= 2048000 bytes
Determination of the information transfer rate
- The communication channel always has a limited throughput(information transfer rate), which depends on the properties of the equipment and the communication line (cable) itself
- I- amount of information
- v- bandwidth of the communication channel (measured in bits per second or similar units)
- t- transmission time
The amount of transmitted information I is calculated by the formula:
* Instead of speed designation V sometimes used q
* Instead of indicating the length of the message I sometimes used Q
The data transfer rate is determined by the formula:
and measured in bps
Solving tasks 9 USE in Informatics
USE in Informatics 2017 task 9 FIPI option 1 (Krylov S.S., Churkina T.E.):
What is the minimum amount of memory (in KB) that must be reserved to store any bitmap of size 160 x 160 pixels, provided that the image can be used 256
different colors?
✍ Solution:
- We use the formula for finding volume:
- We calculate each factor in the formula, trying to bring the numbers to powers of two:
- MxN:
Result: 25
Detailed analysis of task 9 of the exam in computer science, we suggest watching in the video:
Subject: Image Encoding:
USE in informatics task 9.2 (source: 9.1 option 11, K. Polyakov):
Figure size 128
on the 256
pixels occupied in memory 24 KB(excluding compression). number of colors in the image palette.
✍ Solution:
- where M*N is the total number of pixels. Let's find this value, using powers of two for convenience:
Number of colors = 2 i
i = I / (M*N)
Result: 64
Watch the video review of the task:
Subject: Image Encoding:
USE in informatics task 9.3 (source: 9.1 option 24, K. Polyakov):
After converting the bitmap 256-color graphic file in 4-color format, its size has decreased by 18 KB. What was the size source file in KB?
✍ Solution:
- According to the formula for the volume of an image file, we have:
- i can be found by knowing the number of colors in the palette:
where N is the total number of pixels,
a i
number of colors = 2 i
Result: 24
Detailed analysis 9 USE assignments look at the video:
Subject: Image Encoding:
USE in informatics task 9.4 (source: 9.1 option 28, K. Polyakov, S. Loginova):
The color image was digitized and saved as a file without the use of data compression. Received file size - 42 MB 2
times less and the color coding depth increased by 4
times more than the original parameters. Data compression was not performed. Specify file size in MB obtained by re-digitization.
✍ Solution:
- According to the formula for the volume of an image file, we have:
- In such tasks, it is necessary to take into account that a decrease in resolution by 2 times implies a decrease in pixels by 2 times separately in width and height. Those. overall N decreases 4 times!
- Let's create a system of equations based on the available information, in which the first equation will correspond to the data before the file conversion, and the second equation - after:
where N
a i
\[ I= \frac (N)(4) * 4* \frac (42)(N) \]
Result: 42
Subject: Image Encoding:
USE in informatics task 9.5 (source: 9.1 option 30, K. Polyakov, S. Loginova):
The image was digitized and saved as a bitmap file. The resulting file was transferred to cities through the communication channel 72 seconds. Then the same image was re-digitized with a resolution of 2
times larger and the color coding depth in 3
times less than the first time. Data compression was not performed. The resulting file has been transferred to city B, throughput of the communication channel with city B c 3
times higher than the communication channel with the city A.
B?
✍ Solution:
- According to the file transfer rate formula, we have:
- According to the formula for the volume of an image file, we have:
- For this task, it is necessary to clarify that the resolution actually has two factors (pixels in width * pixels in height). Therefore, if the resolution is doubled, both numbers will increase, i.e. N will increase in 4 times instead of two.
- Let's change the formula for obtaining the file size for the city B:
- For city A and B, replace the volume values in the formula to get the speed:
- Substitute the speed value from the formula for city A into the formula for city B:
- Express t:
where I is the size of the file, and t- time
where N is the total number of pixels or resolution,
a i- color depth (number of bits allocated per 1 pixel)
\[ I= \frac (2*N * i)(3) \]
\[ V= \frac (N*i)(72) \]
\[ 3*V= \frac(\frac (4*N*i)(3))(t) \]
\[ t*3*V= \frac (4*N*i)(3) \]
\[ \frac (t*3*N*i)(72)= \frac (4*N*i)(3) \]
Result: 32
For another solution, see the video tutorial:
Subject: Image Encoding:
USE in informatics task 9.6 (source: 9.1 option 33, K. Polyakov):
The camera takes pictures 1024 x 768 pixels. One frame is stored 900 KB.
Find the maximum number of colors in the image palette.
✍ Solution:
- The number of colors depends on the color encoding depth, which is measured in bits. To store a frame, i.e. total number of pixels allocated 900 KB. Convert to bits:
\[ \frac (225 * 2^(15))(3 * 2^(18)) = \frac (75)(8) \approx 9 \]
9 bits per 1 pixel
Result: 512
Watch the video for a detailed solution:
Subject: Audio Coding:
USE in Informatics 2017 task 9 FIPI option 15 (Krylov S.S., Churkina T.E.):
At the studio with a four-channel ( quad) sound recordings with 32 -bit resolution per 30 seconds, an audio file was recorded. Data compression was not performed. It is known that the file size is 7500 KB.
From what sample rate(in kHz) was it recorded? Enter only a number as an answer, you do not need to specify units of measurement.
✍ Solution:
- According to the formula for the volume of an audio file, we get:
- From the assignment we have:
I = β*t*ƒ*S
\[ ƒ = \frac (I)(S*B*t) = \frac (7500 * 2^(10) * 2^2 bits)(2^7 * 30)Hz = \frac ( 750 * 2^6 )(1000)KHz = 2^4 = 16 \]
2 4 = 16 kHz
Result: 16
For a more detailed analysis, please see video solution for this 9 task of the exam in computer science:
Subject: Image Encoding:
9 task. Demo version of the exam 2018 informatics:
An automatic camera produces bitmaps of size 640
× 480
pixels. In this case, the size of the file with the image cannot exceed 320
KBytes, data packing is not performed.
Which maximum number of colors can be used in the palette?
✍ Solution:
- According to the formula for the volume of an image file, we have:
- Let's see what we have already been given from the formula:
where N is the total number of pixels or resolution, and i- color encoding depth (number of bits allocated per 1 pixel)
number of colors = 2 i
\[ i = \frac (I)(N) = \frac (320 * 2^(13))(75 * 2^(12)) \approx 8.5 bits \]
Result: 256
For a detailed solution of this task 9 from the USE demo version of 2018, see the video:
Subject: Audio Coding:
USE in informatics task 9.9 (source: 9.2 option 36, K. Polyakov):
The musical fragment was digitized and recorded as a file without the use of data compression. The resulting file was transferred to the city BUT via a communication channel. Then the same musical fragment was re-digitized with a resolution of 2 3 times less than the first time. Data compression was not performed. The resulting file was transferred to the city B per 15 seconds; throughput of the communication channel with the city B in 4 times higher than the communication channel with the city BUT.
How many seconds did it take to transfer the file to the city A?
In the answer, write down only an integer, you do not need to write a unit of measure.
✍ Solution:
- To solve, you need a formula for finding the data transfer rate of the formula:
- Recall also the formula for the volume of an audio file:
- We will write out separately, all the data relating to the city B(about BUT almost nothing is known.)
I = β*ƒ*t*s
where:
I- volume
β
- coding depth
ƒ
- sampling frequency
t- time
S- number of channels (if not specified, then mono)
\[ t_A = \frac (15)(2) * 3 * 4 \]
90 secondsResult: 90
For a detailed solution, see the video:
Subject: Audio Coding:
USE in informatics task 9.10 (source: 9.2 option 43, K. Polyakov):
The musical fragment was recorded in stereo format ( two-channel recording), digitized and saved as a file without using data compression. Received file size - 30 MB. Then the same piece of music was re-recorded in the format mono and digitized with a resolution of 2 times higher and a sampling rate of 1,5 times less than the first time. Data compression was not performed.
Specify file size in MB obtained by rewriting. In the answer, write down only an integer, you do not need to write a unit of measure.
✍ Solution:
- Let's write out separately, all the data concerning the first state of the file, then the second state - after the transformation:
I = β * ƒ * t * S
I- volume
β
- coding depth
ƒ
- sampling frequency
t- time
S-number of channels
Result: 20
Watch the video for this task:
Subject: Encoding sound files:
USE in informatics task 9.11 (source: 9.2 option 72, K. Polyakov):
The musical fragment was digitized and recorded as a file without the use of data compression. The resulting file was transferred to cities through the communication channel 100 seconds. Then the same piece of music was re-digitized with a resolution 3 times higher and sample rate 4 times less than the first time. Data compression was not performed. The resulting file has been transferred to city B per 15 seconds.
How many times the speed (channel capacity) to the city B more bandwidth to the city BUT
?
✍ Solution:
- Recall the formula for the volume of an audio file:
- We will write out separately all the data relating to the file transferred to the city BUT, then the converted file transferred to the city B:
I = β * ƒ * t * S
I- volume
β
- coding depth
ƒ
- sampling frequency
t- time
✎ 1 way to solve:
Answer: 5
✎ 2 way to solve:
\[ \frac (V_B)(V_A) = \frac (3/_4 * I)(15) * \frac (100)(I) = \frac (3/_4 * 100)(15) = \frac (15) )(3) = 5 \]
(((3/4) * I) / 15) * (100 / I)= (3/4 * 100) / 15 = 15/3 = 5
Result: 5
Detailed video analysis of the task:
Subject: Audio Coding:
USE in informatics task 9.12 (source: 9.2 option 80, K. Polyakov):
Produced four-channel(quad) audio recording at sample rate 32 kHz and 32 bit resolution. The record lasts 2 minutes, its results are written to a file, data compression is not performed.
Determine the approximate size of the resulting file (in MB).
Give the answer as the nearest integer to the size of the file, multiple of 10.
✍ Solution:
- Recall the formula for the volume of an audio file:
- For simplicity of calculations, we will not take into account the number of channels. Consider what data we have, and which of them need to be converted to other units of measurement:
I = β * ƒ * t * S
I- volume
β
- coding depth
ƒ
- sampling frequency
t- time
S- number of channels
Q = 2 10 * 125 * 2 2 * 15 = = 2 12 * 1875 bits for all characters
Result: 480000
Analysis of 9 tasks:
Subject: Transfer rate:
USE in Informatics task 9.14 (
The calculation of the information volume of a text message (the amount of information contained in the information message) is based on counting the number of characters in this message, including spaces, and on determining the information weight of one character, which depends on the encoding used in the transmission and storage of this message.
Traditional encoding (Windows, ASCII) uses 1 byte (8 bits) to encode one character. This value is the informational weight of one symbol. Such an 8-bit code allows you to encode 256 different characters, because 28 =256.
Currently, the new international Unicode standard has become widespread, which allocates two bytes (16 bits) for each character. With it, you can encode 2 16 = 65536 different characters.
So, to calculate the information volume of a text message, the formula is used
V text = n char *i / k compression, (2)
where V text is the information volume of a text message, measured in bytes, kilobytes, megabytes; n char is the number of characters in the message, i is the information weight of one character, which is measured in bits per character; k compression - data compression ratio, without compression it is equal to 1.
Unicode information is transmitted at 128 characters per second for 32 minutes. What part of the 1.44 MB floppy disk will be occupied by the transferred information?
Given: v = 128 characters/sec; t \u003d 32 minutes \u003d 1920 seconds; i = 16 bits/symbol
Solution:
n chars = v*t = 245760 chars V=n chars *i = 245760*16 = 3932160 bits = 491520 bytes = 480 Kb = 0.469Mb, which is 0.469Mb*100%/1.44Mb = 33% of the floppy size
Answer: 33% of the disk space will be occupied by the transmitted message
Calculation of the information volume of a raster image
The calculation of the information volume of a raster graphic image (the amount of information contained in a graphic image) is based on counting the number of pixels in this image and on determining the color depth (the information weight of one pixel).
So, to calculate the information volume of a raster graphic image, formula (3) is used:
V pic = K * n sym * i / k compression, (3)
where V pic is the information volume of a raster graphic image, measured in bytes, kilobytes, megabytes; K is the number of pixels (dots) in the image, which is determined by the resolution of the information carrier (monitor screen, scanner, printer); i - color depth, which is measured in bits per pixel; k compression - data compression ratio, without compression it is equal to 1.
The color depth is given by the number of bits used to encode the color of a point. The color depth is related to the number of displayed colors by the formula N=2 i , where N is the number of colors in the palette, i is the color depth in bits per pixel.
1) As a result of converting a raster graphic image, the number of colors has decreased from 256 to 16. How will the amount of video memory occupied by the image change?
Given: N 1 = 256 colors; N 2 = 16 colors;
Solution:
We use formulas V 1 = K*i 1 ; N 1 \u003d 2 i 1; V 2 \u003d K * i 2; N 2 \u003d 2 i 2;
N 1 \u003d 256 \u003d 2 8; i 1 = 8 bits/pixel
N 2 \u003d 16 \u003d 2 4; i 2 = 4 bits/pixel
V 1 \u003d K * 8; V 2 \u003d K * 4;
V 2 /V 1 \u003d 4/8 \u003d 1/2
Answer: The size of the graphic will be halved.
2) A color image of standard A4 size (21*29.7 cm) is scanned. The resolution of the scanner is 1200dpi and the color depth is 24 bits. What information volume will the resulting graphic file have?
Given: i = 24 bits per pixel; S = 21cm*29.7cm D = 1200 dpi (dots per inch)
Solution:
We use the formulas V = K*i;
1inch=2.54cm
S = (21/2.54)*(29.7/2.54) = 8.3in*11.7in
K = 1200*8.3*1200*11.7 = 139210118 pixels
V = 139210118*24 = 3341042842bits = 417630355bytes = 407842KB = 398MB
Answer: the size of the scanned graphic image is 398 MB
3.2. Formulas
In formulas, the symbols established by the relevant state standards should be used as symbols. Calculation by formulas is carried out in basic units of measurement, the formulas are written as follows: first, the formula is written in a letter designation, after the equal sign, instead of each letter, its numerical value in the main system of units of measurement is substituted; then an equal sign is put and the final result is written with the unit of measurement . Explanations of the symbols and numerical coefficients included in the formula, if they are not explained earlier in the text, should be given directly below the formula. Explanations of each character should be given on a new line in the order in which the characters are given in the formula. The first line of the explanation must begin with the word "where" without a colon after it. For example,
The density of each sample r, kg / m 3, is calculated by the formula
(1)
where m is the mass of the sample, kg;
V - sample volume, m 3 .
Formulas that follow one after another and are not separated by text are separated by a comma.
Transferring formulas to the next line is allowed only on the signs of the operations being performed, and the sign at the beginning of the next line is repeated. When transferring the formula on the multiplication sign, the sign "x" is used.
The formula is numbered if it is required further in the text. Formulas, with the exception of formulas placed in the appendix, must be numbered consecutively with Arabic numerals, which are written at the formula level on the right in parentheses. Numbering within a section is allowed. In this case, the formula number consists of the section number and the ordinal number of the formula, separated by a dot. For example, formula (3.1).
Formulas placed in applications must be numbered separately, Arabic numeration within each application with the addition of the application designation before each digit. For example, formula (A.1).
The distance between the formula and the text, as well as between the formulas, must be 10 mm.
Entering one letter in the printed formula is not allowed! In this case, the entire formula is written by hand.
3.3. Illustrations and Applications
Illustrative material can be presented in the form of diagrams, graphs, etc. . The illustrations placed in the text and appendices of the explanatory note are called figures.
Illustrations are done in black ink, paste or ink on a separate sheet as close as possible to the reference to it in the text.
Illustrations, with the exception of illustrations of appendices, should be numbered in Arabic numerals within the section, or through numbering. For example, "Figure 1", "Figure 1.1", "Figure 2.1".
An illustration, if necessary, may have a name and explanatory data (figure text). The word "Figure" and the name are placed after the explanatory text without a dot at the end, as in Figure 3.4.1.
All drawings larger than A4 are included in the appendices. Applications are designed as a continuation this document and placed at the end of the explanatory note in the order of reference to them in the text. References should be given to all annexes in the text of the document. Each application should start from a new sheet with the word “Appendix” and its designation indicated at the top in the middle of the page (Figure 3.4.2). For example, "Appendix A". The application should have a title that is written in the middle of the page, symmetrical with respect to the capitalized text. Figures and tables located in the appendix are numbered within the appendix, with the addition of the appendix designation before the number. For example, "Figure A.1".
Applications are denoted by capital letters of the alphabet, starting with A, except for the letters E, Z, Y, O, H, b, s, b. It is allowed to designate the application with letters of the Latin alphabet, with the exception of the letters I and O. Applications are made on sheets of A4, A3, A4X3, A4x4, A2, A1 according to GOST 2.301.
Appendices should share continuous pagination with the rest of the document.
3.4. tables
Tables are used for better clarity and ease of comparison of indicators.
The word "Table", its number and title are placed on the left above the table. The title of the table, if any, should reflect its content, be precise, brief. The name of the table is written with a dash after the word "Table" with a capital letter without a dot at the end. For example:
Table 2.1 - Technical data
The table may contain a head and a side. The head and side of the table should be separated by a line from the rest of the table. Tables on the left, right and bottom, as a rule, are limited by lines. Minimum Height lines 8 mm, the maximum is not regulated.
The column "number in order" is not done. If column numbering is required, the number is written directly on the line. The headings of the columns and rows of the table should be written with a capital letter, and the subheadings of the graph with a lowercase letter if they form one sentence with the heading, or with a capital letter if they have an independent meaning. Do not put dots at the end of headings and subheadings of tables. The headings and subheadings of the columns are indicated in the singular.
To shorten the text of headings and subheadings individual concepts are replaced by the letter symbols established by GOST 2.321, or other symbols, if they are explained in the text, for example, D is the diameter, h is the height.
It is not allowed to separate the headings and subheadings of the sidebar and the graph with diagonal lines. The spacing between rows in table headers can be reduced to one spacing. Horizontal and vertical lines delimiting the rows of the table may not be drawn if their absence does not impede the use of the table.
Column headings, as a rule, are written parallel to the rows of the table. If necessary, a perpendicular arrangement of column headings is allowed.
The table, depending on its size, is placed under the text in which the link to it is first given, or on the next page, and, if necessary, in an annex to the document. It is allowed to place the table along the long side of the document sheet.
If the table is interrupted at the end of the page, its continuation is placed on the next page. In this case, the lower horizontal line is not drawn in the first part of the table. The word "Table" and its number and name are indicated above the first part of the table, the words "Continuation of the table" are written above the other parts, indicating the number of the table. When transferring part of the table to the same or other pages, the name of the table is placed only above the first part of the table.
If the rows or columns of the table go beyond the page format, it is divided into parts, placing one part under the other or next to it, while in each part of the table the head and side are repeated. When dividing a table into parts, it is allowed to replace its head or sidebar with the number of columns and lines, respectively. In this case, the columns and (or) lines of the first part of the table are numbered with Arabic numerals.
All tables, with the exception of annex tables, should be numbered with Arabic numerals through numbering. It is allowed to number the tables within the section. In this case, the table number consists of the section number and the ordinal number of the table, separated by a dot.
The tables of each application are designated by separate numbering in Arabic numerals with the addition of the application designation before the digit, for example, "Table A.1".
All tables of the document should be referenced in the text; when referring, the word "table" with its number is written in full.
If the values of the same physical quantity are placed in the column of the table, i.e. the values have the same dimension, then the designation of the unit of the physical quantity is indicated in the heading (subheading) of this column. For example,
Table 2.4 - Name of the table
If all the values of the quantities in the table have the same dimension, then the designation of the unit of the physical quantity is indicated after the heading of the table. For example,
Table 1 - Attenuation in communication sections, dB
| Plot A - B | Plot B - C | Plot C - D | Plot D-E |
| 18 | 36 | 24 | 15 |
If the names of the lines are repeated, then "the same" is written in the next line, and in the 3rd and 4th quotes >> or -"-. If only part of the phrase is repeated, it can be replaced with the words "same" and the last addition. Such substitution is not allowed in columns. normative documents not allowed. For example,
Table 2.1 - Table name
An empty window in the table is not left, a dash is put. Decimal numbers related to the same indicator must have the same number of digits after the decimal point. The numerical values in the columns of the table must be entered so that the digits of the numbers in the entire column are located one below the other if they refer to the same indicator.