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

Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизиро…

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

Советы по структурированию информации

Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:

• Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
• Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
• Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
• В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
• Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.

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

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

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

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

1. Выбрать инструмент «Сетка по границе рисунка» при нажатии на пиктограмму «Границы».
2. При зажатой левой кнопке мыши (ЛКМ) перетащить указатель по обозначенным линиям, в результате чего появляется сетка. Таблица создается, пока нажата ЛКМ.

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

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

Область таблицы

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

1. Выделить требуемый диапазон.
2. В MS Excel 2013-2019 на вкладке «Главная» кликнуть на пиктограмму «Форматировать как таблицу».
3. При раскрытии выпадающего меню выбрать понравившийся стиль.

Кнопка «Таблица» на панели быстрого доступа

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

1. Активировать интервал ячеек, необходимых для работы.
2. Перейти в меню «Вставка».
3. Найти пиктограмму «Таблицы»:

• В MS Excel 2007 кликнуть на пиктограмму. В появившемся диалоговом окне отметить или убрать переключатель пункта «Таблица с заголовками». Нажать ОК.
• В MS Excel 2016 нажать пиктограмму и выбрать пункт «Таблица». Указать диапазон ячеек через выделение мышкой или ручное прописывание адресов ячеек. Нажать ОК.

Примечание: для создания объекта используют сочетание клавиш CTRL + T.

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

Диапазон ячеек

Работа с числовой информацией подразумевает применение функций, в которых указывается интервал (диапазон ячеек). Под диапазоном справочная литература определяет множество клеток электронной таблицы, в совокупности образующих единый прямоугольник (А1:С9).

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

Заполнение данными

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

• Для заполнения необходимо активировать ячейку и начать вписывать информацию.
• Для редактирования ячейки дважды кликнуть на ней или активировать редактируемую ячейку и нажать F2.
• При раскрытии стрелок в строке заголовка структурированной информации MS Excel можно отфильтровать имеющуюся информацию.
• При выборе стиля форматирования объекта MS Excel автоматически выбрать опцию черезстрочного выделения.
• Вкладка «Конструктор» (блок «Свойства») позволяет изменить имя таблицы.
• Для увеличения диапазона рядов и колонок с последующим наполнением информацией: активировать кнопку «Изменить размер таблицы» на вкладке «Конструктор», новые ячейки автоматически приобретают заданный формат объекта, или выделить последнюю ячейку таблицы со значением перед итоговой строкой и протягивает ее вниз. Итоговая строка останется неизменной.
  Расчет проводится по мере заполнения объекта.

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

Сводная таблица

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

1. Структурировать объект и указать сведения.
2. Перейти в меню «Вставка» и выбрать пиктограмму: в MS Excel 2007 – «Сводная таблица»; в MS Excel 2013-2019 – «Таблицы – Сводная таблица».
3. При появлении окна «Создание сводной таблицы» активировать строку ввода диапазона, устанавливая курсор.
4. Выбрать диапазон и нажать ОК.

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

5. При появлении боковой панели для настройки объекта перенести категории в нужные области или включить переключатели («галочки»).

Созданная сводка автоматически подсчитывает итоги по каждому столбцу.

Рекомендуемые сводные таблицы

Поздние версии MS Excel предлагают воспользоваться опцией «Рекомендуемые сводные таблицы». Подобная вариация анализа информации применяется в случаях невозможности правильного подбора полей для строк и столбцов.

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

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

Готовые шаблоны в Excel 2016

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

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

Чтобы воспользоваться шаблонами:

1. Выбирать понравившийся образец.
2. Нажать «Создать».
3. Заполнить созданный объект в соответствии с продуманной структурой.

Оформление

Экстерьер объекта – важный параметр. Поэтому пользователь изучает не только, как построить таблицу в Excel, но и как акцентировать внимание на конкретном элементе.

Создание заголовка

Дана таблица, нарисованная посредством инструмента «Границы». Для создания заголовка:

Выделить первую строку, кликнув ЛКМ по численному обозначению строки.

На вкладке «Главная» найти инструмент «Вставить».

Активировать пункт «Вставить строки на лист».

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

Нажать на пиктограмму «Объединить» и выбрать первый пункт.

Задать название в ячейке.

Изменение высоты строки

Обычно высота строки заголовка больше первоначально заданной. Корректировка высоты строки:

• Нажать правой кнопкой мыши (ПКМ) по численному обозначению строки и активировать «Высота строки». В появившемся окне указать величину строки заголовка и нажать ОК.
  
• Или перевести курсор на границу между первыми двумя строками. При зажатой ЛКМ оттянуть нижнюю границу ряда вниз до определенного уровня.

Выравнивание текста

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

Изменение стиля

Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.

Пользователь может воспользоваться пиктограммой «Стили». Для этого выбирает диапазон ячеек и применяет понравившийся стиль.

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

Для добавления строк, столбцов и ячеек:

• выделить строку или столбец, перед которым вставляется объект;
• активировать пиктограмму «Вставить» на панели инструментов;
• выбрать конкретную опцию.

Удаление элементов

Для удаления объектов на листе MS Excel пользователь активирует аналогичную кнопку на панели инструментов, предварительно выделив строку, столбец, ячейку.

Заливка ячеек

Для задания фона ячейки, строки или столбца:

• выделить диапазон;
• найти на панели инструментов пиктограмму «Цвет заливки»;
• выбрать понравившийся цвет.

II способ

• вызвать «Формат ячеек» через ПКМ;
• перейти на вкладку «Заливка»;
• выбрать цвет, способы заливки, узор и цвет узора.

III способ

• щелкнуть на стрелочку в блоке «Шрифт»;
• перейти на вкладку «Заливка»;
• выбрать понравившийся стиль.

Формат элементов

На панели инструментов находится пиктограмма «Формат». Опция помогает задать размер ячеек, видимость, упорядочить листы и защитить лист.

Формат содержимого

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

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

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

Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».

Для задания формулы:

• активировать ячейку, где будет рассчитываться формула;
• открыть «Мастер формул»;

или

• написать формулу самостоятельно в строке формул и нажимает Enter;

или

• применить и активирует плавающие подсказки.

На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:

• выделить диапазон;
• активировать пиктограмму.

Использование графики

Для вставки изображения в ячейку:

1. Выделить конкретную ячейку.
2. Перейти в меню «Вставка – Иллюстрации – Рисунки» или «Вставка – Рисунок».
3. Указать путь к изображению.
4. Подтвердить выбор через нажатие на «Вставить».

Инструментарий MS Excel поможет пользователю создать и отформатировать таблицу вручную и автоматически.

Как сделать таблицу в Excel за 5 минут

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

По-простому

По-простому, но красиво

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

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

Итак, нам понадобятся: компьютер, Excel и 5 минут времени. Для начала вам нужно включить компьютер и перейти в программу Эксель (90% дела будет сделано).

Если нет компьютера или программы, то сорри, миссия провалена.

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

Здесь есть строки и столбцы (в принципе как и у любой нормальной таблицы) — это номер один, что вам нужно знать про Эксель.

Работа в программе происходит на листах, например сейчас вы находитесь на первом листе. Листов можно создавать много — это номер два, что вам нужно знать про Эксель.

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

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

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

Шаг 1. Введём названия столбцов и строк и заполним таблицу данными:

Шаг 2. Зададим границы таблицы:

Вот и всё, таблица готова, поздравляю вас с хорошей работой, теперь можно и отдохнуть! Или нет?

Внимание! Сейчас вылетит фишка!

Для того чтобы быстро форматировать границы таблицы можно воспользоваться следующим манёвром:

• Выделяем одну из ячеек внутри таблицы (в первом столбце — это важно).
• Нажимаем комбинацию клавиш CTRL+A (таблица должна выделиться)
• Нажимаем решетку (см. скрин выше) и выбираем «Все границы»

Кстати, я думаю вы же делаете табличку не просто так (для красоты), а для работы. Это значит вам необходимо хоть как-то научиться манипулировать данными внутри неё.

Поэтому покажу пару самых часто используемых приёмов.

Приём первый — фильтры в таблицах Эксель

Фильтры штука очень полезная. Фильтры в Excel нужны для того чтобы сортировать данные и/или быстро просматривать только нужные данные (фильтруя/убирая из поля зрения не нужные)

Чтобы воспользоваться этим чудо-инструментом, нужно выделить всю таблицу (например используя CTRL+A), затем вверху справа выбрать «Сортировка и фильтр» и в выпадающем меню выбрать «Фильтр».

Получается вот такая красота, покликайте на эти штуки и узнаете на что они способны!

Приём второй — автосумма в Excel

Например, вы заполнили таблицу данными и вам оперативно нужно посчитать сумму определённого столбца или строки. Нет проблем, выделяете столбец или строку и нажимаете «Автосумма».

Ба-бам, рядом появится еще одна ячейка с суммой.

Магия вне Хогвартса удалась!

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

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

Поэтому! Давайте сразу расскажу как добавлять/убавлять строки/столбцы в эту самую таблицу. Делается это элементарнейшим образом.

Выделяем строку/столбец, перед которыми нужно добавить новую строку или столбец и жмем кнопку «Вставить» (как показано на скрине), затем выбираем то что мы хотим вставить.

Этот же манёвр можно сделать более элегантно и после выделения, например, строки, нажмите «CTRL + ПЛЮС».

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

Почему почти? – спросите вы. Да потому что гуру должен владеть несколькими способами достижения результата.

Хотите стать гуру, тогда читайте дальше.

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

Сделать это невероятно легко, таблица создаётся буквально в 4 шага!

Итак. Переходим в вкладку «Вставка» – выделяем область таблицы – нажимаем на «Таблицу» – нажимаем «Ок»

иииии всё! Готово! Должна получиться красивая табличка, да еще и сразу с фильтрами, попробуйте, вам понравится!

Видео о том как сделать таблицу в Excel

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

Итоги сего повествования

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

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

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

Автор: Павел Истомин

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

Как работать в экселе с таблицами для чайников. Рабочая область. Формат ячеек. Удаление и добавление строк/столбцов. Работа с формулами. Сортировка данных.

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

Знакомимся с рабочей областью

Окно табличного редактора Эксель содержит 4 основных элемента:

• столбцы. В процессе работы их можно добавлять, удалять, объединять, разбивать, расширять. Столбцы обозначаются латинским алфавитом A,B,C, и т.д.;
• строки. Действия со строками идентичны действиям со столбцами. Обозначаются цифрами 1,2,3, и т.д.;
• меню. В нем расположены все доступные в редакторе функции. Переключая закладки, пользователь выбирает необходимые ему действия.;
• ячейка. Содержит данные – текст  или цифры. Данные копируют, составляют по ним формулы, текст видоизменяют, как в Ворде. Каждой ячейке присвоены координаты по номеру строки и столбца. Координаты данной – 5F.

Действия со столбцами и строками

Вариант А – просто растянем по ширине. Наводим курсор мышки на границу столбцов А и В до появления специфического курсора и тянем вправо.

Вариант Б – воспользоваться специальной функцией в меню: один раз кликнуть по ячейке – нажать кнопку «Перенос текста».

Текст распределился равномерно. Но для одного слова это непрактично. Чаще этой функцией пользуются для распределения предложения.

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

Автоподбор

Выделить столбец – в меню найти «Формат» – Автоподбор.

Массовое изменение ширины ячеек

Выделяем область: навести курсор мышки на столбец – зажать ЛКМ – провести по всем столбцам, которые нужно выделить. Если столбцы стоят не подряд, кликаем на первый, зажимаем Ctrl и кликаем по одному разу на необходимые.

Затем, у любого из выделенных столбиков растягиваем ширину по методу А: во всей выделенной области ширина изменяется пропорционально.

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

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

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

Аналогичные действия для вставки строки.

Чтобы удалить строку или ячейку, нужно: выделить столбец – ПКМ по выделенной области – Удалить.

Как задать формат ячеек

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

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

Затем ПКМ по выделенной области – «Формат ячеек». В закладках находим «Число», выбираем Числовой формат, определяем количество символов после запятой, жмем Ок.

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

Как убрать числа после запятой

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

Как создать формулу

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

Кликаем по ячейке, в которую вписывается формула, ставим курсор в строку для формулы.

В строке формулы ставим знак «=», кликаем по ячейке с данными, ставим знак «*», кликаем по ячейке со вторыми данными, нажимаем Enter.

В результате произошло математическое действие умножения, при котором перемножились данные из В2 и С2, а результат записался в D2.

Копирование формулы

Выделить ячейку с формулой – навести курсор на черный квадратик в правом нижнем углу границы до появления черного крестика – зажать ЛКМ – растянуть область на весь столбец.

Для проверки кликните на любую ячейку столбца общей суммы. В строке формулы появится формула вычисления для этой ячейки.

Объединение ячеек

Выделить ячейки, которые нужно объединить – найти в меню кнопку «Объединить и поместить в центре» – нажать на нее.

Для снятия объединения: выделить объединенную область – нажать на кнопку «Объединить и поместить в центре».

Метод работает только для объединенных ячеек.

 

Создание и ведение таблиц Excel

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

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

Создание таблицы 

1. Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
2. В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table]. 

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

 

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

4.  ОК.

Присвоение имени таблице 

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя. 

1. Выделить ячейку таблицы.
2. На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

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

1. Выделить ячейку таблицы.
2. На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

 

Вычисления в таблицах

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

1. На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

2. В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию. 

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

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

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

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

1. Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
2. В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
3. OK.

Преобразование таблицы в обычный диапазон 

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

1. На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
2. Выбрать вкладку Преобразовать в диапазон [Convert to Range].

3. Нажать на кнопку Да [Yes].

Общие сведения о таблицах Excel

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

Элементы таблиц Microsoft Excel

Таблица может включать указанные ниже элементы.

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

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

• Чередование строк.    Альтернативная заливка или чередование в строках помогает лучше отличать данные.

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

• Строка итогов    После добавления строки итогов в таблицу Excel предлагает вам раскрывающийся список автосуммирования для выбора таких функций, как сумм, СРЗНАЧ и т. д. Если выбрать один из этих параметров, таблица автоматически преобразует их в функцию промежуточных итогов, в результате чего строки, скрытые с помощью фильтра, будут игнорироваться по умолчанию. Если вы хотите включить в вычисления скрытые строки, вы можете изменить аргументы функции промежуточные .

  Дополнительные сведения можно также найти в разделе Общие данные в таблице Excel.

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

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

Создание таблицы

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

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

1. Выделите ячейку или диапазон данных.

2. На вкладке Главная выберите команду Форматировать как таблицу.

3. Выберите стиль таблицы.

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

Также посмотрите видеоролик о создании таблиц в Excel.

Эффективное использование данных в таблице

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

• Использование структурированных ссылок.    Вместо использования ссылок на ячейки, таких как A1 и R1C1, можно использовать структурированные ссылки, которые указывают на имена таблиц в формуле. Дополнительные сведения можно найти в разделе использование структурированных ссылок в таблицах Excel.

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

Экспорт таблицы Excel на сайт SharePoint

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

См. также

Форматирование таблицы Excel

проблемы совместимости таблиц Excel

Работа с таблицами в excel для чайников

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

Знакомство с электронными таблицами

Каждая клетка электронной таблицы называется ячейкой. У которой есть собственное обозначение – имя. Как правило, имя ячейки задается по имени столбца и номеру строки на пересечении которых она расположена. Например, G5,E8.

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

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

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

Формулой является запись, начинающаяся со знака «=»(равно), содержащая адреса ячеек, знаки арифметических операций, числа и функции.

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

Внешний вид интерфейса Excel 2016

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

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

Внешний вид интерфейса Excel 2016

Блок №2 (выделен жёлтым цветом) содержит ленту вкладок с инструментами.

Блок №3 (выделен зелёным цветом) слева содержит поле, в котором отображается имя (адрес) активной ячейки. Далее идут три кнопки режима редактирования формулы:

• крестик – отказ от ввода в текущую ячейку – удаление ее содержимого;
• галочка – завершение редактирования содержимого ячейки;
• Fx – вызов мастера функций для заполнения ячейки встроенными функциями табличного процессора.

Третье поле (длинное) служит для отображения содержимого активной ячейки или расчетной формулы.

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

Блок №4 (выделен фиолетовым цветом) содержит рабочую область (таблицу). Столбцы таблицы обозначают латинскими буквами в алфавитном порядке. Строки нумеруются арабскими цифрами по возрастанию. Внизу рабочей области отображаются ярлыки листов, которые можно добавлять кнопкой «плюс».

Блок №5 содержит строку состояния, в правой части которой расположены кнопки управления режимами отображения таблиц: обычный, разметка страницы, страничный и масштаб содержимого листа.

Видео:Сложение чисел в Excel

Как создать формулу в Excel

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

Формирование чека стоимости покупок

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

Вычисление стоимости товара в Excel

В ячейке E5 задана формула =C5*D5, которая перемножает количество единиц товара и цену за единицу продукта. Для остальных четырех строк будут подобные формулы, только в них поменяется та часть адреса ячейки, которая отвечает за номер строки. Например, шоколад находится в девятой строке таблицы и формула для расчета стоимости в ячейке E9, соответственно примет вид =C9*D9. Формулы можно вводить для каждой строки вручную, но можно провести копирование формулы из ячейки E5 вниз до E9(включительно).

Копирование формулы в Excel

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

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

Для расчета итоговой стоимости, необходимо суммировать содержимое ячеек диапазона E5:E9. Для этого в ячейку E10 запишем формулу =E5+E6+E7+E8+E9.

Для вычисления сдачи в ячейку E12 записываем формулу =E11-E10. В ячейку E11 вписываем число наличных денег.

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

Расчет коммунальных услуг: электричество, газ, вода

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

 

Дополнительная информация:

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

формул Excel с примерами

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

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

В этом руководстве мы изучим основы создания и использования формул в Excel. А поскольку один из самых эффективных способов обучения — это практика, мы также обсудим ряд примеров формул, чтобы упростить понимание. Вот список тем, которые мы рассмотрим:

Формулы Microsoft Excel — основы

В MS Excel формулы — это уравнения, которые выполняют различные вычисления на ваших листах.Хотя за прошедшие годы Microsoft представила несколько новых функций, концепция формул электронной таблицы Excel одинакова во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007 и ниже.

• Все формулы Excel начинаются со знака равенства (=).
• После символа равенства введите либо вычисление , либо функцию . Например, чтобы сложить значения в ячейках с B1 по B5, вы можете:
  • Введите уравнение целиком: = B1 + B2 + B3 + B4 + B5
  • Используйте функцию СУММ: = СУММ (B1: B5)
• Нажмите клавишу Enter, чтобы заполнить формулу.Выполнено!

Элементы формул Microsoft Excel

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

• Константы — числа или текстовые значения, которые вы вводите непосредственно в формуле, например = 2 * 3.
• Ссылки на ячейки — ссылка на ячейку, содержащую значение, которое вы хотите использовать в формуле Excel, e.грамм. = СУММ (A1, A2, B5) .

  Чтобы обратиться к данным в двух или более смежных ячейках, используйте ссылку на диапазон , например A1: A5. Например, чтобы суммировать значения во всех ячейках от A1 до A5 включительно, используйте следующую формулу:
  = СУММ (A1: A5) .

• Имена — определенное имя для диапазона ячеек, константы, таблицы или функции, например = SUM (my_name) .
• Функции — стандартные формулы в Excel, которые выполняют вычисления с использованием значений, указанных в их аргументах.
• Операторы — специальные символы, указывающие тип операции или расчета, которые необходимо выполнить.

Операторы в формулах листа Excel

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

Использование арифметических операторов в формулах Excel

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

Оператор	Значение	Пример формулы
+ (знак плюса)	Дополнение	= A2 + B2
— (знак минус)	Вычитание Отрицание (меняя знак)	= A2-B2 = -A2 (меняет знак значения в A2)
* (звездочка)	Умножение	= A2 * B2
/ (косая черта)	Дивизион	= A2 / B2
% (знак процента)	Процент	= A2 * 10% (возвращает 10% значения в A2)
^ (каретка)	Экспоненциальная (степень)	= A2 ^ 3 (увеличивает число в A2 в степени 3)

Например, если у вас есть цена товара в ячейке A2 и НДС в ячейке B2, вы можете рассчитать сумму НДС, используя следующую процентную формулу: = A2 * B2

Операторы сравнения в формулах Excel

В формулах Microsoft Excel сравнение или логических , операторов используются для сравнения двух значений.Результатом сравнения всегда является логическое значение ИСТИНА или ЛОЖЬ. В Excel доступны следующие логические операторы:

Оператор сравнения	Значение	Пример формулы
=	Равно	= A2 = B2
<>	Не равно	= A2 <> B2
>	Больше	= A2> B2
<	Менее	= A2
> =	Больше или равно	= A2> = B2
<=	Меньше или равно	= A2 <= B2

Например, формула = A1 = B1 возвращает ИСТИНА, если ячейки A1 и B1 содержат одно и то же значение (число, текст или дату), в противном случае - ЛОЖЬ.

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

Оператор конкатенации текста

Оператором конкатенации текста в Excel является символ амперсанда ( и ). Вы можете использовать его для объединения двух или более текстовых строк в одну строку.

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

= A1 & "" & B1

В приведенной выше формуле мы объединяем пробел между ними, чтобы числа лучше читались:

Тот же результат может быть достигнут с помощью функции СЦЕПИТЬ, а в следующем руководстве объясняются все детали: Как объединить текстовые строки, ячейки и столбцы в Excel.

Справочные операторы в формулах и функциях Excel

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

Двоеточие (:) - это оператор диапазона , который позволяет создать одну ссылку для нескольких ячеек, расположенных между двумя указанными вами ячейками.

Например, диапазон A1: A00 включает 100 ячеек от A1 до A100. Чтобы найти среднее значение из этих 100 ячеек, используйте следующую формулу: = СРЕДНЕЕ (A1: A00)

Вы также можете ссылаться на весь столбец (A: A) или на всю строку (1: 1).Например, следующая формула находит сумму всех чисел в столбце A: = СУММ (A: A) . Узнайте больше о ссылках на весь столбец и целую строку.

Запятая (, ) - используется для разделения аргументов в формулах электронных таблиц Excel. Например, формула = ЕСЛИ (A1> 0, «хорошо», «плохо») читается следующим образом: если A1 больше нуля, вернуть «хорошо», иначе «плохо».

Примечание. Запятая - это значение по умолчанию Разделитель списка в Северной Америке и некоторых других странах.В европейских странах запятая зарезервирована как десятичный символ , а разделитель списка обычно устанавливается на точку с запятой (;). В этом случае вам необходимо разделить аргументы функции точкой с запятой, например = ЕСЛИ (A1> 0; «хорошо»; «плохо») .

Итак, если вы пытаетесь создать формулу на своем листе, но Excel не принимает ее и выдает ошибку «недопустимая формула», перейдите в региональные настройки ( Панель управления> Регион и язык > Дополнительно Settings) и проверьте, какой символ установлен там как List Separator .Это тот символ, который вам нужно использовать для разделения аргументов в формулах Excel.

Пробел - это оператор пересечения, который позволяет получить ячейку (я), общую для двух указанных вами ссылок. Например, если у вас есть список элементов в столбце A и некоторые связанные данные в других столбцах, вы можете получить значение на пересечении
данного столбца и строки, используя следующую формулу: = B3: D3 C2: C4

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

Типы формул Excel

Формулы, которые вы создаете в электронных таблицах Excel, могут быть простыми или сложными:

• Простые формулы Excel выполняют всего одну математическую операцию, например = 10 * 5 или = СУММ (A1: A10)
• Сложный (расширенный) Формулы Excel включают более одного вычисления, например = 10 * 5 + 20 или = СУММ (A1: A10) / 2

Далее в этом руководстве вы найдете подробные инструкции по созданию обоих типов формул электронной таблицы Excel.

Как создавать формулы в Excel

Как уже упоминалось, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как можно составлять разные формулы в Excel.

Как создавать простые формулы в Excel

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

Как создавать сложные формулы в Excel

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

Создание сложных формул с константами и математическими операторами

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

.

• Математические операции в скобках
• Степень (экспоненциальные вычисления)
• Умножение и деление, в зависимости от того, что наступит раньше в формуле
• Сложение и вычитание, в зависимости от того, что наступит раньше в формуле

Например, вы можете использовать следующие формулы для расчета суммы и комиссии:

А теперь давайте разберем эти формулы, чтобы увидеть, как Microsoft Excel их вычисляет:

Итоговая формула: = $ B2 * $ D2 + $ B2 * $ D2 * $ C2

• 1-е умножение: $ B2 * $ D2 (цена * кол-во.= сумма)
• 2 ^nd и 3 ^rd умножения: $ B2 * $ D2 * $ C2 (цена * кол-во *% НДС = сумма НДС)
• Дополнение: сумма + сумма НДС = итого

Формула комиссии: = ($ B2 * $ D2 + $ B2 * $ D2 * $ C2) * 10%

Для расчета 10% комиссии нужно умножить сумму на 10%, чтобы вы заключили предыдущий расчет в скобки и получили желаемый результат.

Конечно, ничто не мешает умножить сумму, уже рассчитанную в столбце E, на 10%, в этом случае формула сводится к простому вычислению = E2 * 10% .Однако на больших листах имеет смысл писать независимо вычисляемые формулы, чтобы удаление столбца с одной формулой не привело к поломке других.

Формулы Excel с вложенными функциями

В формулах Microsoft Excel вложение одной функции в другую означает использование одной функции в качестве аргумента другой функции. В современных версиях Excel 2016, 2013, 2010 и 2010 можно использовать до 64 вложенных функций. В более старых версиях Excel 2003 и ниже разрешены только до 7 уровней функций.

Вот очень простой пример вложенной формулы Excel, которая включает функцию СУММ для нахождения итога и функцию ОКРУГЛ для округления этого числа до ближайшего целого (0 знаков после запятой):

= ОКРУГЛ (СУММ (B2: B6), 0)

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

Подробное объяснение синтаксиса вложенных IF и примеров расширенных формул см. В следующем руководстве: Использование вложенных функций IF в Excel.

Формулы массива в Excel

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

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

Предположим, у вас есть 2 столбца чисел, столбец A и B. И вы хотите знать, во сколько раз столбец B больше или равен столбцу A, если значение в столбце B больше 0.

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

= СУММ ((B2: B10> = A2: A10) * (B2: B10> 0))

Примечание. Чтобы правильно ввести формулу массива Excel, необходимо нажать Ctrl + Shift + Enter вместо обычного штриха Enter.

Чтобы узнать больше о формулах массива Excel, см. Следующие руководства:

Пользовательские функции Excel

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

Такие пользовательские функции называются Пользовательскими функциями (UDF), и они особенно полезны для сложных математических или инженерных расчетов. Как и макросы, пользовательские функции написаны на языке VBA (Visual Basic для приложений). Например, вы можете просмотреть и загрузить пользовательские функции, созданные нашей командой для подсчета и суммирования ячеек по цвету.

Абсолютные, относительные и смешанные ссылки на ячейки в формулах Excel

В Excel существует 3 типа ссылок на ячейки: абсолютные ($ A $ 1), относительные (A1) и смешанные ($ A1 или A $ 1).Все три приведенные выше ссылки относятся к одной и той же ячейке, а знак доллара ($) используется только для одной цели - он сообщает Microsoft Excel, следует ли изменять или не изменять ссылки на ячейки, когда формула перемещается или копируется в другие ячейки.

Абсолютная ссылка на ячейку ($ A $ 1) - знак $ перед координатами строки и столбца делает ссылку статической и позволяет копировать формулу без изменения ссылок .

Относительная ссылка на ячейку (A1) - ссылка на ячейку без изменения знака $ в зависимости от относительного положения строк и столбцов в электронной таблице.

Ссылка на смешанную ячейку - может быть 2-х типов:

• Абсолютный столбец и относительная строка ($ A1) - знак $ перед буквой столбца блокирует ссылку на указанный столбец, поэтому столбец никогда не изменяется. Относительная ссылка на строку без знака доллара изменяется в зависимости от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка (A $ 1) - ссылка на строку, заблокированная с помощью $, не изменяется, а ссылка на столбец изменяется.

На следующем изображении показано, как на практике работают разные типы ссылок.

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

Советы и быстрые клавиши для работы с формулами Excel

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

• Для переключения между абсолютными, относительными и смешанными ссылками в формуле используйте клавишу F4, как показано в разделе «Переключение между типами ссылок в Excel».
• Чтобы просмотреть все формулы на листе, нажмите кнопку Показать формулы на вкладке Формулы > Аудит формул или нажмите сочетание клавиш Ctrl + ~.
От
• до измените формулу , нажмите F2 или дважды щелкните ячейку или щелкните строку формул.
• Для отладки формул в Excel выберите часть формулы и нажмите F9. Это позволит вам увидеть фактические значения за ссылками на ячейки.
• В скопируйте формулу в все ячейки в столбце , введите формулу в первую ячейку, выберите эту ячейку и наведите курсор на маленький квадрат в правом нижнем углу, пока он не изменится на черный крест (который называется
  дескриптором заполнения). Дважды щелкните этот крестик, и формула будет скопирована через весь столбец.
• Чтобы преобразовать формулы в значения , выберите все ячейки с формулами, которые вы хотите преобразовать, нажмите Ctrl + C, чтобы скопировать эти формулы, затем нажмите Shift + F10, затем нажмите V, а затем нажмите Enter. Shift + F10 + V - это ярлык для специальной вставки Excel - значения только . Если вы не уверены, что запомните этот ярлык, просто нажмите обычный ярлык для вставки Ctrl + V, щелкните маленькую стрелку справа от кнопки Вставить , чтобы открыть раскрывающийся список, и выберите Вставить значения .В разделе Как заменить формулы на их значения в Excel.

Формулы Microsoft Excel с примерами

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

Процентная формула Excel

Формулы сумм Excel

Формула счета в Excel

Формула среднего в Excel

Формулы даты Excel

• Как преобразовать текст на дату в Excel - несколько формул для преобразования текста на дату.
• Как преобразовать дату Excel в текст - формула, чтобы преобразовать дату в текстовую строку в указанном формате.
• Примеры формул Excel DATE - как получить серийный номер, представляющий дату, сложить и вычесть даты в Excel, вернуть дату на основе значений в других ячейках, преобразовать текстовую строку в дату, а также несколько расширенных примеров формул Excel DATE.
• Формулы для вычисления дней в Excel (функции WEEKDAY, DAY) - формулы даты для возврата дня недели от даты, получения количества дней в году, определения количества дней между двумя датами и более.
• Как рассчитать месяцы в Excel (функции МЕСЯЦ и МЕСЯЦ) - примеры формулы даты Excel для извлечения месяца из даты, получения первого и последнего дня месяца, преобразования названия месяца в число и т. Д.
• Расчет номера недели в Excel (функция WEEKNUM) - как использовать формулы даты в Excel, чтобы получить номер недели от даты, преобразовать номер недели в дату, получить номер недели в месяце, значения суммы по номеру недели и т. Д.
• Как складывать и вычитать даты в Excel - формулы даты для сложения и вычитания дней, недель, месяцев и лет.
• Как рассчитать разницу между двумя датами (функция РАЗНДАТ) - Формула даты Excel для расчета разницы между двумя датами в днях, неделях, месяцах или годах.
• Расчет рабочих дней в Excel (WORKDAY и NETWORKDAYS) - использование формулы даты в Excel для расчета рабочих дней с настраиваемыми параметрами выходных и праздничных дней.
• Преобразование даты в год и вычисление возраста по дате рождения - формулы возраста Excel и несколько других формул дат для извлечения года из даты, преобразования даты в месяц и год, определения високосных лет.

Формулы времени в Excel

Формулы Excel для ВПР

Формулы условного форматирования Excel

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

Вас также может заинтересовать

25 удивительных советов и приемов Power Query

Power query - отличный инструмент для преобразования данных! Он позволяет с легкостью импортировать и преобразовывать данные и помогает создавать повторяемые и надежные процедуры для ваших данных.

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

Самое приятное то, что он встроен прямо в Excel 2016 или новее. Это та же технология, которая используется в Power BI, поэтому вы одновременно изучаете два востребованных навыка обработки данных!

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

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

Видеоурок

Удалить шаги до конца

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

На панели окна Applied Steps щелкните правой кнопкой мыши первый шаг, который вы хотите удалить, а затем выберите в меню Удалить до конца .

Будет удален этот шаг и все шаги запроса после этого шага.

Предварительный просмотр или переход к объектам таблицы

Столбец в вашем запросе может содержать объекты таблицы. В этом случае записи строки столбца будут отображать текст Таблица и значок типа данных.

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

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

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

Дважды щелкните, чтобы отредактировать запрос

Редактировать запрос можно несколькими способами, используя панель окна Queries & Connections .

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

Самый быстрый способ - дважды щелкнуть левой кнопкой мыши по запросу. Это откроет редактор запросов для выбранного запроса.

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

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

Обратите внимание, что параметр Properties , находящийся в меню правой кнопки мыши на панели запросов и подключений, откроет окно свойств запроса, а не окно свойств внешних данных, которое необходимо для этого совета.

Это откроет меню «Свойства внешних данных», и вы можете установить флажок Включить номера строк и нажать кнопку Ok .

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

Изменить параметры нагрузки по умолчанию

Когда вы нажимаете кнопку Close & Load для запроса в первый раз, Power Query загружает данные в таблицу Excel в книге и не добавляет данные в модель данных. Это настройки нагрузки по умолчанию в запросе мощности.

Если вы хотите загрузить данные только как соединение или вам нужно загрузить их в модель данных для использования с Power Pivot позже, тогда вам нужно вместо этого выбрать Close & Load To и выбирать эти параметры каждый раз, когда вы создаете новый запрос.

Если вы обнаружите, что большинство создаваемых вами запросов должны быть только подключением или должны загружаться в модель данных, то вы можете изменить настройки загрузки по умолчанию. Перейдите на вкладку Data , затем выберите Get Data , затем откройте Query Options .

В настройках Global Data Load выберите опцию Specify custom default load settings , а затем выберите или отмените выбор нужных опций.

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

Отобразить моноширинный шрифт в редакторе запросов

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

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

Перемещение по столбцам с помощью клавиш со стрелками

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

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

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

Если вы выберете одну ячейку в столбце, вы можете использовать сочетание клавиш Ctrl + Пробел, чтобы выбрать весь столбец. Вы также можете использовать сочетание клавиш Ctrl + A, чтобы выделить всю таблицу. Как в обычном листе Excel!

Вы можете многое сделать с помощью запроса мощности только из пользовательского интерфейса, даже не касаясь кода M, который создается за кулисами.Если вы начнете изучать M-код с помощью расширенного редактора (вкладка View , затем Advanced Editor ), то вы, вероятно, захотите добавить комментарии к своему коду.

Это можно сделать двумя способами.

Вы можете создать однострочный комментарий, начав строку с // двойной косой черты.

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

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

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

Комментарий будет отображаться в строке формул, пока вы не перейдете к другому шагу или другому запросу. Когда вы вернетесь, он больше не будет отображаться в строке формул, но он все равно будет присутствовать в M-коде и отображаться в расширенном редакторе.

Я уже сказал, что комментарии видны только в расширенном редакторе, но есть хитрость, чтобы заставить их отображаться в строке формул.

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

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

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

Вы сможете добавить описание к шагу. Это описание появится как однострочный комментарий над M-кодом для шага в расширенном редакторе.

Вы также можете изменить имя шага из этого окна. Это также изменит ссылки для шага в расширенном редакторе.

Создание последовательного списка

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

= {5..10}

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

= {[Начало] .. [Конец]}

Если столбец «Начало» или «Конец» представляет собой десятичное число, выражение приведет к ошибке.

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

Избегайте чувствительности к корпусу с помощью фильтров

Запрос питания чувствителен к регистру. Это может привести к ошибкам в анализе данных в будущем, если вы не будете осторожны.

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

Этого можно избежать, преобразовав столбец в верхний регистр перед применением каких-либо фильтров.

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

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

Дважды щелкните, чтобы переименовать столбец

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

Переименование столбца с помощью сочетания клавиш

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

Перемещение по сложным зависимостям запросов

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

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

В окне "Зависимости запросов" много чего происходит.

1. Вы сможете увидеть свои источники данных.Это меньшие поля в окне, в которых будет отображаться значок большего размера, указывающий тип исходных данных.
2. Щелкните левой кнопкой мыши по любому элементу, и он станет светло-зеленым вместе со всеми его зависимостями и прецедентами.
3. Щелкните правой кнопкой мыши любой элемент, и появится меню с возможностью центрировать представление на этом элементе или центрировать представление на всех предшествующих и зависимых элементах.
4. Щелкните левой кнопкой мыши и перетащите любую пустую область, чтобы переместить вид.
5. Вы можете изменить макет, чтобы отображать зависимости сверху вниз, снизу вверх, слева направо или справа налево.
6. Вы можете увеличивать или уменьшать масштаб изображения вручную с помощью полосы масштабирования. Другой способ увеличить изображение - использовать колесо прокрутки мыши, если оно у вас есть. Другой вариант - дважды щелкнуть левой кнопкой мыши по любой пустой области, чтобы увеличить масштаб.
7. Вы можете полностью уменьшить масштаб изображения, используя кнопку уменьшения в правом нижнем углу.

Перейти к одному значению

Перейти к любому элементу запроса. Щелкните ячейку правой кнопкой мыши и выберите Drill Down .

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

Включить быструю загрузку данных в Power Query

Быстрее всегда лучше, правда? Если ваши запросы медленные, вы можете включить опцию быстрой загрузки данных, чтобы ускорить их. Перейдите на вкладку Data и нажмите кнопку Get Data , затем откройте Query Options .

Перейдите к настройкам Global Data Load и установите флажок Fast Data Load . На загрузку ваших запросов уйдет меньше времени, но Excel может перестать отвечать во время загрузки.

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

Когда вы импортируете данные в Excel с помощью мощного запроса, Excel угадывает, какой тип данных каждый столбец основан на первых нескольких сотнях строк. Затем Power query автоматически создаст шаги запроса для продвижения первой строки в заголовки столбцов (для файлов CSV) и изменения типов данных.

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

Чтобы открыть параметры запроса, перейдите на вкладку Data , затем Получить данные , затем выберите Параметры запроса .

Перейдите к настройкам Current Workbook Data Load и установите флажок Автоматически определять типы столбцов и заголовки для неструктурированных источников .

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

Изучите библиотеку функций Power Query

Кодовый язык M

Power query является функциональным языком. Это означает, что M-код в основном построен с функциями, которые делают определенные вещи на основе своих входных данных. Написание M-кода в мощном запросе похоже на построение с помощью Lego, есть много разных типов частей Lego, и каждая часть предназначена для определенной цели.

Код

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

Создайте новый пустой запрос, перейдите на вкладку Data и нажмите команду Get Data , затем выберите From Other Sources , затем выберите Blank Query . Теперь в строке формул введите = # shared и нажмите Enter.

Power query затем покажет все элементы, доступные в книге. Это будет включать все функции запроса мощности!

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

1. Отображается имя функции.
2. Есть краткое описание того, что делает функция.
3. Вы можете протестировать функцию на своих данных.
4. Показан синтаксис функций.
5. Здесь показан простой пример, демонстрирующий ввод и вывод функции.

Разделить запрос на две части

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

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

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

Копирование и вставка запросов в новую книгу

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

В окне Queries & Connections старой книги выберите запросы, которые вы хотите скопировать, затем щелкните правой кнопкой мыши и выберите в меню Copy . Теперь перейдите к новой книге и щелкните правой кнопкой мыши в окне Queries & Connections и выберите в меню Paste .

Это скопирует все выбранные запросы вместе со всеми запросами, на которые они ссылаются.

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

Дважды щелкните, чтобы переименовать запрос

Вы можете быстро переименовать любой запрос на панели списка запросов в левой части редактора запросов. Дважды щелкните левой кнопкой мыши запрос, который вы хотите переименовать, затем введите новое имя и нажмите Enter, чтобы подтвердить изменение.

Дважды щелкните, чтобы скрыть или показать ленту редактора запросов

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

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

Отключить обнаружение отношений

Это еще один совет, который потенциально может ускорить выполнение запросов Power Query.

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

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

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

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

DADM_Tools надстройка: По разным причинам некоторые пользователи наших книг по DADM и PMS предпочитают не использовать надстройки Palisade, описанные в этих книгах. Чтобы предоставить еще один в качестве альтернативы я создал свою собственную надстройку под названием DADM_Tools, которая предоставляет большую часть базовый функционал программного обеспечения Palisade. Эта надстройка, написанная на Язык Excels VBA, не используется в книгах, но доступен здесь для свободно.Поскольку это совершенно бесплатно, поддержка недоступна. Однако это очень прост в использовании и совместим с Excel для Windows и Excel для Mac. Вот ссылка на инструкцию: DADM_Tools Help.docx. Вот ссылка на надстройку: DADM_Tools.xlam.

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

о Для пользователей Windows случайные функции: содержится в надстройке особого типа (XLL, который работает только с Excel для Windows 2010 или выше). Сначала прочтите следующее: RandGen Add-In.docx (обновлено 24.01.2019). Затем установите надстройку, запустив файл установки в: RandGenSetup.zip.

о Для пользователей Mac случайные функции находятся в следующая надстройка: Random Функции для Mac.xlam.

Excel учебник: Вот бесплатная версия моего учебника по Excel: Учебник по Excel для Windows.xlsx. (Он предоставляет информацию для обновления до более полной версия под названием ExcelNow !.) Вы также можете скачать версию учебника для Mac: Excel Учебник для Mac.xlsx. Сравнивая их, вы можете увидеть, какие функции в Excel для Windows не включены в Excel для Mac.

Анализ Руководство ToolPak: Это приложение к нашим книгам для тех из вас, кто хотел бы нравится использовать встроенный в Excel надстройку Analysis ToolPak, а не StatTools, для статистического анализа.ZIP-файл содержит PDF-версию руководства и сопутствующие файлы данных: Анализ ToolPak Guide.zip

SolverTable Надстройка: Каждая версия ниже имеет соответствующий файл справки (файл Word), который вы должны прочитать, прежде чем связываться со мной по поводу проблем. Каждый zip-файл ниже содержит только два файла: файл надстройки .xla или .xlam и файл справки Word. Вы должны распаковать оба архива в одну и ту же папку (любую папку по вашему выбору), а затем прочтите файл справки для получения дополнительных инструкций.Следующие два пункта касаются некоторых возможные проблемы у пользователей.

о Для Решатель, поставляемый с Excel для Mac: SolverTable for Mac.zip

( Примечание: Если вы загрузили этот zip-архив до 30.10.2020, скачайте эту версию вместо. Предыдущая версия не распознала, когда модель не имеет возможности решения. Эта версия устраняет проблему.) Эта версия SolverTable была создан для Mac в октябре 2020 года.Суть SolverTable всегда заключалась в что он выполняет несколько непрерывных запусков Solver. По техническим причинам это невозможно в Excel для Mac, что объясняет, почему версия SolverTable никогда не был доступен для Mac. Однако эта новая версия позволяет обойти проблему, прервав запуск Solver. Перед каждым run, появится запрос о том, хотите ли вы выполнить следующий запуск. (Это объясняется более подробно в файле справки, который является частью этого zip-файла.) Правда, перебои в некоторой степени замедляют процесс (помимо того, что Решатель для Mac просто медленный), но это намного лучше, чем создание Несколько Решателей запускают вручную , каждый с новыми входными значениями. Хорошие новости заключается в том, что пользовательский интерфейс и таблицы результатов точно такие же, как у для версии SolverTable для Windows.

о Для Решатель, который поставляется с Excel 2019 или Office 365: Нет новой надстройки SolverTable для любого из них.Насколько я знайте, что SolverTable для Excel 2016 должен нормально работать с любым из них. Я в настоящее время использую его с Office 365 и не сталкивался с какими-либо проблемами.

о Для Решатель, поставляемый с Excel 2016: SolverTable 2016.zip

Эта версия в основном такая же, как и в 2013 году. версия.

о Для Решатель, поставляемый с Excel 2013: SolverTable 2013.zip

Эта версия не была создана, поскольку SolverTable 2010 не будет работать с Excel 2013.Скорее, я внес некоторые технические изменения в программное обеспечение. Наверное, главное изменение в том, что эта версия теперь запускает каждый Решатель запускается из исходного решения в ячейках переменной решения. (В предыдущих версиях он запускал каждый Solver запустить от предыдущий Solver раствор.)

Изменено 05.10.2015, чтобы открыть файл справки в более простой способ (меньше вероятность возникновения ошибки).

о Для Решатель, поставляемый с Excel 2010: SolverTable 2010.почтовый индекс

Изменено 05.10.2015, чтобы открыть файл справки в более простой способ (меньше вероятность возникновения ошибки).

Изменено 26.04.2012 для устранения проблемы с длинным имена рабочих листов. По сути, Excel позволяет имена листов не длиннее, чем 31 символ. SolverTable создает скрытый лист со своими настройками, а Имя листа - это имя листа модели плюс суффикс _STS. Так что если название вашего модельного листа содержит от 28 до 31 символа, это создаст ошибка.SolverTable теперь предупреждает вас перед возникновением ошибки.

Изменено 05.12.2011, чтобы исправить потенциальную проблема именования листов.

Изменено 8.11.2011 для устранения потенциальной проблемы где пользователь ошибочно выбирает метод Simplex LP для нелинейной модели. В предыдущий код в этом случае мог попасть в бесконечный цикл. Аналогичное исправление было сделано (см. ниже) до версий 2007 и 2003 годов. Однако эта версия 2010 г. может работать некорректно в 2007 или 2003 годах из-за незначительного изменения кода Frontline Systems разработала версию Solver 2010 года.

Изменено от 07.10.2010, чтобы исправить возникшую ошибку когда пользователь по ошибке запустил SolverTable с листа STS (не с листа модели)

Изменено 24.09.2010, чтобы сделать его совместимым с опция GRG Nonlinear Multistart

Изменено 3 сентября 2010 г. для совместимости с 64-разрядная версия Office 2010.

о Для Решатель, поставляемый с Excel 2007: SolverTable 2007.zip

Изменено 26.04.2012, см. Пункт 2 выше. версия

2010 года

Изменено 05.12.2011, см. Пункт 3 выше версия

2010 года

Изменено 8.11.2011, см. Пункт 4 выше. версия

2010 года

Изменено от 07.10.2010, чтобы исправить возникшую ошибку когда пользователь по ошибке запустил SolverTable с листа STS (не с листа модели)

о SolverTable fix: Для тех из вас, у кого проблемы с SolverTable, вот инструкции по возможному исправлению: Исправление SolverTable.docx.

о Отсутствует Ссылка на решатель: Вот еще один возможное исправление для SolverTable: отсутствует Справочник по решателю.docx

о SolverTable совет для международных пользователей: Пользователь из-за пределов США обнаружил, почему его SolverTable не работал. Проблема была в числовых настройках (десятичный символы и разделители списков), и исправление заключалось в том, чтобы изменить их в Windows настройки. Я не уверен, насколько распространенной может быть эта проблема, но если вы на улице США и ваш SolverTable не работает, стоит попробовать.

о Странный Задача SolverTable: Рейнольд Байерс и его студенты в штате Аризона обнаружили, что в простой Модель с целочисленными ограничениями, SolverTable дал несколько неоптимальные решения. я изначально думали, что это связано с настройкой целочисленной оптимальности, но не так ли. Они обнаружили, что десятичный ввод варьируется, что-то вроде от От 0 до 0,6 с шагом 0,1, скажем, не было точно 0,4, а вместо чего-то вроде 0.400025 небольшое округление, и этого было достаточно, чтобы вызывают неоптимальные решения. С их стороны отличная детективная работа, но я не уверен, как решить проблему. Во всяком случае, имейте в виду!

о Интересное использование SolverTable: В одну сторону используйте SolverTable, чтобы позволить ячейке ввода (для односторонней или двусторонней таблицы) быть начальное значение (я) ячейки (я) переменной решения. Для линейной модели единственный Суть в том, чтобы проверить, действительно ли Solver достигает оптимального решение независимо от начальных значений.Для нелинейной модели это может быть используется для проверки наличия локальных оптимумов, до которых может добраться Solver, в зависимости от начальных значений, с которых он начинается. Например, для задачи 7.48 PMS 3e, который имеет ровно две ячейки переменных решения, легко показать этот Решатель достигает глобального оптимума только для некоторых начальных значений ячейки переменной решения. (Спасибо Тому Шриберу за это предложение.)

StatPro и надстройки StatBasics

о StatPro для Excel 2007 и более поздних версий : Хотя я больше не поддерживаю StatPro, я с ним время от времени, и эта версия является результатом: StatPro New.zip. Это не все параметры из исходного StatPro (например, отсутствует пошаговая регрессия), но в нем есть несколько новых функций и немного другой интерфейс. это содержится в одном файле .xla, а работает с Excel 2007 и более поздними версиями. Чтобы загрузить его, просто дважды щелкните значок .xla файл.

о StatPro для Mac : Некоторые из вас запросили версию StatPro для Mac, то есть для Mac-версии Excel.Изначально это было невозможно, потому что В Excel 2008 даже не было языка программирования VBA. Это изменилось в Excel 2011, но интерфейс VBA сильно отличается от интерфейса Excel для Windows. В любом случае, я попробовал, и вы можете попробовать эту версию: StatPro для Mac.zip. Однако, ты полностью сам по себе; Я не поддерживаю эту версию.

о StatBasics для Excel 2007 : StatBasics для Excel 2007.zip. Это мини-версия StatPro, которую я создал (в основном для того, чтобы навыки программирования).Он предоставляет только основы: сводные измерения и полезные статистические диаграммы. Инструкции по установке находятся в zip-файле. Попробуйте, но имейте в виду, что я не поддерживаю его.

• Мартовское безумие NCAA simulation: Вот симуляция на 2019 год: март Безумие 2019 с Data Table.xlsx. Кроме того, вот версия, которая вычисляет вероятность идеальной скобки, т. е. успешное предсказание победителя каждой игры: March Madness Вероятности 2013.xlsx. (Я не обновлял этот файл после 2013 года, но вы можете, если хотите. Вероятность выбрать всех победителей по-прежнему будет бесконечно мал.)
• Анализ временной очереди: Transient Queue.xlsm Вы знайте, что вы можете анализировать очереди с изменяющимся во времени поведением (например, прибытие ставки, которые увеличиваются в пиковые периоды дня) с электронными таблицами, используя аналитических приближений, не моделирование или стационарный анализ? Это действительно возможно, поскольку Уэйн Уинстон указал в 4-м издании своей книги Operations Research .Хотя подход Уэйна заключается в использовании формул Excel, в этом файле используется VBA программа для выполнения расчетов. Наслаждайтесь!
• Автоматизация судоку с помощью VBA: Sudoku.xlsm (или Sudoku16.xlsm) Если вам нравится кропотливые усилия по работе с головоломками Судоку: карандашные отметки, стирание, и т. д., тогда вы должны , а не , открывать эти файлы. Но если ты хочешь увидеть насколько мощными могут быть программы VBA, посмотрите файл Sudoku.xlsm.(Вы можете проверить код VBA, чтобы увидеть, как он работает.) И если вы хотите пойти шаг вперед, с сетками 16x16 вместо обычных 9x9, проверьте файл Sudoku16.xlsm. ( Примечание: I незначительно изменил эти файлы в апреле 2016 года. В частности, Sudoku.xlsm файл теперь имеет лист алгоритма, который проведет вас через алгоритм реализован в коде VBA. Код VBA немного сложен для выполнения вложенных циклов, но сам алгоритм довольно прост.)

Более 100 бесплатных таблиц Excel

Электронные таблицы Excel - продолжение

1. 80 Модель прогнозирования - Пошаговая финансовая модель для прогнозирования финансовых показателей, созданная Сэмом Гуи
2. 81 Экономическая оценка - Пошаговая рабочая тетрадь для оценки экономики системных инвестиций
3. 82 Шаблоны управления проектами - Набор шаблонов (устав, бюджет, реестр рисков, журнал проблем и т. Д.) Для управления проектом - Международная ассоциация управления проектами и программами
4. 83 Рабочая книга оценки стоимости проекта - Модель рабочей книги для разработки оценки стоимости проекта разработки программного обеспечения - ProTrain Китай
5. 84 Реестр оценки рисков - Рабочая тетрадь для создания реестра управления рисками - Создатели потенциала для обеспечения готовности к чрезвычайным ситуациям
6. 85 Простая модель ABC - простая модель, иллюстрирующая калькуляцию затрат на основе действий
7. 86 Six Sigma Tool Kit - большая коллекция шаблонов для выполнения шести сигм задач
8. 87Project Management Tool Kit - Сборник полезных шаблонов для управления проектами - Майкл Д.Тейлор
9. 88 Модель оценки интеллектуальной собственности - простая и удобная в использовании модель, помогающая оценить интеллектуальные активы, такие как патенты, авторские права и товарные знаки
10. 89 Оценка зрелости ИТ-инфраструктуры - Модель зрелости для оценки различных сегментов ИТ-инфраструктуры
Модель
11. 90EVA - Шаблоны рабочих листов для расчета экономической добавленной стоимости (EVA) - Zachary Scott
12. 91EVA Tree Model - Детализированная модель экономической добавленной стоимости с диаграммами - Manfred Grotheer
13. 92 Модель личных финансов - простая и понятная модель для отслеживания личного состояния - команда ROI
14. 93 Модель финансового стартапа - модель с шестью параметрами, помогающая организовать финансовую отчетность для стартапов - команда ROI
15. 94 Оценка результативности лидерства - Простой и понятный инструмент для оценки лидерства по пяти важным категориям - Люди положительно
16. 95 Модель оценки должной осмотрительности - Оценка новых деловых предприятий по десяти различным измерениям - Советы для аффилированных лиц
17. 96Management Diamond Assessment - полезный инструмент для развития человеческого капитала и команд - Гейр Фуглаас | Обзор Management Diamond | Бриллиантовая книга менеджмента
18. 97Performance Solution Tool Kit - Оценка проблем производительности, связанных с пробелами в знаниях
19. 98Ratio Tree - Простое, но исчерпывающее дерево соотношений для большинства предприятий - Strategy Expert
20. 99Руководство по навыкам и моделированию | Рабочая тетрадь оценочных упражнений - Две очень хорошие модели электронных таблиц, которые помогут вам развить свои навыки моделирования электронных таблиц - Tykoh Training
21. 100 Модель прогнозирования безубыточности - чистый и современный инструмент для работы с электронными таблицами для легкого прогнозирования безубыточности.Включает параметры для сезонности и оптимизации запуска - Ethos 360
22. 101ROI IT Initiative Tool - Комплексная бизнес-модель для оценки технологических проектов - Hall Consulting
23. 102 Модель распределения водопада - Распределение доходов от коммерческого предприятия с использованием препятствий - Global iConnect
24. 103LBO Модель DCF - Модель выкупа с использованием заемных средств с вкладками для финансовых показателей и анализа - Global iConnect
25. 104 Еженедельный денежный поток - Прогнозируемый текущий денежный поток от покупок - Финансовый директор портала
26. 105 Факторинг денежных потоков - Еженедельный расчет денежных потоков с факторингом - Факторинг финансового счета MP Star
27. 106 Серия таблиц комиссионных> Калькулятор | Исправленный шаблон | Шаблон-Маржа | Выручка по шаблону
28. 107 Отчет приборной панели - Демонстрирует countif, vlookup и массивы - Боб Лопес
29. 108 Анализ инвестиций в недвижимость - пример рабочей тетради, разработанной Др.Грег Смерш
30. 109 Инвестиции в арендуемую недвижимость - Рабочая тетрадь для оценки арендуемой недвижимости - Globaliconnect
31. 110 Рабочая тетрадь для оценки | Руководство пользователя - Красивая и простая модель оценки с диаграммами чувствительности - Tykoh Training
32. 111 Прогноз продаж Монте-Карло - пример моделирования Монте-Карло с использованием простой модели прогноза продаж - Бизнес-шаблоны
33. 112 Графики амортизации - Расчет годовой амортизации с использованием различных методов - Бизнес-шаблоны
34. 113 Калькулятор NPV - показывает примеры, которые используют функции NPV, IRR, XNPV и XIRR - Бизнес-шаблоны
35. 114 Базовый курс Excel - Коллекция из 4 файлов Excel из базового курса Excel, предлагаемого DeZyre> Все 4 файла (zip) | Как объединить | Как использовать поиск | Как фильтровать данные | Модуль Excel
36. 115 Курс финансового моделирования - 12 различных файлов Excel из курса финансового моделирования, предлагаемого DeZyre> Загрузить все 12 файлов (zip) | Продвинутый доход | Анализируя Домино-1 | Анализируя Домино-2 | Основы бухгалтерского баланса | Отчет о движении денежных средств | Капитальные затраты | Бухгалтерский учет с двойной записью | Признание расходов | Выручка-валовая маржа | Признание доходов | Налог | Резюме |
37. 116 Набор инструментов BPM - 6 различных всеобъемлющих моделей из Best Practice Modeling> All 6 Files (zip) | Пример оценки предприятия | Пример оценки капитала | Модель исторического прогноза | Модель полного бизнес-плана | Резюме проекта | Диаграмма Multi Stack Waterfall
38. 117 Box IPO Model - Полная финансовая модель, разработанная Дираджем Вайдья - Wall Street Mojo
39. 118 Матрица сравнения участников торгов - взвешенная оценка конкурса предложений на основе критериев предложения - MyPM
40. 119 Таблица заявок без заявок - содержит сравнительные данные о рисках, партнерах, клиентах и ​​конкуренции - MyPM
41. 120 Win Theme Development - Сбор данных о клиентах, решениях, точках проверки и дискриминаторах - MyPM
42. 121 Матрица соответствия предложения - отслеживает требования запроса предложения и соответствующие разделы предложения - MyPM

Общие ресурсы

Бизнес-функции | Бизнес-аналитика | Таблицы для бизнеса | Преобразовать в веб-приложение | Преобразование данных | Приложения Excel | Аудитор Excel | Бизнес-пользователи Excel | Шпаргалки по Excel | Курсы Excel | Разработка Excel | Excel Web Conv | Экспозиция в Excel | ExcelFix | Формулы Excel | Идеи Excel | Материалы Excel | Смеситель Excel | Службы программирования Excel | Количественный анализ Excel | Инструмент восстановления Excel | Решения Excel | Поддержка Excel | Учебное пособие по Excel | Excel Необычное | Школа финансового моделирования | Обучение финансовой модели | Тренинг по финансовой модели-2 | Финансовые модели | Улучшить Excel | Invest Excel | Связанные с инвестициями | Магические рабочие тетради | Модель Ответ | Мистер Эксель | Онлайн-справка | Моделирование процессов | Прогнозируемые финансовые показатели | Quantrix Modeling [Примеры моделей Quantrix] | Связанные с недвижимостью | Безопасные электронные таблицы | Средство сравнения электронных таблиц | База данных электронных таблиц | Таблицы Ребята | Безопасность электронных таблиц | Анализатор запасов | Обучай Excel | XL моделирование

Шаблоны Excel

| Шаблоны высокого качества | Управление проектами |

Excel, дополнения

Able Bits | Каталог дополнений | Анализируйте это | Инструменты для кистей | Деловые правила | Пользовательские надстройки | Менеджер базы данных | Панели мониторинга | Анализ решений | Производный анализ | EZ Analyze | EZ Forecaster | FinCad-производные | Бесплатные экономические надстройки | Панель инструментов бесплатной навигации | Построитель моделей | Моделирование Монте-Карло | Монте-Карло - Легко | Монте-Карло-Тухи | NumXL | Оптимизация портфеля | Моделирование рисков | Инструментарий чувствительности | Simul-Stats | Электронная таблица ML | Статистический анализ | Восстановить поврежденные файлы | План дерева | Утилиты | Ценность под риском | XL Modeler | XL Sim | XL Статистика |

Таблицы шести сигм

Инструменты ASQ | Джон Зорич | SPC для Excel | SPC XL | Роберт Даллман | Файлы проекта Six Sigma

Финансовое моделирование

Чанду | Коралловость | Модельный чемпионат | Сливовые решения | Безупречный | Выработать стратегию
Онлайн-уроки финансового моделирования (включая учебное пособие):
Урок 1 - Моделирование денежных потоков
Урок 2 - Разработка финансовых моделей
Урок 3 - Финансовые функции Excel

Книги в формате Excel

Анализ бизнес-данных | Сбалансированные системы показателей в Excel | Бизнес-анализ | Анализ данных | Взломы Excel | Финансовый анализ | Финансовое моделирование | Финансы в Excel | Управление деньгами | Табличное моделирование |

Как создать Microsoft Query в Excel (Excel Query)

Вы можете использовать Microsoft Query в Excel для извлечения данных из книги Excel, а также из внешних источников данных с помощью операторов SQL SELECT .Созданные таким образом запросы Excel можно обновлять и запускать повторно, что делает их удобным и эффективным инструментом в Excel.

Microsoft Query позволяет использовать SQL непосредственно в Microsoft Excel , рассматривая листы как таблицы, для которых вы можете запускать операторы Select с JOIN, UNION и т. Д. Часто операторы Microsoft Query более эффективны, чем формулы Excel или макрос VBA.

Microsoft Query (он же MS Query, он же Excel Query) на самом деле является оператором SQL SELECT. Excel, как и Access, используют Windows ACE.Поставщики OLEDB или JET.OLEDB для выполнения запросов. Это невероятный, часто неиспользованный инструмент, недооцененный многими пользователями!

Используя MS Query в Excel, вы можете извлекать данные из различных источников, таких как:

• Файлы Excel - вы можете извлекать данные из внешних файлов Excel, а также выполнять запрос SELECT в текущей книге
• Access - вы можете извлекать данные из файлов базы данных Access
• MS SQL Server - вы можете извлекать данные из таблиц Microsoft SQL Server
• CSV и текст - вы можете загружать CSV или табличные текстовые файлы

Как создать запрос Microsoft в Excel

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

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

В приведенном ниже процессе показано, как можно создать запрос для своей текущей или внешней книги Excel. Однако процесс создания файлов MS Query for Access, SQL и Text (CSV) очень похож.

Откройте MS Query (из других источников) wizard

Перейдите на вкладку ленты DATA и щелкните Из других источников .Выберите последний вариант из Microsoft Query .

Выберите источник данных

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

Выберите исходный файл Excel

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

Выберите столбцы для вашего запроса MS

Мастер теперь просит вас выбрать столбцы для вашего запроса MS.Если вы планируете позже изменить MS Query вручную, просто нажмите OK . В противном случае выберите свои столбцы.

Вернуть запрос или изменить запрос

Теперь у вас есть два варианта:

1. Вернуть данные в Microsoft Excel это вернет результаты вашего запроса в Excel и завершит работу мастера

2. Просмотр данных или изменение запроса в Microsoft Query откроется окно Microsoft Query и вы сможете изменить Microsoft Query

Дополнительно: Изменить запрос

Если вы выбрали вариант Просмотр данных или редактирование запроса в Microsoft Query , то теперь вы можете открыть окно SQL Edit Query, нажав кнопку SQL .Когда вы закончите, нажмите кнопку возврата (с открытой дверью).

Импорт данных

Когда вы закончите изменять свой оператор SQL (как я на предыдущем шаге). Нажмите кнопку Вернуть данные в окне Microsoft Query .
Это должно открыть окно Import Data , которое позволяет вам выбрать, когда данные должны быть выгружены.
Наконец, когда вы закончите, нажмите ОК в окне Импорт данных , чтобы завершить выполнение запроса.Вы должны увидеть результат запроса в виде новой таблицы Excel:

Как и в окне выше, я вычислил, сколько записей в исходной таблице содержит Male и сколько Female.

Как видите, для достижения чего-то потенциально довольно простого требуется довольно много шагов. Следовательно, есть несколько альтернатив, благодаря мощи VBA Macro….

Надстройка Excel SQL

Чтобы упростить использование MS Query в Excel, я разработал надстройку Excel SQL.Надстройка позволяет создавать запросы MS быстрее и с меньшим количеством шагов. Не стесняйтесь проверить это.
Лента надстройки Excel SQL

Трюк с запросом MS

Если вы не хотите использовать AddIn, другой способ - создать эти запросы с помощью макроса VBA. Ниже приведен быстрый макрос, который позволит вам написать запрос в простом поле ввода VBA в выбранном диапазоне на вашем листе.

Просто используйте мой фрагмент кода VBA:

Подраздел ExecuteSQL () Атрибут ExecuteSQL.VB_ProcData.VB_Invoke_Func = "S \ n14" 'AnalystCave.com При ошибке Перейти к ErrorHandl Развернуть SQL как строку, sConn как строку, qt как QueryTable SQL = InputBox («Предоставьте свой SQL-запрос», «Выполните SQL-запрос») Если SQL = vbNullString, тогда выйдите из Sub sConn = "OLEDB; Provider = Microsoft.ACE.OLEDB.12.0 ;; Password =; User ID = Admin; Data Source =" & _ ThisWorkbook.Path & "/" & ThisWorkbook.Name & ";" & _ "Mode = Share Deny Write; Extended Properties =" "Excel 12.0 Xml; HDR = YES" ";" Установите qt = ActiveCell.Worksheet.QueryTables.Add (соединение: = sConn, назначение: = ActiveCell) С qt .CommandType = xlCmdSql .CommandText = SQL .Name = Int ((1000000000 - 1 + 1) * Rnd + 1). .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery: = Ложь Конец с Выход из подводной лодки ErrorHandl: MsgBox "Ошибка:" и описание ошибки: Err.Clear Конечный переводник

1 2 3 4 5 6 7 8 9 10 11 12 13 14 13 14 18 19 20 21

Sub ExecuteSQL () Атрибут ExecuteSQL.VB_ProcData.VB_Invoke_Func = "S \ n14" 'AnalystCave.com При ошибке GoTo ErrorHandl Dim SQL As String, sConn As String, qt As QueryTable SQL = InputBox («Provide your SQL Query» SQL Query ") Если SQL = vbNullString, то выйдите из Sub sConn =" OLEDB; Provider = Microsoft.ACE.OLEDB.12.0 ;; Password =; User ID = Admin; Data Source = "& _ ThisWorkbook.Path & "/" & ThisWorkbook.Name & ";" & _ "Mode = Share Deny Write; Extended Properties =" "Excel 12.0 Xml; HDR = YES ""; " Установить qt = ActiveCell.Worksheet.QueryTables.Add (Connection: = sConn, Destination: = ActiveCell) With qt .CommandType = xlCmdSql ext = xlCmdSql .Comm SQL .Name = Int ((1000000000 - 1 + 1) * Rnd + 1) .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery: = False Конец с Exit Sub ErrorHandl: Msg Err.Описание: Err.Clear Концевой переводник

Просто создайте новый модуль VBA и вставьте приведенный выше код. Вы можете запустить его, нажав сочетание клавиш CTRL + SHIFT + S Keyboardshortcut или добавив макрос на панель быстрого доступа.

Изучение SQL с помощью Excel

Создание MS Queries - это одно, но вам нужно хорошо разбираться в языке SQL, чтобы использовать его истинный потенциал. Я рекомендую использовать простую базу данных Excel (например, Northwind) и практиковать различные запросы с JOIN.

Power Query (получение и преобразование)

Другой способ выполнения запросов - использовать Microsoft Power Query (также известный в Excel 2016 и более поздних версиях как Get and Transform ). Надстройка, предоставляемая Microsoft, требует знания языка SQL, позволяя вам пролистывать данные, которые вы хотите преобразовать.

MS Query и выводы Power Query

MS Query Pros : Power Query - это замечательный инструмент , однако он не отменяет полностью Microsoft Queries.Более того, иногда использовать Microsoft Queries быстрее и удобнее, и вот почему :

• Microsoft Queries более эффективны, если вы знаете SQL . Хотя вы можете перейти к преобразованию данных с помощью Power Query, кто-то, кто знает SQL, вероятно, будет на намного быстрее в написании подходящего запроса SELECT
• Вы не можете повторно запустить Power Queries без надстройки . Хотя это, очевидно, будет менее актуальным, вероятно, через пару лет (в более новых версиях Excel), в настоящее время, если у вас нет надстройки, вы не сможете редактировать или повторно запускать запросы, созданные в Power Query
.

MS Query Cons : Microsoft Query отстает от надстройки Power Query в некоторых других аспектах:

• Power Query имеет более удобный пользовательский интерфейс .Хотя Power Queries относительно легко создать, мастер MS Query Wizard похож на веб-сайт 90-х годов
.
• Power Query складывает операции друг над другом, что упрощает внесение изменений. . Пока MS Query работает или просто не компилируется, Power Query складывает каждую операцию преобразования, обеспечивая видимость вашей задачи преобразования данных и упрощая добавление / удаление операций

Короче говоря, я рекомендую изучить Power Query, если вы не чувствуете себя комфортно с SQL.