Ячейка в электронной таблице это: Урок 20. Электронные таблицы. Интерфейс электронных таблиц. Данные в ячейках таблицы. Основные режим

Электронные таблицы: определение, достоинства, Excel

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

 

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

 

Электронные таблицы имеют ряд неоспоримых достоинств:

 

1. В отличие от таблиц на бумаге электронные таблицы обеспечивают проведение динамических вычислений

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

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

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

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

5. Решение математических, оптимизационных, статистических и иных задач.

6. Анализ, поиск, сортировку, выборку числовых, текстовых и иных по определенным данным.

 

Давайте рассмотрим электронную таблицу на примере программы Microsoft Excel.

 

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

 

1 При работе на компьютере электронная таблица существует в форме рабочего листа, который имеет имя (например, Лист 1).

2 Рабочие листы объединяются в книги, причем пользователь может вставлять, копировать, удалять и переименовывать рабочие листы.

3 Ячейки являются основным элементом электронной таблицы, в которые могут вводиться данные и на которые можно ссылаться по именам ячеек.

4 Одна из ячеек – активная. В ней находиться табличный курсор и в нее, собственно, вводятся данные. К данным относятся: числа, даты, время суток, текст или символьные данные и формулы.

5 При необходимости можно создавать 

диапазон ячеек – выделение нескольких ячеек в одно целое (например диапазон А2:А4, С1:Е1 и так далее).

6 Обработка данных осуществляется с применением команд системы меню Excel и совокупности инструментальных кнопок, расположенных на интерфейсной ленте Office в качестве «шапки» рабочего листа. Лента по умолчанию включает в себя следующие вкладки : Файл, Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид, Надстройки. Каждая из этих вкладок содержит определенный набор инструментов, за счет которых осуществляется основные действия с рабочими листами.

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

Эти и другие элементы окна электронной таблицы Excel 2010 можно увидеть на рисунке.

 

 

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

Электронные таблицы

Электронные таблицы

Типы данных

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

Адреса ячеек

В формулах могут использоваться адреса других отдельных ячеек или блоков ячеек. Адрес ячейки имеет вид A1, где сначала записывается номер столбца, затем номер строки. Столбцы обозначаются буквами от A до Z, затем идут столбцы, обозначенные двумя буквами от AA до ZZ, затем столбцы из трёх букв от AAA и далее.

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

Абсолютные и относительные адреса

Рассмотрим следующую таблицу:

В ячейке С1 записана формула =A1+B1. Если эту формулу скопировать в ячейку C2, то адреса в этой формуле автоматически изменятся на =A2+B2, а если скопировать ещё на одну клетку ниже, то формула примет вид =A3+B3. То есть адреса ячеек автоматически меняются, при копировании формулы на одну строку ниже, номера строк в формуле также уменьшаются на 1. Если же эту формулу из ячейки C1 скопировать в D1, то изменятся номера столбцов: =B1+C1. Говорят, что адреса в формулах являются относительными, то есть при копировании формул используется расположение ячеек относительно той ячейки, где записана формула.

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

Если нужно, чтобы адрес какой-то ячейки не менялся, то есть был бы абсолютным адресом, необходимо в формуле записать этот адрес, поставив перед номером строки и столбца знак “$”: $A$1. Тогда этот адрес при копировании будет всегда иметь вид $A$1.

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

Общие сведения о функциях

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

b\).

SQRT(a) (КОРЕНЬ) — возвращает \(\sqrt{a}\).

QUOTIENT(a; b) (ЧАСТНОЕ) — возвращает целочисленное частное от деления a на b.

MOD(a; b) (ОСТАТ) — возвращает остаток от деления a на b.

ABS(x) (ABS) — возвращает модуль числа x.

MIN(x; y; …) (МИН) — возвращает наименьшее из аргументов. Также можно использовать с одним аргументом, являющимся блоком.

MAX(x; y; …) (МАКС) — возвращает наибольшее из аргументов. Также можно использовать с одним аргументом, являющимся блоком.

Суммы, количество, среднее

SUM(a; b; c; …) (СУММ) — возвращает сумму аргументов, каждый аргумент может быть блоком, тогда берётся сумма блока. Можно просто вызвать от одного аргумента (блока).

COUNT(a; b; c; …) (СЧЕТ) — возвращает количество чисел среди своих аргументов.

AVERAGE(a; b; c; …) (СРЗНАЧ) — возвращает среднее значение чисел среди своих аргументов.

SUMPRODUCT(a; b; …) (СУММПРОИЗВ) — возвращает сумму произведений элементов, которые берутся из блоков–аргументов. Все аргументы должны быть блоками одного размера. Перемножаются по одному элементу из каждого блока, стоящие на одной позиции (то есть если два аргумента, то перемножаются пары чисел, если три аргумента — тройки чисел), и суммируются полученные произведения.

Логические функции

TRUE() (ИСТИНА) — возвращает истину (логическое значение).

FALSE() (ЛОЖЬ) — возвращает ложь (логическое значение).

IF(a; b; c) (ЕСЛИ) — проверяет условие a. Если оно истинно, то функция возвращает значение b, иначе возвращает значение с.

AND(a; b; …) (И) — возвращает конъюнкцию всех своих аргументов.

OR(a; b; …) (ИЛИ) — возвращает дизъюнкцию всех своих аргументов.

IFS(a1; b1; a2; b2; …) (ЕСЛИМН) — проверяет условие a1. Если оно истинно, то функция возвращает значение b1. Если оно ложно, то проверяется условие a2, и если оно истинно, то возвращается b2. Если и оно ложно, то переходится к следующей паре условия и значения. Функция возвращает первое значение, для которого условие истинно.

Операции с блоками ячеек с некоторым условием

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

COUNTIF(Блок; Условие) (СЧЕТЕСЛИ) — подсчитывает количество значений в данном блоке, удовлетворяющих условию. В качестве условия может быть одно значение, например, COUNTIF(A1:A10; 4) подсчитывает количество значений, равных 4. Также в качестве условия может быть строка вида «<0», «<=0», «>0», «>=0», «<>0» (вместо 0 можно написать любое число), строка должна быть заключена в кавычки. Например, COUNTIF(A1:A10; «>0») подсчитывает количество положительных чисел в блоке.

SUMIF(Блок; Условие; Блок суммирования) (СУММЕСЛИ) — подсчитывает сумму значений, для которых верно условие. При этом условие проверяется для первого блока, а суммируются значения, из второго блока. Оба блока должны быть одинакового размера. Например, SUMIF(A1:A10; «>0» B1:B10) суммирует числа из блока B1:B10 в тех строках, в которых в столбце A записаны положительные числа.

AVERAGEIF(СРЗНАЧЕСЛИ) — аналогично SUMIF, но вычисляет среднее значение.

COUNTIFS(Блок1; Условие1; Блок2; Условие2; …) (СЧЕТЕСЛИМН) — возвращает количество записей, удовлетворяющих одному или нескольким условиям. Для каждого блока проверяется своё условие, все блоки должны быть одинакового размера. Например, COUNTIFS(A1:A10; «>0»; B1:B10; 5) возвращает количество строк, для которых в столбце A записано положительное число, а в столбце B записано 5.

SUMIFS(Блок суммирования; Блок1; Условие1; Блок2; Условие2; …) (СУММЕСЛИМН) — возвращает сумму значений, удовлетворяющих одному или нескольким условиям. Значения берутся из блока в первом параметре. Обратите внимание, что порядок аргументов отличается от функции SUMIF.

AVERAGEIFS (СРЗНАЧЕСЛИМН), MINIFS (МИНЕСЛИ), MAXIFS (МАКСЕСЛИ) — аналогично SUMIFS возвращают среднее значение, наибольшее и наименьшее из записей, удовлетворяющих одному или нескольким условиям.

Адресация ячеек из другого листа

В электронной таблице может быть несколько листов, переключаться между которыми можно, кликая на вкладки с названиями листа внизу окна. По умолчанию листы называются Лист1, Лист2 и т.д., но их можно переименовать по своему усмотрению.

В формулах можно использовать ссылки на ячейки и блоки с других листов. Пример ссылки на ячейку A1 с листа Лист1: Лист1.A1. Можно ссылаться также на блоки, использовать в блоках абсолютную и относительную адресацию. Например, SUM(Лист1.$A$1:$A10).

Если в названии листа есть пробелы (или точка, или другие опасные символы), название листа заключается в одинарные кавычки: ‘Результаты олимпиады’.A1.

В Microsoft Excel и в Google docs вместо точки используется восклицательный знак: Лист1!A1.

Сортировки и фильтры

В электронных таблицах бывают сортировки и фильтры.

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

В окне диалога сортировки можно указать один или несколько параметров сортировки (сортировка проводится по первому параметру, при равных значениях — по второму и т. д.). Сортировка является устойчивой. На вкладке “Параметры” диалога сортировки полезная опция  Диапазон содержит метки столбцов.

Фильтры позволяют скрыть (временно) строки, соответствующие определённому условию.

Поиск данных (VLOOKUP)

Функция VLOOKUP позволяет искать записи (строки), в которых в определённых столбцах записано определённое значение. Использование:

VLOOKUP(Значение; Блок; Номер столбца; Признак сортировки) (ВПР)

Значение — число или строка, которую нужно найти.

Блок — обычно из нескольких столбцов. Поиск осуществляется по значениями из первого столбца блока.

Номер столбца — число (1, 2, 3…), не большее, чем число столбцов в блоке. Функция возвращает значение, которое находится в блоке в найденной строке (в первом столбце этого блока в этой строке записано то, что ищется), в столбце с данным номером.

Признак сортировки — почти всегда нужно указать 0. Значение 1 или TRUE(), а также положительное значение предполагает, что строки упорядочены по первому столбцу, тогда используется двоичный поиск.

Пример: VLOOKUP(«Иванов»; Лист1.$C$100:$G$100; 3; 0). Осуществляется поиск записей в строках 1-100 на Лист1, у которых в столбце C записана строка «Иванов». Если строка будет найдена, то будет возвращено значение из столбца E (столбец C имеет номер 1, D — 2; E — 3).

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

Обработка ошибок

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

ISERROR(Выражение) — возвращает TRUE, если выражение является ошибочным, или FALSE при отсутствии ошибок.

IFERROR(Выражение; Другое выражение) — возвращает Выражение, если при его вычислении не произошло ошибок. Если же произошла ошибка, то возвращается Другое выражение.

IFNA — то же самое, но учитывается только ошибка #N/A.

Как связать данные из одной электронной таблицы с другой

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

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

Вот два простых способа скопировать данные с другого листа в Excel (и тот же трюк работает для Google Sheets , Numbers и других популярных приложений для работы с электронными таблицами).

Скопируйте ячейки с одного листа на другой с помощью !

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

1. В Excel (или любом приложении для работы с электронными таблицами) откройте или создайте новый лист.

2. Выберите ячейку, в которую вы хотите извлечь данные.

3. Введите = , за которым сразу же следует имя исходного листа, восклицательный знак и имя копируемой ячейки. Например, = Список!A2 .

Вот подробный пример: 

Предположим, исходный лист называется «Список», и вам нужно скопировать данные из ячейки A2 в другой лист с именем «Имена». На листе «Имена» щелкните нужную ячейку, введите =Roster!A2 , и данные из ячейки A2 исходного листа будут заполнены.

Или есть более простой вариант.

1. Введите = в ячейку, где вы хотите сослаться на данные из других листов.

2. Переключиться на исходный лист.

3. Щелкните копируемую ячейку.

4. Нажмите Enter, и функция заполнится автоматически.

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

Нужно рассчитать значения, используя данные в исходной ячейке? Просто введите остальную часть вашей функции как обычно. Например, если Names!B3 имеет значение 3 и вы введете =Names!B3*3 , вы получите результат 9 в новой ячейке, как и ожидалось.

Соедините листы в разных электронных таблицах

У вас есть данные в двух разных электронных таблицах , которые вы хотите скопировать в новую электронную таблицу? Лучший вариант — использовать рабочие процессы Microsoft Excel Zapier (или рабочие процессы Google Sheets) для подключения ваших таблиц. С помощью этих Zaps (мы называем их готовыми рабочими процессами) Zapier может отслеживать новые или обновленные данные в ваших исходных ячейках и автоматически копировать их в нужную электронную таблицу.

Копировать новые строки между таблицами Excel

Копировать новые строки между таблицами Excel

Попробуйте

Microsoft Excel 9000 5

Microsoft Excel

Обновление строк Google Sheets данными из новых строк также включено Google Sheets

Обновление строк Google Sheets данными из новых строк также в Google Sheets

Попробуйте

Google Sheets

Google Sheets

Создание строк Google Sheets из новых обновлений других строк Google Sheets

Создание строк Google Sheets из новых обновлений других строк Google Sheets

Попробуйте

9 0017

Таблицы Google

Google Таблицы

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