Обработка данных в Excel

На этой странице вы узнаете

• Как игра «Морской бой» связана с Excel?
• Сколько строк и столбцов в таблице Excel?
• Как профильтровать таблицу, а не воду?

Хотите стать настоящими мастерами управления данными, как элита ведущих компаний? Хотите поднять свои навыки работы с табличными данными на новый уровень? Приготовьтесь, ведь мы сейчас отправимся в увлекательный мир Excel – мощного инструмента, который превратит вашу работу с данными в настоящее творчество! Давайте же приступим к его изучению…

Знакомство с интерфейсом Excel

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

Microsoft Excel – программа, предназначенная для хранения и обработки данных в виде электронных таблиц. Основной формат таблицы Excel – xlsx.

Интерфейс открытой таблицы состоит из следующих элементов:

1. Главная панель, в которой все рабочие инструменты разбиты на основные группы.
2. Меню панели содержит инструменты открытой вкладки главной панели.
3. Строка формулы содержит в себе текстовое значение выделенной ячейки без оформлений.
4. Сама таблица.

В разделе «Главное» находятся основные функции, предназначенные для базового оформления внешнего вида таблицы и данных: изменение шрифта, заливки ячейки, выравнивания текста и так далее.

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

Использование формул

Формулы используются для записи выражений и действий над значениями ячеек. Их запись в ячейке начинается с символа «=». 

В первую очередь Excel поддерживает основные математические операции:

• сложение +
• вычитание —
• умножение *
• деление /
• возведение в степень ^ 

Операции можно проводить как над конкретными числами, так и над значениями ячеек, записывая их номер в виде буква-цифра, например, А1 или С16.

Как игра «Морской бой» связана с Excel? Если внимательно посмотреть на рабочее поле, то можно увидеть две панели – слева панель с цифрами, а сверху – буквы. Это нужно для того, чтобы ячейки можно было идентифицировать по их координатам, которые состоят из буквенной комбинации столбца и номера строки.  Например, ячейка А1 находится в первом столбце и первой строке. Чтобы лучше понять это на примере игры «Морской бой», давайте представим, что игровое поле – это таблица Excel. В этом случае, каждая ячейка имеет свои координаты. Первая ячейка, в левом верхнем углу, будет иметь координаты A1, вторая B1, а третья  – С1… Перейдя на вторую строку, координаты будут меняться, например, первая ячейка второй строки будет иметь координату A2, вторая B2…  Например, на рисунке корабль 270 находится в ячейке B2!

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

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

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

Можно заметить, что формула подстроилась под ее растягивание – если во второй строке находилась формула «=А2+В2», то в шестой строке формула оказалась «=А6+В6». Такое явление называется относительной адресацией – когда значения в формуле зависят от ее перемещения по ячейкам.

Относительная адресация – ссылка на ячейку по отношению к ячейке с формулой. При автозаполнении изменяется.

Иногда необходимо произвести вычисления, используя какую-то константу, которая будет применяться ко всем другим значениям во всех вычислениях. Тогда на помощь придет абсолютная адресация: поставив знак «$» перед буквой столбца и числом строки, «подгон» этой ячейки происходить не будет. При растягивании формулы ее номер не изменится.

Абсолютная адресация – точное указание адреса ячейки в формуле. При автозаполнении адрес остается неизменным.

В примере выше мы задали абсолютную адресацию ячейке С2 и относительную адресацию ячейке А4. Поэтому после растягивания ячеек в Е5 появилась формула «=E4^$C$2» – значение с относительной адресацией подстроилось, а с абсолютной – нет.

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

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

Например, в данном случае:

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

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

Кроме простых формул, Excel предлагает множество функций, которые упрощают выполнение сложных вычислений. Давайте же рассмотрим некоторые из них!

Функции Excel

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

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

Для записи более сложных функций, например, функции ЕСЛИ, которая помогает производить определенные операции при определенных условиях, удобно воспользоваться помощником построения функций слева сверху от таблицы. Записав в ячейке  « = », он автоматически предложит несколько функций, и если нужной здесь не окажется, ее можно будет найти по названию в расширенном поиске. 

Помощник откроется при нажатии по необходимой функции и подскажет, что именно и в каком порядке необходимо вводить. При создании функции, в нем все еще можно записывать номера ячеек, щелкая по ним курсором. Для редактирования формулы можно пользоваться полем над таблицей. Между аргументами в функции ставится знак точка с запятой  «;». 

Какие еще есть функции в Excel?

• МАКС(диапазон) – возвращает максимальное из всех переданных значений;
• МИН(диапазон) – возвращает минимальное из всех переданных значений;
• ЕСЛИ(условие; значение если истина; значение если ложь) – проверяет «условие», и если оно выполняется, заносит в ячейку «значение если истина», если же не выполняется – «значение если ложь»;
• ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3]; …) – функция проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение истина;
• СЧЁТ(диапазон) – подсчитывает количество чисел в переданном диапазоне;
• СЧЁТЕСЛИ(диапазон; критерий) – подсчитывает количество значений в диапазоне, удовлетворяющих критерию;
• СУММ(диапазон) – возвращает сумму переданных значений;
• СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) – используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию;
• СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …) – суммирует все аргументы, удовлетворяющие нескольким условиям;
• СРЗНАЧ(диапазон) – возвращает среднее арифметическое переданных значений;
• ОСТАТ(делимое; делитель) – возвращает остаток от деления делимого на делитель;
• И(условие 1; условие 2) – возвращает истину, если оба переданных условия истинны;
• ИЛИ(условие 1; условие 2) – возвращает истину, если хотя бы одно из условий истинно;
• НАИБОЛЬШИЙ(массив;k) – возвращает k-ое по величине значение из множества данных;
• НАИМЕНЬШИЙ(массив;k) – возвращает k-ое наименьшее значение в множестве данных.

Конечно, формулы хороши. Именно они упрощают процесс работы. Но задумывались ли вы о том, как узнать, сколько строк или столбцов в таблице?

Сколько строк и столбцов в таблице Excel? Одним из главных параметров таблицы в Excel является ее размерность, то есть количество строк и столбцов. В новых версиях программы (Excel 2019 и Excel 365) количество строк на листе может достигать 1 048 576, а количество столбцов – 16 384. Это означает, что пользователи могут работать с огромными объемами данных, которые ранее были недоступны для обработки в Excel. Однако, в старых версиях Excel (2003 и ранее) количество строк было ограничено 65 536, а количество столбцов – 256. Это ограничение было обусловлено техническими ограничениями того времени, когда эти версии программы были созданы. Кроме того, в Excel можно создавать несколько листов в одной книге, каждый из которых может иметь свою размерность. Это позволяет пользователю разбивать данные на логические блоки и удобно работать с ними.

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

Фильтрация и сортировка таблиц

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

Например, класс поручил вам неимоверно важную миссию – найти учительский журнал и исправить все двойки на хорошие оценки.

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

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

Как профильтровать таблицу, а не воду? В первую очередь на помощь нам придет фильтрация. Она позволяет среди всех-всех данных выводить на экран только конкретные, по выбранному параметру.  Это нам пригодится, чтобы найти именно тех учеников, которых мы хотим. Для начала необходимо понять, с какими данными мы имеем дело. Например, у нас есть  фамилии и имена учащихся, классы, оценки. При этом таблица относится к определенному предмету. То есть фильтр можно сделать по-разным характеристикам. Например: — сортировать по классам учащихся, чтобы составить список каждого класса; — сортировать по оценкам, составить статистические списки тех, кто учится на 3,4 или 5; — либо же сортировать по алфавиту (фамилии и имена). То есть сортировать можно по различным параметрам, а их выбор будет зависеть от определенной задачи!

Чтобы отсортировать данные, необходимо выделить все данные и нажать на кнопку «Фильтр».

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

У самых верхних ячеек появится кнопка, при нажатии которой откроется меню фильтрации.

Мы собираемся помогать только своим одноклассникам, сделаем так, чтобы видеть только их. Для этого нажмем на кнопку возле слова «Класс» и выделим наш.

Теперь мы будем видеть только наших одноклассников:

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

Снова выделяем все данные и нажимаем кнопку «Сортировка».

Выплывет окно, в котором мы сможем настроить сортировку – по нужному нам параметру и в нужном нам порядке.

Наконец мы имеем список только наших одноклассников, который выведен в порядке возрастания оценок.

Потренируемся применять полученные навыки на примере задачи 14 номера ОГЭ.

Задание. В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.

В столбце A записан код округа, в котором учится ученик; в столбце B – фамилия, в столбце C – выбранный учеником предмет; в столбце D – тестовый балл. 

Выполните задание.

Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на вопрос и выполните задание.

Определите, сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов. 

Решение.

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

2. По условию задания  необходимо будет сделать проверку сразу по двум столбцам. Необходимо выбрать тех людей, которые сдавали информатику, а потом уже проверить, что они набрали более 600 баллов. Это столбы C и D. Будет намного быстрее, если сначала сортировать людей по предмету.

3. Наложили фильтр и выбрали тех, кто сдавал информатику.

4. Рядом с каждым пропишем функцию, которая будет возвращать 1, если количество баллов больше 600, и 0, если это не так. Затем растянем формулу далее.

5. Когда мы растянули формулу, то можем прописать рядом формулу, которая вычисляет сумму в столбце E (если прописать формулу без номеров ячеек, то просто найдем сумму во всей). Это как раз будет сумма тех, кто набрал нужное количество баллов.

Ответ: 32

А что можно еще сделать с теми отсортированными данными? Иногда требуется быстро составить статистику класса или выборку данных, представить полученную информацию на презентациях или при публике на выступлении… Тогда на помощь приходят диаграммы.

Диаграммы в Excel

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

Excel предлагает широкий спектр диаграмм, включающий:

• столбчатые;
• круговые;
• линейные;
• гистограммы;
• и другие.

Разные типы диаграмм подходят для разных целей и типов данных.

• Столбчатые диаграммы используются для сравнения значений различных категорий. Например, можно создать столбчатую диаграмму для сравнения продаж различных продуктов за определенный период времени.
• Круговые диаграммы используются для отображения соотношения частей в целом. Например, можно создать круговую диаграмму для отображения процентного соотношения расходов на различные категории в бюджете.
• Гистограммы используются для показа распределения данных по интервалам. Например, можно создать гистограмму для отображения распределения оценок студентов по предмету.
• И многие другие…

Выбор правильного типа диаграммы поможет максимально передать информацию! 

Где же найти диаграммы? Необходимо перейти в раздел «Вставка», а затем в этом разделе будут различные инструменты для работы с диаграммами.

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

Нужно лишь выбрать нужный набор данных, а затем перейти в раздел, о котором говорилось выше. Давайте посмотрим на примере.

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

 Затем перейти в раздел «Вставка». Построим круговую диаграмму.

Тогда получим такую картинку, которая наглядно будет показывать данные, что достаточно удобно и просто в восприятии!

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

Потренируемся применять полученные навыки на примере задачи 3 номера ЕГЭ!

Задание В файле приведен фрагмент базы данных «Продукты» о поставках товаров в магазины районов города. База данных состоит из трех таблиц.

Выполните задание.

Необходимо определить общую выручку от продажи всех видов сахара в магазинах Заречного района за весь период, который есть в таблице. Записать в ответе целое число – найденную общую выручку.  

Решение.

1. Для начала проанализируем условие задачи. Нам специально дали схему базы данных, так как это поможет разобраться в ее структуре и быстрее понять, с какими полями необходимо работать. Таблица  «Движение товаров» связана с таблицей «Товар» – артикулом, с таблицей  «Магазин» – ID магазина.

2. Сначала найдем те магазины, которые относятся к Заречному району. Перейдем в таблицу «Магазин», отсортируем данные по Заречному району, запомним ID.

То есть магазины, которые находятся в Заречном районе – M3, M9, M11, M14.

3. Теперь необходимо найти артикул сахара в таблице «Товар». Также отсортируем данные. Артикул всех видов сахара – 28, 29, 30.

4. Теперь уже в основной таблице «Движение товаров» можно сделать сортировку по найденным ID магазинов и артикулам.

5. Обратим внимание, что у каждой операции есть определенный статус – поступление или продажа. То есть поступление означает, что товар приняли в магазине, товара прибавилось, магазин потратил деньги на закупку. Продажа означает, что товар продали, количество товара уменьшилось, а магазин заработал деньги. Поэтому придумали специальные экономические термины, которые в данном задании бывают часто полезны! 

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

6. Когда мы прояснили термины, можно перейти к решению задачи. То есть нам нужно найти сумму, которую магазины получили с продажи товаров. Выставим соответствующий фильтр.

7. Теперь подсчитаем общую сумму. Заметим, что у нас в колонках ее нет, но есть количество упаковок, которые были проданы (F), а также есть цена одной упаковки (G). То есть нужно умножить количество упаковок на цену одной, тогда получим сумму за одну операцию.

8. Общую сумму подсчитаем в стороне с помощью формулы. Нужно найти сумму в H.

Ответ: 33620

В этой статье мы узнали о важном и полезном инструменте – Excel, встретились с новыми терминами, которые обязательно пригодятся в дальнейшем! Приглашаем познакомиться с не менее интересной статьей «Файлы Word и работа с ними».

Термины

База данных – это систематический сбор данных, хранящихся в электронном виде. Он может содержать любые типы данных, включая слова, цифры, изображения, видео и файлы, присущие некоторым объектам, которые связаны между собой элементами связи.

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

Фактчек

• Для обработки данных в электронных таблицах Microsoft Excel используются формулы. Помимо стандартных математических операций, также используются функции для более сложных вычислений и поиска информации.
• Функции в Excel являются встроенными инструментами, которые позволяют выполнять различные операции с данными, такие как суммирование, вычисление среднего значения, поиск максимального или минимального значения и т.д. Например: МАКС, МИН, ЕСЛИ, ЕСЛИМН, СЧËТ, СЧËТЕСЛИ и другие.
• Фильтрация электронной таблицы позволяет вывести не все данные, а только необходимые. Сортировка электронной таблицы позволяет вывести все данные в определенном порядке.
• Диаграммы в Excel позволяют визуализировать данные и легко анализировать их. В программе доступны различные типы, такие как столбчатые, круговые, линейные и т.д. Их можно настраивать по своему усмотрению, изменяя цвета, шрифты и многое другое.

Проверь себя

Задание 1.
Какая логика работы функции =ЕСЛИ(А1+В1>C1; A1+B1; C1)?

1. Если А1+В1 больше С1, то в ячейку будет записано А1+В1, иначе – С1
2. Если А1+В1 больше С1, то в ячейку будет записано С1, иначе – А1+В1
3. А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то в ячейке будет и второе значение – С1
4. А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то к этому значению будет еще и прибавлено С1

Задание 2.
Какая функция отвечает за подсчет значений в электронной таблице, удовлетворяющих определенному критерию?

1. СЧЁТ
2. СЧЁТЕСЛИ
3. ОСТАТ
4. СУММ

Задание 3.
Для чего в Excel используется фильтрация?

1. Для вывода информации в определенном порядке
2. Для изменения большого объема информации
3. Для удаления информации
4. Для вывода только определенной информации

Задание 4.
Какой символ следует использовать для абсолютной адресации в Excel?

1. $
2. %
3. @
4. *

Ответы: 1. – 1; 2. – 2; 3. – 4; 4. – 1.