Работа в excel с таблицами и формулами для начинающих

Умные Таблицы Excel – секреты эффективной работы

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

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

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

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

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

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

то она автоматически переделается в

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.


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

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

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

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

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.


Источник: statanaliz.info

Работа с формулами в Excel

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

Как работать с формулами в MS Excel

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

  • = («знак равно») – равенство, равно. Обычно отвечает за начало формулы;
  • + («плюс») – сложение. Может складывать как числа, так и выражения;
  • — («минус») – вычитание;
  • * («звёздочка») – умножение;
  • / («наклонная вправо») – делит;
  • ^ («циркумфлекс») – возводит в степень.

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

Работа с простейшими формулами

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

  1. В готовой таблице выделите ту ячейку, куда хотите получить результат вашей формулы.
  2. Поставьте в этой ячейке знак «=». Он отвечает за начало формулы. Также вы можете его поставить не в самой ячейке, а в специальном поле, что расположено сверху. Здесь уже как вам будет самим удобно. Рассмотрим пример формулы a+b.
  3. После знака равно кликните левой кнопкой мыши по ячейки, значение из которой вам нужно взять в качестве первого.
  4. Автоматически установится наименование этой ячейки (латинская буква и номер) после знака равно. Здесь же вам требуется установить оператор, отвечающий за действие с этими ячейками – плюс, минус, равно, делить и т.д.
  5. Нажмите теперь левой кнопкой мыши по той ячейки, которая будет выполнять функции b в нашей формуле.

  • Чтобы получить результат расчётов, нажмите на клавишу Enter.
  • По такой инструкции можно делать формулы с несколькими переменными, а не только вида a+b. Например, вы можете сделать по аналогии формулу вида a+b+c, a+b+c+d и т.д.

    Примеры вычислений

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

    1. Задайте формулу вида a*b, чтобы рассчитать полученную сумму от продажи товара. Вид у формулы в Excel будет таким: =(название ячейки с количеством товара, например, B3)*(название ячейки с ценой, например, C3).
    2. Нажмите Enter, чтобы увидеть полученный результат в ячейки суммы у определённого товара. Учтите, что в формуле не должно быть пустых ячеек. В противном случае вместо результата вы получите просто сообщение об ошибке.

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

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

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

      В ячейку напротив столбца «Сумма» укажите формулу вида: =([название ячейки с количеством первой партии товара, например, B3]+[название ячейки с количеством второй партии товара, например, C3])*[название ячейки с ценой, например, D3].

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

    Использование в качестве калькулятора

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

    1. На листе выберите ячейку, в которой собираетесь происходить расчёты.
    2. Выделите эту ячейку и поставьте в ней знак равно.
    3. Пропишите нужные действия. Сюда можно прописать и сложный пример по типу: 444*89+((78-660)*9)/15).

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

    Источник: public-pc.com

    12 простых приёмов для ускоренной работы в Excel

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

    Автор проекта «Планета Excel», разработчик и IT-тренер.

    1. Быстрое добавление новых данных в диаграмму

    Если для построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

    2. Мгновенное заполнение (Flash Fill)

    Предположим, у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы сделать это, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно. Подобным образом можно извлекать имена из email, склеивать ФИО из фрагментов и так далее.

    3. Копирование без нарушения форматов

    Вы, скорее всего, знаете о волшебном маркере автозаполнения. Это тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, так как копируется не только формула, но и формат ячейки. Этого можно избежать. Сразу после того, как потянули за чёрный крест, нажмите на смарт-тег — специальный значок, появляющийся в правом нижнем углу скопированной области.

    Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Excel скопирует вашу формулу без формата и не будет портить оформление.

    4. Отображение данных из таблицы Excel на карте

    В Excel можно быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин «Карты Bing» (Bing Maps). Это можно сделать и по прямой ссылке с сайта, нажав кнопку Get It Now.

    После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней. При желании в настройках плагина можно выбрать тип диаграммы и цвета для отображения.

    5. Быстрый переход к нужному листу

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

    6. Преобразование строк в столбцы и обратно

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

    1. Выделите диапазон.
    2. Скопируйте его (Ctrl + C) или, нажав на правую кнопку мыши, выберите «Копировать» (Copy).
    3. Щёлкните правой кнопкой мыши по ячейке, куда хотите вставить данные, и выберите в контекстном меню один из вариантов специальной вставки — значок «Транспонировать» (Transpose). В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose).

    7. Выпадающий список в ячейке

    Если в какую-либо ячейку предполагается ввод строго определённых значений из разрешённого набора (например, только «да» и «нет» или только из списка отделов компании и так далее), то это можно легко организовать при помощи выпадающего списка.

    1. Выделите ячейку (или диапазон ячеек), в которых должно быть такое ограничение.
    2. Нажмите кнопку «Проверка данных» на вкладке «Данные» (Data → Validation).
    3. В выпадающем списке «Тип» (Allow) выберите вариант «Список» (List).
    4. В поле «Источник» (Source) задайте диапазон, содержащий эталонные варианты элементов, которые и будут впоследствии выпадать при вводе.

    8. Умная таблица

    Если выделить диапазон с данными и на вкладке «Главная» нажать «Форматировать как таблицу» (Home → Format as Table), то наш список будет преобразован в умную таблицу, которая умеет много полезного:

    1. Автоматически растягивается при дописывании к ней новых строк или столбцов.
    2. Введённые формулы автоматом будут копироваться на весь столбец.
    3. Шапка такой таблицы автоматически закрепляется при прокрутке, и в ней включаются кнопки фильтра для отбора и сортировки.
    4. На появившейся вкладке «Конструктор» (Design) в такую таблицу можно добавить строку итогов с автоматическим вычислением.

    9. Спарклайны

    Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных. Чтобы их создать, нажмите кнопку «График» (Line) или «Гистограмма» (Columns) в группе «Спарклайны» (Sparklines) на вкладке «Вставка» (Insert). В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

    После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и так далее.

    10. Восстановление несохранённых файлов

    Представьте: вы закрываете отчёт, с которым возились последнюю половину дня, и в появившемся диалоговом окне «Сохранить изменения в файле?» вдруг зачем-то жмёте «Нет». Офис оглашает ваш истошный вопль, но уже поздно: несколько последних часов работы пошли псу под хвост.

    На самом деле есть шанс исправить ситуацию. Если у вас Excel 2010, то нажмите на «Файл» → «Последние» (File → Recent) и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги» (Recover Unsaved Workbooks).

    В Excel 2013 путь немного другой: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги» (File — Properties — Recover Unsaved Workbooks).

    В последующих версиях Excel следует открывать «Файл» → «Сведения» → «Управление книгой».

    Откроется специальная папка из недр Microsoft Office, куда на такой случай сохраняются временные копии всех созданных или изменённых, но несохранённых книг.

    11. Сравнение двух диапазонов на отличия и совпадения

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

    1. Выделите оба сравниваемых столбца (удерживая клавишу Ctrl).
    2. Выберите на вкладке «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» (Home → Conditional formatting → Highlight Cell Rules → Duplicate Values).
    3. Выберите вариант «Уникальные» (Unique) в раскрывающемся списке.

    12. Подбор (подгонка) результатов расчёта под нужные значения

    Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом: всего-то пара десятков итераций «недолёт — перелёт» — и вот оно, долгожданное попадание!

    Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Данные» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert → What If Analysis → Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

    Если этот обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

    Источник: lifehacker.ru

    10 формул Excel, которые пригодятся каждому

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

    Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

    С помощью формулы вы можете:

    • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
    • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
    • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

    Англоязычный вариант: =COUNT(A1:A10)

    Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

    СЧЁТЗ

    Англоязычный вариант: =COUNTA(A1:A10)

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

    ДЛСТР

    Англоязычный вариант: =LEN(A1)

    Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

    СЖПРОБЕЛЫ

    Англоязычный вариант: =TRIM(A1)

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

    Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

    ЛЕВСИМВ, ПСТР и ПРАВСИМВ

    =ЛЕВСИМВ(адрес_ячейки; количество знаков)

    =ПРАВСИМВ(адрес_ячейки; количество знаков)

    =ПСТР(адрес_ячейки; начальное число; число знаков)

    Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

    Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

    Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

    Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

    Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

    Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

    Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

    1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
    2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
    • B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
    • A1:B13 = таблица, в которой находится искомое значение.
    • 2 – номер столбца, где находится искомое значение.
    • ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

    Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

    Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

    Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

    Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».

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

    =ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”)

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

    СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

    Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

    =СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

    Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

    Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

    Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

    Используя функции на предыдущем примере, мы можем узнать:

    СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

    СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

    СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

    Конкатенация

    Формула: =(ячейка1&” “&ячейка2)

    За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&” “&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между ” “, чтобы между объединёнными данными появился пробел.

    Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;” “; B1) или в англоязычном варианте =concatenate(A1;” “; B1).

    Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

    Источник: blog.teachmeplease.ru

    Как работать в Excel с таблицами для чайников: пошаговая инструкция

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

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

    Как создать таблицу в Excel для чайников

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

    Посмотрите внимательно на рабочий лист табличного процессора:

    Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

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

    Как выделить столбец и строку

    Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

    Для выделения строки – по названию строки (по цифре).

    Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

    Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

    Как изменить границы ячеек

    Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

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

    Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

    Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

    Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

    Для столбцов такой метод не актуален. Нажимаем «Формат» – «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» – «Ширина столбца» – вводим заданный программой показатель (как правило это 8,43 – количество символов шрифта Calibri с размером в 11 пунктов). ОК.

    Как вставить столбец или строку

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

    Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+”=”).

    Отмечаем «столбец» и жмем ОК.

    Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+”=”.

    Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

    Пошаговое создание таблицы с формулами

    1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
    2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
    3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
    4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

    Теперь при печати границы столбцов и строк будут видны.

    С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

    Как создать таблицу в Excel: пошаговая инструкция

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

    Сделаем «умную» (динамическую) таблицу:

    1. Переходим на вкладку «Вставка» – инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
    2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

    Как работать с таблицей в Excel

    С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» – «Конструктор».

    Здесь мы можем дать имя таблице, изменить размер.

    Доступны различные стили, возможность преобразовать таблицу в обычный диапазон или сводный отчет.

    Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

    1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
    2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
    3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
    4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+”=”).

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

    Иногда пользователю приходится работать с огромными таблицами. Чтобы посмотреть итоги, нужно пролистать не одну тысячу строк. Удалить строки – не вариант (данные впоследствии понадобятся). Но можно скрыть. Для этой цели воспользуйтесь числовыми фильтрами (картинка выше). Убираете галочки напротив тех значений, которые должны быть спрятаны.

    Источник: exceltable.com

    Как работать в программе Excel: пошаговая инструкция для чайников

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

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

    Работа в Excel с формулами и другими функциями нужна для:

    1. организации бухгалтерского учета;
    2. создания бланков документации;
    3. выполнения вычислений;
    4. анализа данных с помощью диаграмм.

    Для начинающих знание возможностей этой программы поможет с решением различных задач.

    Умения пригодятся при работе с такими объектами:

    • журналы хозяйственных операций;
    • счета-фактуры;
    • расчетно-платежные ведомости
    • накладные и прочее.

    Начальная стадия работы

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

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

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

    Введенная информация сохранится только после нажатия клавиши Enter.

    Работать в программе Excel с данными ячеек легко:

    1. Удаление неверно введенного символа выполняется с помощью клавиши BackSpacе (если данные еще не сохранены). После нажатия кнопки Enter редактировать информацию можно только после повторной активации ячейки. При этом введенные ранее в это поле данные удалятся.
    2. При желании частично изменить текст или числовые значения активируйте поле двойным щелчком. В результате этого появится курсор. Установите его в нужное место и, удалив неверную часть данных кнопкой BackSpacе (удаление знака слева от курсора) или Delete (удаления знака справа от курсора).

    Работа с лишними пробелами

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

    Источник: pcyk.ru