Как сделать предложение в Excel

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Excel Mobile Еще. Меньше

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

PMT рассчитывает платеж по кредиту на основе постоянных платежей и постоянной процентной ставки.

КПЕР вычисляет количество периодов оплаты для инвестиции на основе регулярных, постоянных платежей и постоянной процентной ставки.

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

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

Рассчитайте ежемесячные платежи для погашения задолженности по кредитной карте

Предположим, что остаток к оплате составляет 5400 долларов США по годовой процентной ставке 17 %. Пока идет погашение долга, ничего больше по карте покупаться не будет.

Использование функции ПЛТ(ставка,КПЕР,ТС)

=PMT(17%/12,2*12,5400)

в результате ежемесячный платеж в размере 266,99 долл. США погасит долг за два года.

Аргумент rate — это процентная ставка за период для кредита. Например, в этой формуле годовая процентная ставка 17 % делится на 12 – количество месяцев в году.

Аргумент КПЕР, равный 2*12, – это общее количество периодов платежей по кредиту.

Аргумент PV или текущей стоимости равен 5400.

Рассчитать ежемесячные платежи по ипотеке

Представьте себе дом стоимостью 180 000 долларов США, процентная ставка – 5 %, а срок ипотеки – 30 лет.

Использование функции ПЛТ(ставка,КПЕР,ТС)

=ПЛТ(5%/12,30*12,180000)

в результате ежемесячный платеж (не включая страховку и налоги) составляет 966,28 долларов США.

Аргумент ставки равен 5%, разделенному на 12 месяцев в году.

Аргумент КПЕР равен 30*12 для ипотеки на 30 лет с 12 ежемесячными платежами каждый год.

Аргумент PV равен 180 000 (текущая стоимость кредита).

Узнайте, как ежемесячно откладывать деньги на отпуск мечты

Вы хотите накопить на отпуск через три года, который будет стоить 8 500 долларов США. Годовая процентная ставка для сбережений составляет 1,5%.

Использование функции ПЛТ(ставка,КПЕР,ТС,БС)

Чтобы сэкономить 8 500 долларов США за три года, потребуется экономить 230,99 долларов США каждый месяц в течение трех лет.

Аргумент rate равен 1,5 %, разделенному на 12 – количество месяцев в году.

Аргумент КПЕР равен 3*12 для двенадцати ежемесячных платежей в течение трех лет.

PV (текущая стоимость) равен 0, поскольку учетная запись начинается с нуля.

FV (будущая стоимость), которую вы хотите сохранить, составляет 8 500 долларов США.

Теперь представьте, что вы откладываете 8 500 долларов США на отпуск в течение трех лет и задаетесь вопросом, сколько вам потребуется внести на свой счет, чтобы сохранить ежемесячную экономию на уровне 175 долларов США. Функция PV рассчитает, какая часть начального депозита принесет будущую стоимость.

Использование функции ТС(ставка,КПЕР,ПЛТ,БС)

Потребуется первоначальный депозит в размере 1 969,62 долларов США, чтобы иметь возможность платить 175 долларов США в месяц и получить 8500 долларов США через три года.

Аргумент ставки: 1,5%/12.

Аргумент КПЕР равен 3*12 (или двенадцать ежемесячных платежей в течение трех лет).

PMT равен -175 (вы будете платить 175 долларов США в месяц).

FV (будущая стоимость) – 8 500.

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

Представьте, что у вас есть личный кредит в размере 2500 долларов США, и вы согласились платить 150 долларов США в месяц под 3 % годовых.

Использование функции КПЕР(ставка,ПЛТ,ТС)

=КПЕР(3%/12,-150,2500)

на погашение кредита уйдет 17 месяцев и несколько дней.

Аргумент ставки: 3%/12 ежемесячных платежей в год.

Аргумент PMT равен -150.

Аргумент PV (текущая стоимость) равен 2500.

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

Скажем, вы хотите купить автомобиль стоимостью 19 000 долларов США по процентной ставке 2,9 % в течение трех лет. Вы хотите сохранить ежемесячные платежи на уровне 350 долларов в месяц, поэтому вам нужно выяснить свой первоначальный взнос. В этой формуле результатом функции PV является сумма кредита, которая затем вычитается из покупной цены, чтобы получить первоначальный взнос.

Использование функции ТС(ставка,КПЕР,ПЛТ)

=19000-PV(2,9%/12, 3*12,-350)

требуемый авансовый платеж составит 6 946,48 долл. США

Цена покупки в размере 19 000 долларов США указана первой в формуле. Результат функции PV будет вычтен из цены покупки.

Аргумент rate равен 2,9 %, разделенному на 12.

Аргумент КПЕР равен 3*12 (или двенадцать ежемесячных платежей в течение трех лет).

PMT равен -350 (вы будете платить 350 долларов США в месяц).

Узнайте, сколько со временем вырастет ваша экономия

Начиная с 500 долларов США на счете, сколько у вас будет через 10 месяцев, если вы будете вносить 200 долларов США в месяц под 1,5 % годовых?

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

Форма заказа Excel

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

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

Видео: как создать форму заказа в Excel

В этом пошаговом видео показано, как создать форму заказа в Excel. Письменные инструкции находятся под видео.

Временная шкала видео
  • 00:00 Введение
  • 01:00 3 листа
  • 02:26 Таблицы Excel
  • 03:27 Именованные диапазоны
  • 05:34 Запустить форму заказа
  • 06:21 Добавьте заголовки
  • 06:58 Раскрывающиеся списки товаров
  • 07:43 Поиск цены
  • 08:59 Добавить ВПР
  • 10:09 Итого по строке
  • 11:26 Общий итог
  • 12:06 Добавить форматирование
  • 13:01 Выпадающий список клиентов
  • 13:49 Поиск клиентов
  • 15:15 Комбинированный поиск
  • 17:44 Окончательное форматирование
  • 18:34 Получить образец файла

Настройка рабочей книги

Для начала создайте два листа в новой книге:

  1. Открыть новую пустую книгу
  2. Переименовать Лист 1 в форму заказа.
  3. Вставьте другой лист и переименуйте его в Товары.

Создайте список товаров

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

Введите список

  1. На листе "Товары" введите список продуктов и цен, как показано ниже, начиная с ячейки A1.

Форматировать как таблицу Excel

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

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

Назовите список товаров

Далее выполните следующие действия, чтобы назвать ячейки в столбце A. Чтобы узнать больше об именах Excel, перейдите на страницу «Имя диапазона».

  1. Выберите список товаров в ячейках A2:A6 (НЕ включайте цены в столбец B).
  2. Нажмите в поле имени слева от строки формул.
  3. Введите название диапазона из одного слова --ProductList -- и нажмите клавишу Enter.

Назовите таблицу поиска товаров

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

  1. Выберите все названия продуктов и цены в ячейках A2:B6, НЕ включая заголовки.
  2. Нажмите в поле имени слева от строки формул.
  3. Введите название диапазона из одного слова --ProductLookup--, затем нажмите клавишу Enter.

Запустить форму заказа

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

  1. Введите заголовок "Форма заказа" в ячейку B2.
  2. Отформатируйте шрифт заголовка и размер шрифта.
  3. Сделайте столбец A достаточно узким, например Ширина 1,00 (13 пикселей)
  4. В ячейке E2 введите следующую формулу, чтобы отобразить текущую дату: =СЕГОДНЯ()
  5. Отформатируйте ячейку с датой – в этом примере день – месяц.
  6. В ячейках с B9 по E9 введите заголовки столбцов: "Продукт", "Цена", "Количество", "Итого".
  7. Выберите ячейки с B9 по E14 и примените параметр «Все границы» в списке «Границы».
  8. Уменьшите высоту строк 1, 3 и 8 примерно до 4,5 (6 пикселей).

Создайте раскрывающиеся списки продуктов

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

  1. На листе формы заказа выберите ячейки B10:B14.
  2. На вкладке "Данные" ленты Excel нажмите "Проверка данных".
  3. В раскрывающемся списке Разрешить выберите Список
    4. .
  4. В поле Источник введите: =ProductList
  5. Нажмите кнопку "ОК".

Добавить поиск цены

Формулы в столбце C вернут цену для каждого продукта, выбранного в форме заказа. Формула объединяет две функции Excel — ВПР и ЕСЛИ.

  • Формула ЕСЛИ проверяет ячейку товара и, если она пуста, возвращает пустую строку "", поэтому ячейка цены выглядит пустой: ЕСЛИ(B10="","",
  • Формула ВПР пытается найти выбранный продукт в таблице ProductLookup.
    • Если товар найден, формула возвращает цену этого товара из второго столбца таблицы: ВПР(B10,ПродуктПоиск,2,ЛОЖЬ)
    • Нажмите на эту ссылку, чтобы узнать больше о функции ВПР

    Выполните следующие действия, чтобы добавить формулу в столбец "Цена":

    1. В ячейке B10 выберите продукт из раскрывающегося списка.
    2. На листе формы заказа выберите ячейки C10:C14.
    3. Введите формулу:
      =IF(B10="","",VLOOKUP(B10,ProductLookup,2,FALSE))
    4. Нажмите Ctrl + Enter, чтобы ввести формулу во все выделенные ячейки.

    Рассчитать итоговую сумму строки

    Количество будет введено в столбце D, а итоговая сумма строки рассчитана в столбце E. Формула заключена в формулу ЕСЛИ, чтобы предотвратить нулевой расчет, если в строке не выбран ни один продукт.

    1. На листе формы заказа выберите ячейки E10:E14.
    2. Введите формулу: =ЕСЛИ(C105="","",C10*D10)
    3. Нажмите Ctrl + Enter, чтобы ввести формулу во все выделенные ячейки.

    Общая сумма заказа

    Функция СУММ используется для суммирования всех строк в форме заказа.

    1. На листе формы заказа выберите ячейку E16.
    2. Введите формулу: =СУММ(E10:E14)
    3. Нажмите Enter, чтобы завершить формулу.

    Формат формы заказа

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

    1. Добавьте цвет заливки в столбцы B и D, где требуется ввод данных.
    2. Сделайте шрифты заголовков полужирными
    3. Отформатируйте дату по желанию.
    4. Добавьте формат валюты в столбцы «Цена» и «Итого» и в ячейку «Итог заказа».

    Добавить список клиентов

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

    Добавить список клиентов

    1. Вставьте новый лист и назовите его "Клиенты".
    2. На листе "Клиенты" в строке 1 и заголовках Имя, Улица, Город, Prov, Почтовый индекс.
    3. В следующих строках введите данные для нескольких ваших клиентов.
    4. Выделите ячейки заголовков полужирным шрифтом.

    Форматировать как таблицу Excel

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

    Назовите список клиентов

    Далее выполните следующие действия, чтобы назвать ячейки в столбце A. Чтобы узнать больше об именах Excel, перейдите на страницу «Имя диапазона».

    1. Выберите список клиентов в ячейках A2:A5 (НЕ включайте ячейку заголовка)
    2. Нажмите в поле имени слева от строки формул.
    3. Введите название диапазона из одного слова --CustList -- и нажмите клавишу Enter.

    Назовите таблицу поиска клиентов

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

    1. Выделите все имена и адресную информацию в ячейках A2:E5 — НЕ включайте заголовки.
    2. Нажмите в поле имени слева от строки формул.
    3. Введите имя диапазона, состоящее из одного слова, --CustLookup--, затем нажмите клавишу Enter.

    Создать раскрывающийся список и поиск клиентов

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

    1. В ячейке B4 введите: Отправить:
    2. В ячейку B5 добавьте список проверки данных, используя CustList в качестве источника.
    3. В ячейке B6 введите следующую формулу, чтобы получить почтовый адрес клиента, если ячейка B5 не пуста:
      =IF(B5="","",VLOOKUP(B5,CustLookup,2,FALSE))
    4. В ячейке B7 введите следующую формулу, которая будет искать город, провинцию и почтовый индекс для выбранного имени клиента, если ячейка B5 не пуста. Оператор & объединяет результаты в одной ячейке с запятой после города и пробелом после провинции:
      =IF(B5="","",VLOOKUP(B5,CustLookup,3,FALSE) & ", " & ВПР(B5,Поиск по потребителю,4,ЛОЖЬ) & " " & ВПР(B5,Поиск по потребителю,5,ЛОЖЬ))
    5. Чтобы протестировать формулы, выберите клиента из раскрывающегося списка в ячейке B5. Информация об адресе должна появиться в ячейках B6 и B7.

    Платежный адрес (необязательно)

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

    • Чтобы использовать тот же адрес, установите флажок, чтобы добавить галочку. Макрос копирует адрес доставки в раздел «Кому выставить счет».
    • Чтобы очистить платежный адрес, снова установите флажок, чтобы снять отметку.

    ПРИМЕЧАНИЕ. Эта необязательная функция и макрос находятся в образце файла платежного адреса. Заполненная форма заказа не содержит макросов.

    Сохранить файл

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

    • В верхней части Excel откройте вкладку "Файл".
    • Слева нажмите "Сохранить как".
    • В правом верхнем углу в раскрывающемся списке выберите Книга Excel с поддержкой макросов (*.xlsm) в качестве типа файла
      • ПРИМЕЧАНИЕ. При желании вы можете выбрать двоичную книгу Excel (*.xlsb).

      Добавить счет в раздел

      Вот шаги по добавлению раздела «Выставить счет» в верхней части формы заказа.

      • Выберите ячейки «Отправить в», B5:E7 и измените границу на «Без границы».
      • Выберите ячейки B5:C7 и измените границу на "Внешние границы".
      • Выберите ячейки D5:E7 и измените границу на "Внешние границы".
      • В ячейке D4 введите Счет на: и отформатируйте курсивом.

      Добавить флажок

      • Перейдите на вкладку "Разработчик" на ленте Excel. Если он не отображается, выполните действия, описанные здесь.
      • Нажмите стрелку команды "Вставить" и в разделе "Элементы управления формой" выберите инструмент "Флажок"

        Связать флажок

        • Добавьте новый лист в книгу и назовите его "Администратор".
        • Выберите ячейку A1 и измените ее границу на "Внешние границы".
        • Измените цвет заливки на серый, чтобы показать, что ячейку не следует изменять вручную.
        • В ячейке B1 введите метку "То же, что и для отправки".
        • Вернитесь к листу формы заказа, щелкните правой кнопкой мыши флажок и выберите "Управление форматом".
        • На вкладке "Управление" щелкните поле "Ссылка на ячейку".
        • В книге откройте вкладку "Администратор" и выберите ячейку A1.
        • Нажмите "ОК", чтобы закрыть диалоговое окно "Формат объекта".

        Проверить ссылку

        • Нажмите на лист вне флажка, чтобы снять его.
        • Нажмите на флажок, чтобы добавить галочку, затем перейдите на лист администратора.
          • В ячейке A1 должно быть указано TRUE, если флажок установлен.
          • Если этот флажок снят, в ячейке A1 будет указано ЛОЖЬ.

          Назовите диапазоны ячеек

          Далее вы создадите 3 именованных диапазона — для ячеек «Кому отправить» и «Кому выставить счет», а также связанную ячейку на листе администратора. Эти имена будут использоваться в макросе флажка.

          • На листе формы заказа выберите ячейки B5:C7.
          • Нажмите в поле "Имя" слева от строки формул.
          • Введите название диапазона из одного слова -- ShipTo -- и нажмите клавишу Enter

            ПРИМЕЧАНИЕ. Чтобы просмотреть определения этих имен, перейдите на вкладку "Формулы" на ленте Excel и нажмите команду "Диспетчер имен".

            Добавить макрос

            • На листе формы заказа щелкните правой кнопкой мыши флажок и выберите "Назначить макрос".
            • Нажмите кнопку «Создать». Откроется окно редактора Visual Basic с модулем кода.
            • В этом модуле кода есть пустая процедура макроса с именем вашего флажка.

              Выставление счета по коду макроса

              Вот как работает макрос:

              • Если ячейка A1 на листе администратора (BillLink) содержит значение TRUE,
                • скопируйте адрес доставки (ShipTo) в раздел выставления счетов (BillTo)
                • очистить содержимое раздела оплаты.

                Назначить новый макрос

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

                • На листе формы заказа щелкните правой кнопкой мыши флажок и выберите "Назначить макрос".
                • В списке макросов нажмите ChangeBillAddress
                • Нажмите кнопку "ОК", чтобы назначить этот макрос флажку.

                Проверить макрос

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

                Окончательные корректировки

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

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

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

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

                Знакомые лист Excel, лента и сетка

                Лента редактора Power Query и предварительный просмотр данных

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

                Переименовывать вкладки рабочего листа Рекомендуется переименовывать вкладки рабочего листа осмысленно, особенно если их много. Особенно важно прояснить разницу между рабочим листом данных и рабочим листом, загруженным из редактора Power Query.Даже если у вас есть только два рабочих листа, один с таблицей Excel с именем Sheet1, а другой с запросом, созданным путем импорта этой таблицы Excel с именем Table1, легко запутаться. Всегда полезно изменить имена вкладок рабочего листа по умолчанию на имена, которые имеют для вас больше смысла. Например, переименуйте Sheet1 в DataTable и Table1 в QueryTable. Теперь понятно, на какой вкладке данные, а на какой запрос.

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

                Создайте запрос из импортированных данных

                Это наиболее распространенный способ создания запроса.

                Импорт некоторых данных. Дополнительные сведения см. в разделе Импорт данных из внешних источников данных.

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

                Создайте пустой запрос

                Возможно, вы захотите начать с нуля. Есть два способа сделать это.

                Выберите Данные > Получить данные > Из других источников > Пустой запрос.

                Выберите Данные > Получить данные > Запустить редактор Power Query.

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

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

                Выберите Новый источник, чтобы добавить источник данных. Эта команда аналогична команде Данные > Получить данные на ленте Excel.

                Выберите «Недавние источники», чтобы выбрать источник данных, с которым вы работали. Эта команда аналогична команде Данные > Недавние источники на ленте Excel.

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

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

                Загрузить запрос из редактора Power Query

                В редакторе Power Query выполните одно из следующих действий:

                Чтобы загрузить на лист, выберите Главная > Закрыть и загрузить > Закрыть и загрузить.

                Чтобы загрузить модель данных, выберите Главная > Закрыть и загрузить > Закрыть и загрузить в.

                В диалоговом окне "Импорт данных" выберите "Добавить эти данные в модель данных".

                Совет. Иногда команда «Загрузить в» неактивна или неактивна. Это может произойти при первом создании запроса в книге. В этом случае выберите «Закрыть и загрузить», на новом листе выберите «Данные» > «Запросы и подключения» > вкладка «Запросы», щелкните запрос правой кнопкой мыши и выберите «Загрузить в». Либо на ленте редактора Power Query выберите "Запрос" > "Загрузить в".

                Загрузить запрос с панели "Запросы и подключения"

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

                В Excel выберите Данные > Запросы и соединения, а затем перейдите на вкладку Запросы.

                В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите Загрузить в. Появится диалоговое окно «Импорт данных».

                Решите, как вы хотите импортировать данные, а затем нажмите кнопку ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, щелкните знак вопроса (?).

                Существует несколько способов редактирования запроса, загруженного на лист.

                Редактировать запрос на основе данных на листе Excel

                Чтобы изменить запрос, найдите его, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Запрос" > "Изменить".

                Редактировать запрос на панели "Запросы и подключения"

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

                В Excel выберите Данные > Запросы и соединения, а затем перейдите на вкладку Запросы.

                В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите "Изменить".

                Редактировать запрос в диалоговом окне "Свойства запроса"

                В Excel выберите "Данные" > "Данные и соединения" > вкладка "Запросы", щелкните запрос правой кнопкой мыши и выберите "Свойства", выберите вкладку "Определение" в диалоговом окне "Свойства", а затем выберите "Редактировать запрос".

                Совет. Если вы находитесь на листе с запросом, выберите "Данные" > "Свойства", выберите вкладку "Определение" в диалоговом окне "Свойства", а затем выберите "Редактировать запрос".

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

                Чтобы открыть модель данных, выберите Power Pivot > Управление.

                В нижней части окна Power Pivot выберите вкладку рабочего листа нужной таблицы.

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

                Обратите внимание на название таблицы.

                Чтобы закрыть окно Power Pivot, выберите «Файл» > «Закрыть». Освобождение памяти может занять несколько секунд.

                Выберите "Данные" > "Подключения и свойства" > вкладка "Запросы", щелкните запрос правой кнопкой мыши и выберите "Изменить".

                После внесения изменений в редакторе Power Query выберите "Файл" > "Закрыть и загрузить".

                Запрос на листе и таблица в модели данных обновляются.

                Если вы заметили, что загрузка запроса в модель данных занимает гораздо больше времени, чем загрузка на лист, проверьте шаги Power Query, чтобы узнать, фильтруете ли вы текстовый столбец или столбец со структурой списка с помощью оператора «Содержит». Это действие заставляет Excel снова перебирать весь набор данных для каждой строки. Кроме того, Excel не может эффективно использовать многопоточное выполнение. В качестве обходного пути попробуйте использовать другой оператор, например Equals или Begins With.

                Microsoft известно об этой проблеме, и она расследуется.

                Вы можете загрузить Power Query:

                На лист. В редакторе Power Query выберите Главная > Закрыть и загрузить > Закрыть и загрузить.

                К модели данных. В редакторе Power Query выберите Главная > Закрыть и загрузить > Закрыть и загрузить в.

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

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

                Глобальные настройки, которые применяются ко всем вашим книгам

                В редакторе Power Query выберите "Файл" > "Параметры и настройки" > "Параметры запроса".

                В левой части диалогового окна "Параметры запроса" в разделе "ГЛОБАЛЬНЫЕ" выберите "Загрузка данных".

                В разделе "Параметры загрузки запросов по умолчанию" выполните следующие действия:

                Выберите Использовать стандартные настройки загрузки.

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

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

                Настройки книги, которые применяются только к текущей книге

                В левой части диалогового окна "Параметры запроса" в разделе ТЕКУЩАЯ РАБОЧАЯ КНИГА выберите "Загрузка данных".

                Выполните одно или несколько из следующих действий:

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

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

                В разделе "Связи" установите или снимите флажок "Создавать связи между таблицами при первом добавлении в модель данных".

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

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

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

                В разделе "Фоновые данные" установите или снимите флажок Разрешить предварительный просмотр данных для загрузки в фоновом режиме.

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

                поиск меню

                Урок 12. Распродажа со скидкой

                "Эй, не мог бы ты мне помочь? Я хочу купить кое-что новое для своего велосипеда, и сегодня отличная распродажа — скидка 15 % на все.

                Можете ли вы помочь мне понять, сколько это будет стоить после скидки? Я добавил основную информацию в электронную таблицу, если так будет проще."

                Наша таблица

                Загрузив нашу электронную таблицу, откройте файл в Excel или другом приложении для работы с электронными таблицами. Здесь у нас есть два основных столбца: один для названия товара, а другой для цены товара. В ячейке B5 также есть формула, которая вычисляет первоначальную общую стоимость товаров: =СУММ(B2:B4)

                Что мы пытаемся выяснить?

                Давайте подумаем о проблеме, которую мы пытаемся решить. Мы хотим знать общую стоимость после скидки. Скидка – это процент от первоначальной цены.В этом примере скидка составляет 15 % от исходной суммы.

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

                1. Найдите скидку, которая составляет 15 % от 78,77 доллара США.
                2. Вычтите это значение из исходной суммы, чтобы найти окончательную стоимость.

                Шаг 1. Найдите скидку

                Мы знаем, что нам нужна формула, которая рассчитает 15 % от 78,77 доллара США. Всякий раз, когда вы видите фразу «чего-то» в процентной задаче, это намек на то, что вам нужно умножить. Поэтому нам нужно умножить 78,77 доллара на 15%, чтобы найти скидку. Когда вы используете проценты в формулах, вам нужно записывать их в виде десятичных дробей.

                Отлично! Теперь, когда мы знаем, как записать 15% в виде десятичной дроби, мы готовы написать нашу формулу:

                =B5*0,15

                Мы введем эту формулу в ячейку B6 и затем нажмем Enter:

                И вот наш ответ: скидка составляет 11,82 доллара США.

                Шаг 2. Вычтите скидку из исходной суммы

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

                =B5–B6

                Мы введем эту формулу в ячейку B7 и нажмем Enter:

                Окончательная цена после скидки составит 66,95 долларов США.

                "Значит, я сэкономлю около 12 долларов?

                Хорошо! Я полагал, что это будет примерно столько, но я всегда хотел бы точно знать, сколько я трачу! Большое спасибо — и дайте мне знать, если вы скоро отправитесь на велосипедную дорожку!"

                Бонусный раздел

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

                Можете ли вы выяснить, какую формулу мы могли бы использовать вместо этого? Подсказка: поскольку мы знаем, что получим скидку 15 %, вы также можете сказать, что окончательная стоимость составит 85 % от первоначальной суммы.

                Читайте также: