Магия Excel: 10 самых полезных «фишек» для работы с таблицами
Дарья Гордеева
Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО).
Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
ДД.
ММ.ГГГГ
ДД/ММ/ГГГГ
ГГГГ-ММ-ДД
С датами можно производить операции вычитания и сложения.
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число.
Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне.
Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».
На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:
А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить».
Изменения будут применены ко всем нужным ячейкам в документе.
На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь→
Фото на обложке отсюда
Nothing found for Ms Excel %25D0%25B1%25D0%25B0%25D0%25B7%25D0%25Be%25D0%25B2%25D1%258B%25D0%25B9 %25D0%25Ba%25D1%2583%25D1%2580%25D1%2581 %25D0%25B4%25D0%25Bb%25D1%258F %25D0%25Bd%25D0%25B0%25D1%2587%25D0%25B8%25D0%25Bd%25D0%25B0%25D1%258E%25D1%2589%25D0%25B8%25D1%2585
- Soome tööohutuskaardi ehk rohelise kaardi koolitus
Soome tööohutuskaart (nn roheline kaart ehk työturvallisuuskortti) on Soomes väljatöötatud riiklik meede tööohutuse edendamiseks.
Koolituse edukas läbimine on eelduseks peamiselt Soome ja ka teistesse Põhjamaade ettevõtetesse tööle kandideerimisel. - Arvutigraafika e-kursus
Koolitus on mõeldud kõikidele, kes soovivad omandada teoreetilisi ja praktilisi teadmisi arvutigraafikast.
Kursuse alustamiseks on vajalik esmane arvutikasutamisoskus. - Töökeskkonnaspetsialistide ja -volinike väljaõppe koolitus (24 akad. t)
Koolitus töötervishoiu- ja tööohutusest annab vajalikud teadmised, mida teha, et tagada ohutu ja seaduse nõudeid järgiv töökeskkond.
- Raamatupidamise algkursus
Kursus on suunatud eelkõige isikutele, kes tunnevad huvi ja praktilist vajadust hakata tegelema raamatupidamisega: ettevõtjatele, algajatele, kellel puuduvad eelnevad kokkupuuted raamatupidamisega, väike- ning perefirmade juhtidele, sekretäridele/juhiabidele ja teistele huvilistele.
- Esmaabi väljaõppe koolitus (16-tunnine)
Koolituse eesmärk on põhiteadmiste ja praktiliste oskuste andmine kannatanu elu päästmiseks õnnetusjuhtumi ja eluohtlike haigusseisundite korral ning esmaabi osutamiseks.
- Tegevusjuhendaja koolitus koos esmaabi väljaõppega
Koolituse eesmärgiks on omandada teadmised, kuidas parandada psüühilise erivajadusega inimeste sotsiaalseid oskusi ja toimetulekuvõimet ning tagada neile vajalik kõrvalabi.
- Esmane erakorraline abi – Esmaabi täiendõppe koolitus perearstidele ja pereõdedele
Koolituse eesmärk: Tagada esmatasandi tervishoiutöötajate vajalik ettevalmistus, mis võimaldab edukalt täita esmaabiandja ülesandeid erakorralistes situatsioonides.
- Tervishoiuasutuste tööohutusalane koolitus
Õppe eesmärk: Anda tervishoiuasutuste tööohutuse eest vastutavatele isikutele teadmised töökeskkonna, erinevate ohutegurite kohta ning juhised, kuidas erinevates olukordades käituda.
- Стропальщик. Крановщик, управляющий мостовыми, козловыми и консольными кранами грузоподъёмностью до и выше 5 тонн, управляемыми вне кабины.
Цель: Дать слушателям знания, умения и навыки в формировании компетенции для выполнения трудовой деятельности машинистом крана, обеспечения безопасной эксплуатации грузоподъемных кранов при производстве строительно-монтажных, ремонтно-строительных и погрузочно-разгрузочных работ.
- Курсы cтропальщикoв
Цель: Дать знания по безопасному производству работ по подъему и перемещению различных грузов (производственное помещение, стройплощадка, склад и пр.). Знание производственных инструкций по технике безопасности. Знание прав и обязанностей работодателей и работников по обеспечению выполнения требований гигиены и безопасности труда в любой ситуации, связанной с трудом.
- Kodulehe tegemise koolitus WordPressiga e-kursus
WordPressiga kodulehe tegemise koolituse e-kursus koosneb 88 videost kogukestvusega 5 tundi ja 48 minutit.
WordPressiga kodulehe koolituse e-kursus on mõeldud nii neile, kes pole üldse WordPressiga kokku puutunud, kui ka neile, kes on küll WordPressi kasutanud, aga soovivad juurde õppida. Antud koolituse abil saad Sa täiesti ise luua tänapäevased ja sisukad, professionaalse väljanägemisega kodulehed. - Курс по пожарной безопасности при выполнении огневых работ
Курс по пожарной безопасности при выполнении огневых работ предусмотрен постановлением министра МВД за номером 38.
Данное постановление вытекает из Закона о пожарной безопасности, пар.14 пункт 4. - Курсы усовершенствования по оказанию первой помощи
На рабочем месте всегда должно быть, по меньшей мере, одно обученное лицо, оказывающее первую помощь. Назначенное лицо, оказывающее первую помощь, должно в течение 1 месяца со дня назначения пройти 16-часовое обучение в учебном заведении, зарегистрированном в Министерстве социальных дел.
- Tuleohutuse eest vastutava isiku koolitus
Õppekava eesmärk on anda organisatsioonis tuleohutuse ja evakuatsiooni eest vastutavale isikule teadmised ohutu evakuatsiooni tagamiseks ja tuleohutusalase heakorra tagamiseks.
- CEH v12 Certified Ethical Hacking
The Five Phases of Ethical Hacking and the original core mission of CEH remain valid and relevant today: “To beat a hacker, you need to think like a hacker.
- Tõstukijuhi ja korvtõstukijuhi põhikoolitus
Koolituse eesmärk on anda teadmisi tööks erinevate tõstukitega (korvtôstuk, teleskooptôstuk, hüdroteleskooptôstuk, kahveltôstuk, poomkorvtôstuk, käärtôstuk, virnastaja, käsukahveltôstuk).
- MS Exceli põhikursus
Koolitusele ootame inimesi, kel puudub varasem töökogemus tabelarvutusega ja kelle töö nõuab Exceli kasutamist
- Õhtune raamatupidamise algkursus
Kampaania! Soodsam hind ja pikem kursus!
Raamatupidamise algõpe kõigile, kes soovivad lähemalt kokku puutuda raamatupidamisega. - Esmaabi väljaõppe koolitus (16-tunnine)
Koolituse eesmärk on põhiteadmiste ja praktiliste oskuste andmine kannatanu elu päästmiseks õnnetusjuhtumi ja eluohtlike haigusseisundite korral ning esmaabi osutamiseks.
Данное постановление вытекает из Закона о пожарной безопасности, пар.14 пункт 4.
SorteeriNimetusHindToimumisaeg
Shruti M
Старший аналитик-исследовательShruti — инженер и технофил. Она работает над несколькими трендовыми технологиями. Ее хобби включают чтение, танцы и изучение новых языков. В настоящее время она изучает японский язык.
Popular from Shruti M
Статья25 лучших формул Excel, которые вы должны знать
В этой статье мы обсудим различные функции и формулы Microsoft Excel.
60 лучших вопросов и ответов для интервью с аналитиками данных за 2023 год
В этой статье вы познакомитесь с самыми популярными вопросами для интервью с аналитиками данных.
By Shruti MLПоследнее обновление 3 апреля 2023 г. УчебникТипы кибератак, о которых следует знать в 2023 году Кибератаки и как они могут быть предотвращено.
на Shruti Mlast обновлен на 5 мая 2023 г. Учебное пособиеЛучшие приложения для больших данных в разных отраслях
Узнайте, как применения больших данных играют важную роль в рамках банковского обучения, здравоохранения и нескольких других отраслей.
Автор: Shruti MLПоследнее обновление 13 апреля 2023 г. СтатьяПолное руководство по Power Query в Excel
Интерфейс Power Query удобен для пользователя. Так как он очень похож на интерфейс Excel. Читай дальше!
By Shruti MLПоследнее обновление 5 мая 2023 г.
Учебное пособие10 лучших вакансий в области кибербезопасности в 2023 году: информация о карьере и зарплате
В этой статье вы получите представление о различные рабочие места в области кибербезопасности и то, как вы можете начать свою карьеру.
Автор: Shruti MLПоследнее обновление: 8 мая 2023 г. модельные ответы. By Shruti MLПоследнее обновление 12 декабря 2022 г. СтатьяКак создать абсолютную ссылку в Excel?
В этой статье мы расскажем вам о создании абсолютной ссылки в Excel, ее использовании и многом другом.
Автор: Shruti MLПоследнее обновление 4 апреля 2023 г. СтатьяВсе, что вам нужно знать об очистке данных Excel
Узнайте, как очистить данные с помощью Excel.
By Shruti MLПоследнее обновление 31 марта 2023 г. Учебное пособиеУзнайте о различиях между ИИ, машинным обучением и глубоким обучением
В этом руководстве четко объясняются различия между ИИ, машинным обучением и глубоким обучением.
.
Установка Cloudera Quickstart VM — лучший способ
В этой статье мы рассмотрели, что такое Cloudera QuickStart VM, и какие условия для установки это.
By Shruti MLПоследнее обновление 3 апреля 2023 г. Учебное пособиеПолное ноу-хау по алгоритму MD5
Узнайте все об алгоритме MD5
By Shruti MLПоследнее обновление 23 февраля, 2023 СтатьяУпрощенное и полное руководство по изучению распределения вероятностей
Подробнее о распределении вероятностей и его типах
By Shruti MLПоследнее обновление 15 февраля 2023 г. СтатьяВсе, что вам нужно знать о формуле округления в Excel
Узнайте все о формуле округления в Excel.
Автор: Shruti MLПоследнее обновление 15 февраля 2023 г. СтатьяЗамораживание областей в Excel: все, что вам нужно знать
В этой статье мы обсудим замораживание областей в Excel.
Советы по проекту выпускного года, которые спасут положение
В этой статье мы дадим вам семь жизненно важных советов, которые помогут вам успешно выполнить проект выпускного года.
Автор: Shruti MLПоследнее обновление 24 марта 2023 г. СтатьяКак сортировать данные в Excel: лучшее руководство
В этой статье вы узнаете, как сортировать данные в Excel. Кроме того, вы также узнаете, как фильтровать данные.
По Shruti MLПоследнее обновление 24 февраля 2023 г. СтатьяКак транспонировать данные Excel? Три метода, которые необходимо знать
В этой статье вы узнаете о различных способах транспонирования данных Excel.
By Shruti MLПоследнее обновление 24 февраля 2023 г. Учебное пособиеВведение в случайный лес в R
В этой статье мы рассмотрим алгоритм случайного леса в R с нуля.
Учебник по пряже
YARN — это аббревиатура от Yet Another Resource Negotiator. Хотите узнать больше? Посмотрите этот урок!
на Shruti Mlast, обновленный 11 октября, 2022 УчебникВаше универсальное решение, которое поможет вам узнать, как преобразовать PDF в Excel
таблица данных Excel
Автор: Shruti MLПоследнее обновление 3 мая 2023 г. УчебникPHP Вопросы для интервью
Вот 40 лучших вопросов и ответов на PHP-интервью, которые помогут вам пройти собеседование.
Автор: Shruti MLПоследнее обновление 24 февраля 2023 г. СтатьяУчебное пособие по расширенным концепциям Hive и разделению файлов данных
Узнайте о языке запросов Hive и о том, как его можно расширить для улучшения производительность запросов и многое другое
Автор: Shruti MLПоследнее обновление 23 февраля 2023 г.
СтатьяЛучшее руководство по созданию информационной панели Excel
В этой статье мы объясним, как создать информационную панель Excel.
Автор: Shruti MLПоследнее обновление 23 февраля 2023 г. УчебникКак установить Hadoop на Ubuntu
Этот блог представляет собой руководство по установке Hadoop на Ubuntu.
По Shruti MLПоследнее обновление 2 июня 2021 г. Учебное пособиеУчебное пособие по Sqoop. Ваше руководство по правильному управлению большими данными в Hadoop
Давайте углубимся во все, от основ Sqoop и его архитектуры до того, как его использовать.
Автор: Shruti MLПоследнее обновление 23 февраля 2023 г. СтатьяУчебное пособие по Apache Storm: введение
Узнайте все об Apache Storm, его модели данных, архитектуре и компонентах
9 0002 К Shruti MLПоследнее обновление 12 декабря 2022 г. СтатьяДокумент обновления MongoDB
Узнайте, как обновить документ в MongoDB.
Работа с командой Alter в SQL
Узнайте все о команде ALTER в SQL, а также различные примеры
9 0002 По Shruti MПоследнее обновление 24 февраля 2023 г.Написание формул в Excel и Airtable
- Обновлено 04 июля 2022 г.
- 3 Минуты на чтение
Распечатать
Темный
Свет
Если вы пользуетесь Excel, знакомы с написанием формул и только начинаете работать с Airtable, эта статья для вас! Хотя принципы функций формул в Airtable схожи, есть некоторые ключевые отличия, которые нужно изучить в начале, и они окупятся, когда вы станете экспертом по формулам Airtable.
Терминология
| Excel | Использование Excel | Airtable | Использование Airtable |
|---|---|---|---|
| Рабочая тетрадь | Файл электронной таблицы Excel | База | База похожа на файл, содержащий любое количество таблиц |
| Рабочий лист | Отдельные электронные таблицы в рабочей книге | Таблица | Таблицы, содержащие отдельные наборы информации (хотя они могут быть связаны) |
| Н/Д | Функция не похожа на представления Airtable | Представление | Одна из самых мощных функций Airtable, представления — это различные способы просмотра данных в таблице, включая следующие типы представлений: сетка, календарь , канбан, форма и галерея. |
| Столбец | Буквенный (например, столбец B) вертикальный набор ячеек | Поле | Различные типы полей используются для отображения различных данных, которые применяются к каждой записи в таблице
|
| Строка | Пронумерованная (например, строка 3) горизонтальная совокупность ячеек | Запись | Единая запись информации, содержащая информацию, которая хранится в полях |
| Ячейка | Отдельная ячейка, на которую ссылается столбец + строка (например, ячейка B4) | Ячейка | Отдельная ячейка, являющаяся частью записи. |
Airtable похож на электронную таблицу
и база данныхХотя такие программы, как Excel, функционируют только как традиционные электронные таблицы, Airtable работает одинаково как с электронными таблицами, так и с базами данных. В виде электронной таблицы вы можете структурировать информацию в виде сетки с ожидаемым форматом столбцов, строк и ячеек. В качестве базы данных вы можете связывать записи друг с другом для создания мощных ссылок на данные, которые открывают больше возможностей для ввода данных, расчетов по формулам и многого другого.
Airtable также позволяет просматривать те же данные, которые отображаются в виде сетки, другими способами в виде календаря, доски канбан, построителя форм и представления галереи.
Формулы Airtable ссылаются на все поля, а не на определенные ячейки
В электронной таблице вы можете поместить формулу в любую ячейку, и она будет ссылаться на любую другую ячейку на листе.
Airtable — это реляционная база данных, поэтому формулы применяются ко всему полю (то, что Airtable называет столбцом), так что одна и та же формула применяется к каждой записи в этом поле.
Excel
На любую конкретную ячейку можно ссылаться в другой ячейке. Например, чтобы определить источник каждой покупки в таблице, вы должны написать формулу, которая ссылается на каждую ячейку — например, A3 — для проверки типа источника.
Это формула, используемая ниже: =IF(A3='Online','Web','Store')
Ссылается на определенные ячейки, такие как A3.
Airtable
Формула всегда применяется ко всему полю, а не к отдельным ячейкам. Например, чтобы определить источник каждой покупки, вы должны написать одну формулу для ссылки на поле {Тип покупки} , и формула будет проверять это поле для каждой записи в таблице.
Это формула, используемая ниже: IF({Тип покупки}='Онлайн','Интернет','Магазин')
здесь будет ваш заголовок
В Airtable вы не начинаете формулу с написания =.
Вместо этого вы можете просто начать писать формулу.
Формулы Airtable работают внутри таблиц, а не в базе
Аналогично предыдущему, формулы Airtable по умолчанию ссылаются только на поля в одной таблице. Однако есть способы перенести значение из другой таблицы, чтобы сослаться на него в поле формулы (в этом подходе используются связанные записи и поля подстановки, которые более подробно рассматриваются здесь).
Excel
Чтобы сослаться на запись в другой таблице, вы должны написать конкретные ссылки на другую таблицу, используя формат, подобный следующему: =IF(Tasks!B1=’Домашние улучшения’,Tasks!A1,0). Затем вам нужно применить эту формулу к каждой ячейке (убедившись, что вы изменили ссылки на ячейки для каждой), где вы хотите запустить формулу.
Airtable
