Работа в эксель с формулами таблицы: Самые полезные формулы Excel — Раздел 5. Урок 1 — Онлайн-курс «Digital-аналитика» — This is Data

Практическая работа по теме «Работа с электронными таблицами Excel»

Практическая работа.

Тема:
Работа с электронными таблицами Excel.

Цель: освоить основные принципы работы с электронными
таблицами.

Задачи: 

­ Изучить основные понятия.

­ Создать электронную таблицу в
Microsoft Excel.

­ С помощью встроенного
функционала создать расчетную таблицу, применяя математические формулы,
автовычисление. 

­ Научиться выполнять в Microsoft
Excel построение диаграмм и графиков.

Теоретический материал.

Основные понятия.

Электронные
таблицы предназначены для хранения и обработки информации, представленной в
табличной форме.

Обработка включает в себя:

1)                    
Проведение различных вычислений с использованием мощного аппарата
функций и формул;

2)                    
Исследование влияния различных факторов на данные;

3)                    
Решение задач оптимизации;

4)                    
Получение        выборки     данных,      удовлетворяющих       определенным

критериям;

5)                    
Построение графиков и диаграмм; 6)
    Статистический анализ данных.

Таким образом,
применение электронных таблиц имеет достаточно обширный спектр.

В таблицы можно
вводить любую информацию: текст, числа, даты и время, формулы, рисунки,
диаграммы, графики. Вся вводимая информация может быть обработана при помощи
специальных функций. 

При запуске
программы Excel появляется рабочая книга, которая представляет собой файл,
используемый для обработки и хранения данных. Каждая книга может состоять из
нескольких листов, поэтому в одном файле можно поместить разнообразные сведения
и установить между ними необходимые связи. Имя листу можно задать, выбрав команду
Переименовать контекстного меню, вызываемого правой кнопкой мыши на
надписи: Лист 1.

Листы
можно добавлять или удалять, используя контекстное меню или через главное       меню:
        Вставка/Лист     и        Правка/Удалить лист
соответственно.

Окно Excel – это обычное
окно приложения Windows. Две первые строки окна содержат элементы, общие для
всех окон приложений Windows. В третьей и ниже размещены панели инструментов,
но они могут и отсутствовать. Еще ниже – строка формул. В ней мы видим вводимые
в таблицу величины, содержащиеся в текущей ячейке формулы.

В левой части
строки формул – поле имен, в нем указывается имя и адрес текущей (активной в
данный момент) ячейки или диапазона. Справа от поля в той же строке расположены
три кнопки: первая играет роль клавиши Esc, вторая – роль Enter, третья служит
для вызова функций при вводе формул в таблицу.

Последняя строка – строка
состояния.

Рабочее поле
редактора Excel представляет собой таблицу. Каждая ячейка этой таблицы имеет
свой адрес, который определяется латинскими буквами по горизонтали и арабскими
цифрами по вертикали. Например: А4, B12, AC23 и т.д. В ссылке на ячейку сначала
указывают имя столбца, затем номер строки. Всего ЭТ может содержать до 256
столбцов, пронумерованных от А до IV. Строки последовательно нумеруются
цифрами, от 1 до 65 536 (максимально допустимый номер строки). При формировании
формул используются именно ссылки на ячейки, которые содержат необходимые
данные. В ячейках размещают текст, числа, формулы.

Абсолютные и относительные ссылки

При
копировании формул возникает необходимость управлять изменением адресов ячеек
или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливается
символ $. 

Если в ссылке
используется знак $, то она называется   абсолютной, а адреса таких ссылок
абсолютными. Абсолютные адреса при перемещении формул не изменяются.

Если знак $ не
используется в формуле, то ссылка называется относительной, и ее адрес
называется относительным. При перемещении формул происходит смещение на
величину переноса. 

Например, ссылка $D$5 при
перемещении не изменится.

$D7
– при перемещении формулы с такой ссылкой будут меняться только номера строк.
Ссылка является абсолютной по столбцам и относительной по строкам.

D$7 – при
перемещении формулы изменятся только имена столбцов. Ссылка будет относительной
по столбцам и абсолютной по строкам.

Построение формул

Особенность
электронных таблиц заключается в возможности применения формул для описания
связи между значениями различных ячеек. Расчет по заданным формулам выполняется
автоматически. Изменение содержимого какой-либо ячейки приводит к пересчету
значений всех ячеек, которые с ней связаны формальными отношениями и, тем
самым, к обновлению всей таблицы в соответствии с изменившимися данными. 

Для
ввода формулы в Excel выделите ячейку, где она будет размещена, затем введите
знак = и далее введите числа, функции или ссылки на ячейки, которые будут
содержать данные.        После          завершения          ввода нажмите
ENTER. Например:

Ячейка С3 содержит произведение ячеек А3 и В3. обратите
внимание, что формула видна не только в самой ячейке, но и в специальной строке
формул, где ее всегда удобно исправить или просмотреть.

При выделении любой другой ячейки можно видеть результат
работы формулы:

Изменяя значения в
ячейках А3 и В3 можно увидеть, что Excel производит расчет новых данных
автоматически.

Для построения значений в Excel

существует специальный Мастер функций, в котором
содержатся специальные функции для сложных и более простых расчетов. Вызов
мастера осуществляется одним из следующих способов:

1.      
Командой главного меню: Вставка / Функция.

2.      
При выборе знака = в ячейке с будущей формулой слева от строки
формул автоматически появится выборка десяти последних вызванных формул. Если
ни одна формула не подходит пользователю, необходимо выбрать в списке Другие
функции…, после чего появится Мастер функций.

С      помощью
   функций     производят вычисления          корней, тригонометрических     функций,
   среднего     арифметического,         подсчет элементов по условию и многое
другое.

Формат данных

Данные в Excel
вводятся в определенном формате. В Excel применяются следующие форматы данных:
общий, числовой, денежный, дата, время, процентный, дробный, текстовый,
экспоненциальный, дополнительный (почтовый индекс, номер телефона и др.) По
умолчанию информация выводится в формате Общий. 

Для изменения
формата необходимо выделить ячейку (или группу ячеек), затем выполнить одну из
следующих команд:

1)               
правой кнопкой мыши вызвать контекстное меню, в котором выбрать
команду Формат ячеек для вызова окна Формат ячеек;

2)               
выбрать команду главного меню Формат / Ячейки… Результатом
будет также раскрытие командного окна Формат ячеек.

После открытия
окна Формат ячеек необходимо выбрать в окне Числовые форматы
нужный Вам формат. На рис. 3 приведен пример формата даты. Просто укажите один
из предлагаемых в окне справа форматов и нажмите ОК. В выбранной ячейке будет
применен выбранный вами формат.

Построение диаграмм и графиков

Диаграмма
представляет собой графическое изображение связей между числами      электронной
       таблицы.     Она   позволяет   показать количественное соотношение между
сопоставляемыми  величинами.

Создать диаграмму в Excel можно по шагам с
помощью Мастера диаграмм, вызов которого осуществляется с панели инструментов
соответствующей кнопкой. Сначала Мастер диаграмм предлагает выбрать тип
диаграммы (шаг 1), затем указать диапазон ячеек из соответствующей таблицы (шаг
2), если диапазон с исходными данными заранее не был выделен. Для выделения
несмежных областей с исходными данными следует сначала выделить первую область,
затем нажать клавишу <Ctrl> и, удерживая ее, выделить остальные области.
На последнем шаге (шаг 3) необходимо выбрать место расположения диаграммы: на
новом листе или на текущем.  

Для
редактирования диаграммы выделите ее. Затем вызовите контекстное меню, щелкнув
по любому месту диаграммы. Через команды контекстного меню в построенную
диаграмму можно вносить различные изменения: менять ее тип, изменять текст и
шрифты, добавлять и удалять данные, метки и т. д.      

Расчетные операции в Microsoft Excel. 

Создание и редактирование таблиц. Расчет по формулам.

Задание 1.1. Создание
таблиц в Microsoft Excel.

Составить таблицу по теме «Общий анализ крови», используя
образец. 

Задание
1. 2. Составить таблицу. «Отчет движения медикаментов за текущий
месяц» в табличном процессоре EXCEL. Используя математические формулы,
заполнить данными столбец «РАСХОД».

Методические
рекомендации для выполнения задания 1.2.

1.                     
Сделать текущей ячейку А1. Ввести заголовок таблицы «Отчет
движения медикаментов», нажать на клавиши Alt (левый)+ Enter, набрать на
клавиатуре «за январь 2020 года». Выделить ячейки А1 — А7 (они
становятся черными). Нажать на значок а. Заголовок
располагается по середине 7 столбцов;

2.                     
Сделать текущей ячейку А2. Ввести «№», нажать Alt + Enter,
набрать на клавиатуре «п/п»;

3.                     
Сделать текущей В2. Ввести «Наименование медикаментов»

4.                     
Сделать текущей ячейку С2. 

•          
Ввести «Единицы»

•          
Нажать на комбинацию клавиш Alt + Enter

•          
Ввести «измерения»

5.                     
Сделать текущей ячейку  D2

•          
ввести «Остаток»

•          
нажать на комбинацию клавиш Alt (левый) + Enter  ввести  «на начало»

•          
нажать на комбинацию клавиш Alt (левый) + Enter

•          
ввести «месяца»

6.                     
Сделать текущей ячейку Е2 

•          
ввести «Приход»

7.                     
Сделать текущей ячейку F2

•          
ввести «Расход»

8.                      
Произвести щелчок на ячейку D2

•          
произвести щелчок на ПРАВКА из зоны меню

•          
произвести щелчок на КОПИРОВАТЬ из дополнительного меню

•          
произвести щелчок на ячейку G2

•          
произвести щелчок ПРАВКА из зоны меню

•          
произвести щелчок на ВСТАВИТЬ

•          
щелкнуть мышью на «начало» и удалить это слово

•          
ввести слово «конец»

9.                     
Сделать текущим А3 , ввести «1.»

10.                 
Сделать текущей В3 ввести «Аспирин»

Заполнить таким образом
необходимое количество строк в таблице

11.                 
После заполнения таблицы, делаем расчет столбца «Расход» используя
формулы. Для этого:

•          
сделать текущей ячейку F3

•          
ввести форму для расчета  =D3+E3-G3, нажать Enter,  появится
результат расчета

•          
Введенную формулу с помощью функции автозаполнения
копируем на остальные строчки, следующим образом:

•          
Сделать текущей ячейку  F3

•          
Установить курсор мыши на правый нижний угол ячейки таким
образом, чтобы появился знак +

•          
нажать на кнопку мыши и, не отпуская ее, вытянуть прямоугольную
рамку на все последующие строки, где следует просчитать результат.

•          
кнопку мыши отпустить.

В результате
выполненных операций во всех стоках графы РАСХОД появится результат и
таблица будет полностью заполнена.

12.                 
Выделить заголовок таблицы и отформатировать текст, назначив
размер шрифта 12, вид жирный и подчёркнутый.

Задание 1.3. Рассчитать общую стоимость
стоматологических услуг.

Стоматологическая
поликлиника

Построение
диаграмм и графиков в Microsoft Excel

Задание 2. 1. Используя данные
таблицы из задания 1.2, составить диаграммы следующих разновидностей:

а) гистограмму по всем
медикаментам;

б) круговые диаграммы (разных
видов) по отдельным медикаментам;

Методические рекомендации для выполнения задания по
построению диаграммы:

1)      Построить диаграмму —
гистограмму:

•      выделить
всю таблицу за исключением заголовка;

•      щёлкнуть
на ВСТАВКА;

•      выбрать
ДИАГРАММА;

•      щёлкнуть
на НА ЭТОМ ЛИСТЕ;

•      натянуть
курсором мыши рамку на рабочем листе, тем самым зарезервировав место для
диаграммы;

шаг 1: в появившемся окне
«Мастер диаграмм» выбрать Далее 

шаг 2: определить тип
диаграммы, через щелчок мышью на Гистограмма и нажать Далее;

шаг 3: выбрать вид
гистограммы под № 8 и нажать Далее; шаг 4: в появившемся окне «Мастер
диаграмм» появится образец, нажать  Далее;

шаг 5: в появившемся окне
«Мастер диаграмм» выбрать Добавить легенду? Да и ввести название диаграммы
«Учёт движения медикаментов» и щёлкнуть на Готово.

2)
Построение круговой диаграммы по отдельным медикаментам различных видов:

•      выделить
отдельную строку по какому-либо медикаменту;

•      щёлкнуть
на ВСТАВКА;

•      выбрать
ДИАГРАММА;

•      щёлкнуть
на НА  НОВОМ ЛИСТЕ; шаг 1: в появившемся окне «Мастер диаграмм» выбрать
Далее; шаг 2: в окне «Мастер диаграмм» выбрать тип диаграммы Круговая и
Далее;

шаг 3: в окне «Мастер
диаграмм» выбрать вид круговой диаграммы из предложенных вариантов и Далее;

шаг 4: в окне «Мастер
диаграмм» отметить щелчком Да (Добавить легенду?), ввести название диаграмм,
название медикамента и щёлкнуть на Готово.

Задание 2.2. Составить график измерения
температуры больного за 10 дней, относительно нормальной температуры.

Например:

График изменения температуры больного ФИО

Задание 2.3. Построение
круговой и столбиковой диаграмм.

 Составить
круговую и столбиковые диаграммы составных частей лекарственного
препарата-драже «Ревит», ориентируясь на следующие данные:

Задание 6. Построить
гистрограмму и проанализировать демографические показатели города N(в промилях)
за несколько лет. Показатели строки

«Естественный прирост» вычислите по формулам.

4.

Обучение работе с формулами, функциями и диаграммами в Excel Формулы

Формулы – это выражение, начинающееся
со знака равенства и состоящее из
числовых величин, адресов ячеек, функций,
имен, которые соединены знаками
арифметических операций. К знакам
 арифметических операций, которые
используются в Excel относятся:сложение;
вычитание; умножение; деление; возведение
в степень.

Некоторые операции в формуле имеют
более высокий приоритет и выполняются
в такой последовательности:

 возведение в степень и выражения в
скобках;

 умножение и деление;

 сложение и вычитание.

Результатом выполнения формулы является
значение, которое выводится в ячейке,
а сама формула отображается в строке
формул. Если значения в ячейках, на
которые есть ссылки в формулах, изменяются,
то результат изменится автоматически.

Внесение изменений в формулу
Для
внесения изменений в формулу щелкните
мышью на строке формул или клавишу F2.
Затем внесите изменения и нажмите кнопку
Ввода в строке формул или клавишу Enter.
Если вы хотите внести изменения в формулу
непосредственно в ячейке, где она
записана, то дважды щелкните мышью на
ячейке с этой формулой. Для отмены
изменений нажмите кнопку Отмена в строке
формул или клавишу Esc.
Использование
ссылок

Ссылка однозначно определяет ячейку
или группу ячеек рабочего листа. С
помощью ссылок можно использовать в
формуле данные, находящиеся в различных
местах рабочего листа, а также значение
одной и той же ячейки в нескольких
формулах. Можно также ссылаться на
ячейки, находящиеся на других листах
рабочей книги, в другой рабочей книге,
или даже на данные другого приложения.
Ссылки на ячейки других рабочих книг
называются внешними. Ссылки на данные
в других приложениях называются
удаленными.

Перемещение и копирование формул
После
того как формула введена в ячейку, вы
можете ее перенести, скопировать или
распространить на блок ячеек. При
перемещении формулы в новое место
таблицы ссылки в формуле не изменяются,
а ячейка, где раньше была формула,
становится свободной. При копировании
формула перемещается в другое место
таблицы, при этом абсолютные ссылки не
изменяются, а относительные ссылки
изменяются.
При копировании
формул можно управлять изменением
адресов ячеек или ссылок. Если перед
всеми атрибутами адреса ячейки поставить
символ “$” (например, $A$1), то это будет
абсолютная ссылка, которая при копировании
формулы не изменится. Изменятся только
те атрибуты адреса ячейки, перед которыми
не стоит символ “$”, т.е. относительные
ссылки. Для быстрой установки символов
“$” в ссылке ее необходимо выделить в
формуле и нажать клавишу F4.
Для
перемещения формулы подведите указатель
мыши к тому месту границы ячейки, где
изображение указателя мыши изменяется
с белого крестика на белую стрелку.
Затем нажмите левую кнопку мыши и,
удерживая ее, перемещайте ячейку в
нужное место таблицы. Завершив перемещение,
отпустите кнопку мыши. Если в записи
формулы есть адреса ячеек, они при
перемещении формулы не изменяются.
Для
копирования формулы подведите указатель
мыши к тому месту границы ячейки или
блока, где изображение указателя
изменяется с белого крестика на белую
стрелку. Затем нажмите клавишу Ctrl и
левую кнопку мыши и перемещайте ячейку
в нужное место таблицы. Для завершения
копирования отпустите кнопку мыши и
клавишу Ctrl. Если в записи формулы есть
относительные адреса ячеек, при
копировании формулы они
изменятся.
Распространение
формул
Помимо копирования и перемещения
формулу можно распространить на часть
строки или столбца. При этом происходит
изменение относительных ссылок. Для
распространения формулы необходимо
выполнить следующие действия:
1. 
Установите курсор в ячейку с формулой.
2. 
Подведите указатель мыши к маркеру
заполнения. Изображение указателя
изменяется на черный крестик.
3. 
Нажмите левую кнопку мыши и, удерживая
ее нажатой, перемещайте курсор до нужного
места. Для завершения распространения
формулы отпустите кнопку.

Необходимо отметить, что Excel выводит в
ячейку значение ошибки, когда формула
для этой ячейки не может быть правильно
вычислена. Если формула содержит ссылку
на ячейку, которая содержит значение
ошибки, то эта формула также будет
выводить значение ошибки.

Как сделать электронную таблицу молниеносной

Иногда бывают такие дни

Мы приходим в офис утром с кофе в руке, но мы медлительны. Нам кажется, что мир движется в 10 раз медленнее!

У Excel тоже бывают вялые дни (слишком часто, если вы спросите меня), и когда это случается, ваш день может пойти под откос. Однако есть вещи, которые вы можете сделать, чтобы свести к минимуму эти проблемы с производительностью, и я покажу вам, как!

Фантомные данные

Первое, что вам нужно сделать, чтобы оптимизировать производительность вашего листа, это проверить, есть ли у вас данные в ячейках, которые вы считаете пустыми. Дело в том, что иногда, когда вы заняты этим сверхважным анализом, вы непреднамеренно форматируете ячейки, которые не собирались делать (например, выделяете всю строку), или Excel считывает данные в ячейках, которые раньше содержали данные. Я называю это «фантомными данными»…

Эти фантомные данные могут увеличить размер вашего файла до большего, чем он должен быть. Вы должны избавиться от этих данных:

• Найдите последнюю строку данных
• Выделите строку под последней строкой данных
• Нажмите CTRL + SHIFT + Стрелка вниз
• Нажмите CTRL + – (минус)
• Найдите последний столбец данных
• Выделить столбец после последнего столбца данных
• Нажмите CTRL + SHIFT + Стрелка вправо
• Нажмите CTRL + – (минус)

Перестаньте соединять свои формулы в цепочку

Все мы знаем, что происходит, когда вы ставите костяшки домино друг перед другом и переворачиваете первую. Происходит гирляндная цепная реакция, когда одна за другой падают остальные костяшки домино.

Чем больше костей домино, тем больше времени требуется для завершения последовательной цепочки.

Когда вы соединяете формулы Excel вместе, это может привести к такому же медленному прогрессу в электронной таблице Excel. Гирляндные формулы — это когда результат одной влияет на вычисление в другой ячейке, которая влияет на вычисление другой, еще одной и так далее. Чем больше данных вы работаете, тем больше влияние на вашу электронную таблицу и тем медленнее она становится.

Помните об этом, когда в следующий раз будете настраивать электронную таблицу. Если вы считаете, что вам необходимо объединить формулы в цепочку, рассмотрите возможность использования сводной таблицы для расчета необходимых значений и использования функции ПОЛУЧИТЬ СВОД, чтобы получить доступ к этим значениям.

Избегайте использования энергозависимых функций

Слово «изменчивые функции» вызывает в воображении образ песочных часов, вращающихся в небытие.

Excel имеет два типа функций, и большинство из них относится к категории энергонезависимых. Эти функции пересчитываются только тогда, когда изменяется ячейка, которая влияет на эту функцию или на которую она влияет. Эффективность до мозга костей…

Однако, когда вы используете изменчивую функцию, такую ​​как TODAY(), Excel будет пересчитывать и обновлять результат этой функции независимо от того, какие ячейки вы изменяете на своем листе. Как вы понимаете, это потенциально может вызвать у вас огромные проблемы. Что еще хуже, многие действия заставляют Excel обновить расчет этих функций:

• Удаление/вставка строк или столбцов.
• Фильтрация/дефильтрация.
• Добавление, редактирование или удаление определенного имени.
• Переименование листа.
• Изменение положения листа по отношению к другим листам.
• Скрытие или отображение строк

Вы почти ничего не можете сделать, чтобы избежать этого (кроме отключения автоматических вычислений).

Итак, каковы примеры изменчивых функций Excel? Вот список:

• RAND()
• СЕЙЧАС()
• СЕГОДНЯ()
• OFFSET()
• CELL()
• INDIRECT()
• INFO() 9 0016
• СЛУЧМЕЖДУ()

Если вы работаете с относительно небольшим набором данных, это не такая большая проблема. Если вы работаете с исторически медленным листом и используете одну из функций из приведенного выше списка, рассмотрите возможность использования альтернативных функций для выполнения работы.

Например: вместо использования TODAY() рассмотрите возможность использования ярлыка Excel для ввода статической даты и времени:

Ctrl + ;

Рассмотрите возможность обновления Excel до 64-разрядной версии

В настоящее время Excel (версии после 2010 г.) поставляется в двух разных версиях; 32-битные и 64-битные. Одно из основных различий между ними заключается в том, что 64-разрядная версия в два раза больше способна обрабатывать данные, чем 32-разрядная версия. Обычно это означает, что ваши электронные таблицы будут загружаться и работать быстрее, особенно если вы привыкли работать с большими файлами данных. По моему опыту, 32-разрядная версия Excel обычно начинает испытывать проблемы с производительностью, когда размер файла превышает 20 МБ. Обновление может значительно повысить производительность.

Однако есть одна загвоздка…

Я хотел бы отметить, что прежде чем вносить какие-либо изменения в свою систему, вы должны сначала выяснить, подходит ли вам это. Вы должны учитывать конфигурацию вашего оборудования, вашу операционную систему и способна ли ваша машина запускать 64-разрядное программное приложение (некоторые из них — нет). Все это кажется более сложным, чем есть на самом деле, но я хочу убедиться, что вы понимаете, что если ваша система не справится с обновлением, вы можете нанести больше вреда, чем вы думали.

Как узнать, какую версию Excel вы используете?

1. Откройте Excel
2.  Выберите «Файл» в верхнем левом углу, а затем выберите «Учетная запись» или «Справка» в списке слева.
3. Нажмите «О программе Excel»
4. Здесь вы сможете увидеть, какую версию Excel вы используете.

Если вы используете 32-разрядную версию и хотите обновить ее, обязательно изучите ее. Я бы начал с прочтения этой статьи, в которой подробно обсуждаются преимущества и недостатки обновления.

Старайтесь избегать формул массива

Формулы массива могут быть очень мощными, но чрезмерное их использование имеет свои недостатки. Excel с трудом вычисляет формулы больших массивов или слишком много формул массивов одновременно.

В двух словах, формулы массива могут оценивать и применять формулу к каждой отдельной ячейке в диапазоне и давать несколько результатов. Поскольку формула массива иногда должна вычислять формулу для каждой ячейки отдельно, слишком частое использование этих мощных функций может замедлить работу вашего листа.

Рассмотрите возможность использования нескольких вспомогательных столбцов или отдельного листа для повторения шагов, выполняемых с формулой массива, с использованием стандартных формул Excel для получения того же результата. Сработает ли это, будет зависеть от вашего уникального случая, поэтому вам нужно действительно использовать свои творческие способности и навыки решения проблем.

Узнайте больше о формулах массива

Используйте таблицы Excel

В ваших книгах есть большие списки данных? Преобразуйте их в таблицы Excel и наблюдайте, как на вашем листе гораздо проще выполнять вычисления. Преобразование ваших данных в таблицу значительно упрощает обращение к этим данным в формуле для вас и Excel (и гораздо более интуитивно понятно).

Лучший способ обеспечить полную оптимизацию вашего листа — правильно настроить его с самого начала. Тщательное планирование того, как ваши данные будут загружаться в таблицу и как они будут генерировать результат, имеет решающее значение для обеспечения полной оптимизации вашей таблицы для повышения производительности.

Итак, в следующий раз, когда вы будете сидеть и смотреть на этот экран…

И вам захочется нанести удар по вашему компьютеру, просто запомните эту тактику, и у вас должна быть более производительная электронная таблица.

Вам также может понравиться…

Табличные формулы

В этом руководстве для анализа данных используются электронные таблицы Google. Другие программы для работы с электронными таблицами работают аналогичным образом — поэкспериментируйте и посмотрите, чем они отличаются.

Здесь есть примеры данных для этого руководства:

Теперь, когда вы понимаете, как работают формулы электронных таблиц, вот краткое введение в некоторые из наиболее распространенных символов формул, с которыми вы, вероятно, столкнетесь.

Символы

Это все «базовые математические функции» — такие вещи вы найдете на простом калькуляторе.

• = : сообщает электронной таблице, что вы пишете формулу. Это первое, что должно быть в ячейке с формулой. (ПРИМЕЧАНИЕ. Электронная таблица предполагает, что все , начинающиеся с «=», являются формулами… поэтому будьте осторожны при ее использовании!)
• + : Добавить
• - : Вычесть
• * : Умножить (на калькуляторе это будет «x»)
• / : Разделить (на калькуляторе это будет «÷»)

Совет: располагайте символы в правильном порядке

Следует помнить, что применяются основные математические правила порядка функций. Например, формула =3+5*2 даст вам 13 , а НЕ 16. Если вы не знаете почему или не можете вспомнить правила, ознакомьтесь с этим вводным введением. Если вы хотите изменить порядок функций, вам понадобятся круглые скобки: формулы внутри круглых скобок будут оцениваться перед любой другой формулой.
Если вы хотите, чтобы приведенная выше формула дала 16, вам нужно ввести: =(3+5)*2

Попробуйте использовать эти формулы в «игровом листе» вашей электронной таблицы, пока не почувствуете себя комфортно. Вы должны обнаружить, что они работают почти так, как вы ожидаете.

Что делать, если вы хотите добавить больше номеров? Вы всегда можете добавить их вручную, используя +, или вы можете использовать формулу SUM для суммирования всех значений в заданном диапазоне. Давайте попробуем подсчитать, сколько яблок, слив и всего фруктов мы продали за неделю: Перейдите в ячейку B7 и введите 9.0154 =СУММ(A2:A6) это добавит количество яблок.

Пошаговое руководство: использование электронных таблиц для сложения значений.

Подсчитаем общее количество проданных фруктов.

1. Получите данные примера и создайте копию.
2. Для начала перейдите к первому ряду.
3. Каждая формула в электронной таблице начинается с =
4. Введите = и выберите первую ячейку, которую хотите добавить. Заметили, как в формуле появляется ссылка на ячейку?
5. теперь введите + и выберите вторую ячейку, которую хотите добавить
6. Нажмите Введите или вкладку .
7. Формула исчезает и заменяется значением.
8. Попробуйте изменить число в одной из исходных ячеек (яблоки или сливы), вы должны увидеть значение в общем обновлении автоматически.
9. Вы можете вводить каждую формулу по отдельности, но также можно вырезать и вставлять или перетаскивать формулы через диапазон ячеек.
10. Скопируйте только что написанную формулу (используя ctrl + c ) и вставьте ее в ячейку ниже (используя ctrl + v ), вы получите сумму двух чисел в строке ниже.
11. Либо щелкните правый нижний угол ячейки (синий квадрат) и перетащите формулу в нижнюю часть столбца. Посмотрите обновление столбца «всего» . Ощущение магии!

Пошаговое руководство: использование электронной таблицы для вычитания значений.

Взгляните на приведенный выше пример — он точно такой же, за исключением того, что вы используете — вместо + . Легкий! Найдите разницу между двумя столбцами в приведенном выше примере.

Прохождение: умножение и деление

Резюме по математике: Если у вас есть процент и связанное с ним значение, вы можете вычислить значение процента. экв. скажем, 25% людей в городе с населением 1000 человек моложе 15 лет. Вы можете рассчитать количество жителей, используя следующую формулу:

 количество жителей в городе x (25 ÷ 100)
 

В электронной таблице приведенный выше ответ выражается просто: = 25 * 1000/100 (более подробное объяснение процентов см. на BBC Skillswise )

Теперь более сложный пример:

Вернемся к нашему набору данных Всемирного банка. Если вы не прошли предыдущие уроки или потеряли файл: загрузите его здесь.

1. В исходных данных у нас есть три столбца, относящиеся к расходам на здравоохранение; «расходы на здравоохранение (частные)», «расходы на здравоохранение (государственные)» и «расходы на здравоохранение (всего)». Поэтому нам нужно добавить три новых столбца справа от электронной таблицы, чтобы выполнить ваши расчеты. Дайте каждому заголовок; возможно, «расходы на здравоохранение (частные) в долларах США» и т. д.
2. В исходных данных государственные, частные и общие расходы на здравоохранение выражены в процентах от ВВП. ВВП уже указан в долларах. Вычислить расходы в долларах США из этих двух фактов — это всего лишь один шаг. Мы хотим рассчитать, сколько денег (в долларах США) тратится на здравоохранение в каждой стране и в год.
3. Давайте начнем с самой первой полной строки (Примечание: найдите пробел! У нас нет данных по ВВП Афганистана в 2000 году.
   Просто имейте это в виду (более подробно о пробелах в данных мы поговорим позже). Первая полная строка — Афганистан в 2001 г.

4. В 2001 году ВВП Афганистана составлял 2 461 666 315 долларов США. Их частные расходы на здравоохранение составили 6,009337744% от этой суммы. Итак, расчет, который вам нужно сделать, это 2461666315 * 6,009337744 / 100

5. С формулой электронной таблицы нам не нужно беспокоиться обо всех числах — вам просто нужно ввести ячейки. Итак, вам понадобится следующая формула: =E3*h4/100 (где ячейка E3 содержит ВВП Афганистана в 2001 г., а ячейка h4 содержит данные о частных расходах на здравоохранение в Афганистане в 2001 г.).
6. Перетащите эту формулу вниз по столбцу и вуаля! Вы должны рассчитать частные расходы на здравоохранение в долларах США для каждой страны за последние 10 лет. Гораздо быстрее, чем делать все суммы самостоятельно!

Пошаговое руководство: копирование формул сбоку

Точно так же, как мы могли бы перетащить формулу вниз по столбцу, а электронная таблица распознала шаблон и выбрала правильные ячейки, мы также можем перетащить формулу сбоку в новые столбцы (расходы на здравоохранение в долларах США и общие расходы на здравоохранение в долларах США). . Итак, если мы хотим НО, нам нужно сделать одну небольшую корректировку.

Все еще используя данные Всемирного банка, попробуйте просто перетащить формулу ячейки. Вы видите проблему? Электронная таблица автоматически перемещает 90 156 всех 90 157 ячеек, если смотреть на один столбец вправо. Итак, если раньше у нас было: =E3*h4/100 , то теперь мы имеем =F3*I3/100 … но ВВП по-прежнему находится в столбце E, так что эта формула не та, которая нам нужна.

Чтобы «исправить» столбец или строку, все, что вам нужно сделать, это добавить «$» перед разделом, который вы хотите исправить. Итак, если вы адаптируете исходную формулу к… =$E3*h4/100 …вы без проблем сможете перетащить его вправо.

Совет: Поначалу может быть немного сложно привыкнуть к команде $ . Если вы впервые сталкиваетесь с этим, мы предлагаем вам потратить некоторое время на то, чтобы поиграть и посмотреть, на что он способен. Вернитесь к своей «игровой» таблице, придумайте числа и поэкспериментируйте! Попробуйте, например, =$B2*C2 vs =B$2*C2 , перетащите его и посмотрите, какая разница. Лучший способ освоиться с формулами — это использовать их!

Итак, теперь с помощью одной простой формулы вы можете рассчитать фактические расходы на государственное, частное и государственное+частное здравоохранение в каждой стране за последние десять лет. Электронные таблицы — довольно мощная штука.

Пошаговое руководство: минимальные и максимальные значения

Одна вещь, которая нам очень интересна при работе с данными, — это максимальные и минимальные значения каждого из имеющихся у нас столбцов. Это поможет нам понять, близки ли значения друг к другу или далеко друг от друга. Давай сделаем это!

1. Открыть новый лист. Сделайте это, нажав «+» в левом нижнем углу.
2. Оставьте первый столбец в первой строке пустым, во втором столбце введите = , чтобы сообщить электронной таблице, что вы будете использовать формулу.
3. Вернитесь к листу с набором данных Всемирного банка.
4. Выберите первый столбец с числовыми данными на листе, где хранятся ваши данные.
5. Нажмите Enter, и вы увидите имя на первом листе: magic.
   Почему мы делаем это именно так, а не просто копируем и вставляем? Это автоматически изменит заголовки, если вы измените свои заголовки (например, вы переместите столбцы или переименуете вещи).
6. Теперь первый столбец будет тем, что вы вычисляете: введите Минимум во второй строке первого столбца (A2) для минимального значения.
7. В соседней ячейке введите =MIN( (MIN — это формула минимума)
8. Вернитесь на другой лист, чтобы выбрать первый столбец с числовыми данными — чтобы выбрать весь столбец, нажмите на серую область с буквой столбца.
9. Закройте скобки, набрав ) .
10. Теперь вы должны увидеть минимальное значение в этом поле.
11. Теперь сделайте то же самое для Максимума в третьей строке. Как только вы закончите, просто отметьте три значения во второй строке (формула для максимума: =max() )
12. Видите синий квадрат в правом нижнем углу? Возьмите его и потяните его правильно. Отпустите его, и если у вас все еще нет всех столбцов, продолжайте, пока не получите все значения.
13. Таким образом вы создали таблицу с минимумами и максимумами каждого из столбцов.

Прохождение: работа с пустыми ячейками

Вы заметили, что некоторые из минимальных значений равны 0? Вы действительно верите, что есть страны, которые не тратят деньги на здравоохранение? Их нет (наверное). Нули потому что есть пустые ячейки. Правильная обработка отсутствующих значений — важный шаг в очистке и анализе данных — большие наборы данных почти никогда не бывают полными, и вам нужно найти стратегию для работы с отсутствующими частями.

В этом пошаговом руководстве мы создадим сложную формулу. Мы будем делать это с помощью итеративного процесса — это означает, что одна маленькая формула за раз.
Если вы будете следовать за нами, вы заметите, что можете создавать довольно сложные формулы и результаты просто шаг за шагом.

1. Чтобы справиться с пустыми ячейками, мы должны исправить части наших расчетных формул в таблице данных Всемирного банка

.

2. Для начала создайте фиктивную электронную таблицу, чтобы поиграть с данными. Скопируйте в него первые несколько строк набора данных Всемирного банка, чтобы вы могли начать. Чтобы проверить наши формулы, попробуйте удалить значения в некоторых строках.
3. У нас есть отсутствующая проблема прямо в первом значении: ВВП Афганистана отсутствует за 2000 год.
4. Подумайте о нашей цели. Чего мы хотим добиться: если любое из значений, которые мы умножаем (в данном случае ВВП или расходы на здравоохранение), равно , а не число (вероятно, потому что значение отсутствует), мы не хотим отображать итог.
5. Иными словами: только если присутствует значение как для ВВП, так и для расходов на здравоохранение, если электронная таблица выполняет расчет; , иначе , ячейка должна оставаться пустой.
6. Формула для выражения этого состояния — «ЕСЛИ». (Обзор подобных формул можно найти в справке Google doc. )
7. Формула предлагает нам заполнить три элемента: (1) условие, (2) значение, если условие истинно, (3) значение, если условие ложно.
   
=ЕСЛИ(Условие, Значение, если условие истинно, Значение, если условие ложно)

8. В нашем случае мы знаем части (2) и (3). (2) это формула, которую мы использовали выше, это расчет, который мы хотим выполнить, если оба значения присутствуют в электронной таблице.
   
=ЕСЛИ(Условие, $E3*h4/100, Значение, если условие ложно)

9. (3) — это пробел — если цифр нет, мы не хотим ничего отображать, поэтому мы вообще ничего не заполняем это значение.
   
=ЕСЛИ(Условие, $E3*h4/100,)

10. Итак, теперь нам просто нужно решить (1), условие.
    
=ЕСЛИ(Условие, $E3*G3/100,)

11. Помните, что мы хотим, чтобы ОБА значения ВВП и расходов на здравоохранение были числовыми. Формула для определения того, является ли ячейка числом: ISNUMBER .
12. Это еще одна из тех маленьких формул, с которыми стоит попробовать поиграть! Если вы введете =ISNUMBER(F2) и F2 будет пустым полем, будет указано ЛОЖЬ . Если есть номер, он скажет TRUE . Удобно не правда ли?
13. Нам нужна формула, которая будет рассчитываться только в том случае, если и ВВП , и расходы на здравоохранение являются фактическими числами.
14. Нам нужно объединить результаты ISNUMBER(ВВП) и ISNUMBER(расходы на здравоохранение) вместе. Для этого используется формула AND. Это просто скажет TRUE , если оба они равны TRUE (т.е. оба числа) или ЛОЖЬ , если один или оба из них равны ЛОЖЬ .
15. Это именно то, что нам нужно. Итак, наше условие будет:
    
И(ЧИСЛО(ВВП),ЧИСЛО(расходы на здравоохранение))


16. или, если использовать наши клетки раньше
    
И(ЧИСЛО($E3),ЧИСЛО(h4))


17. Фу! Итак, теперь мы можем объединить части (1), (2) и (3) выше в одну большую формулу, используя «ЕСЛИ».
    
=ЕСЛИ(Условие, $E2*h3/100,)
=ЕСЛИ(И(ЧИСЛО($E2),ЧИСЛО(h3)),$E2*h3/100,)

18. Попробуйте: введите его в первую строку первого столбца расчета и вставьте во все остальные места.