Базы данных Excel: создание реляционных таблиц 2021

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

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

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

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel H&F разобрался, как предпринимателю перестать бояться формул Excel и начать считать всё, что хочется.

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel

Excel — не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel-гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О’кей, иногда этих кликов 15.)

Импорт курса валют

/>

В Excel можно настроить постоянно обновляющийся курс валют.

— Выберите в меню вкладку «Данные».

— Нажмите на кнопку «Из веба».

— В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

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

— Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».

— Курс появится в ячейках на вашем листе.

— Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).

— В появившемся окне выберите частоту обновления курса и нажмите «ОК».

Супертайный лист

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

— Слева у вас появится вытянутое окно (картинка 2).

— В верхней части окна выберите номер листа, который хотите скрыть.

В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.

Запрет на изменения задним числом

Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».

— Нажмите на кнопку «Проверка данных». Появится таблица.

— В выпадающем списке «Тип данных» выбираем «Другой».

— В графе «Формула» пишем =А2=СЕГОДНЯ().

— Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).

— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).

— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

Запрет на ввод дублей

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

— Выделяем ячейки А1:А10, на которые будет распространяться запрет.

— Во вкладке «Данные» нажимаем кнопку «Проверка данных».

— Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).

— В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.

— В этом же окне переходим на вкладку «Сообщение об ошибке» и там вводим текст, который будет появляться при попытке ввести дубликаты (картинка 2).

Выборочное суммирование

Перед вами таблица, из которой видно, что разные заказчики несколько раз покупали у вас разные товары на определённые суммы. Вы хотите узнать, на какую общую сумму заказчик по имени ANTON купил у вас крабового мяса (Boston Crab Meat).

— В ячейку G4 вы вводите имя заказчика ANTON.

— В ячейку G5 — название продукта Boston Crab Meat.

— Встаёте на ячейку G7, где у вас будет подсчитана сумма, и пишете для неё формулу <=СУММ((С3:С21=G4)*( B3:B21=G5)*D3:D21)>. Сначала она пугает своими объёмами, но если писать постепенно, то её смысл становится понятен.

— Первый множитель (С3:С21=G4) ищет в указанном списке клиентов упоминания ANTON.

— Второй множитель (B3:B21=G5) делает то же самое с Boston Crab Meat.

— Третий множитель D3:D21 отвечает за столбец стоимости, после него мы закрываем скобки.

— Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.

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

У вас есть таблица (картинка 1), где указано, какой товар, какому заказчику, на какую сумму продал конкретный менеджер. Когда она разрастается, выбирать отдельные данные из неё очень сложно. Например, вы хотите понять, на какую сумму продано моркови или кто из менеджеров выполнил больше всего заказов. Для решения таких проблем в Excel существуют сводные таблицы. Чтобы её создать, вам нужно:

— Во вкладке «Вставка» нажать кнопку «Сводная таблица».

— В появившемся окне нажать «ОК» (картинка 2).

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

Товарный чек

Чтобы посчитать общую сумму заказа, можно поступить как обычно: добавить столбец, в котором нужно перемножить цену и количество, а потом посчитать сумму по этому столбцу (картинка 1). Если же перестать бояться формул, можно сделать это более изящно.

— Выделяем ячейку C7.

— Выделяем диапазон B2:B5.

— Вводим звёздочку, которая в Excel ­ — знак умножения.

— Выделяем диапазон C2:C5 и закрываем скобку (картинка 2).

— Вместо Enter при написании формул в Excel нужно вводить Ctrl + Shift + Enter.

Сравнение прайсов

Это пример для продвинутых пользователей Excel. Допустим, у вас есть два прайса, и вы хотите сравнить их цены. На 1-й и 2-й картинке у нас прайсы от 4 и от 11 мая 2010 года. Часть товаров в них не совпадает — вот как узнать, что это за товары.

— Создаём в книге ещё один лист и копируем в него списки товаров и из первого, и из второго прайса (картинка 3).

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

— В меню выбираем «Данные» — «Фильтр» — «Расширенный фильтр» (картинка 4).

— В появившемся окне отмечаем три вещи: а) скопировать результат в другое место; б) поместить результат в диапазон — выберите место, куда хотите записать результат, в примере это ячейка D4; в) поставьте галочку на «Только уникальные записи» (картинка 5).

— Нажимаем кнопку «ОК» и, начиная с ячейки D4, получаем список без дублей (картинка 6).

— Удаляем первоначальный список товаров.

— Добавляем колонки для загрузки значений прайса за 4 и 11 мая и колонку сравнения.

— Вводим в колонку сравнения формулу =D5-C5, которая будет вычислять разницу (картинка 7).

— Осталось автоматически загрузить в колонки «4 мая» и «11 мая» значения из прайсов. Для этого используем функцию: =ВПР( искомое_значение; таблица; номер_столбца; интервальный _просмотр).

— «Искомое_значение» — это строчка, которую мы будем искать в таблице прайса. Легче всего искать товары по их наименованию (картинка 8).

— «Таблица» — это массив данных, в котором мы будем искать нужное нам значение. Он должен ссылаться на таблицу, содержащую прайс от 4-го числа(картинка 9).

— «Номер_столбца» — это порядковый номер столбца в диапазоне, который мы задали для поиска данных. Для поиска мы определили таблицу из двух столбцов. Цена содержится во втором из них (картинка 10).

— Интервальный_просмотр. Если таблица, в которой вы ищете значение, отсортирована по возрастанию или по убыванию, надо ставить значение ИСТИНА, если не отсортирована — пишете ЛОЖЬ.

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

— В итоговом столбце отражается разница в ценах по тем позициям, которые есть и в том и в другом прайсе. Если в итоговом столбце отражается #Н/Д, это значит, что указанный товар есть только в одном из прайсов, а следовательно, разницу вычислить невозможно.

Оценка инвестиций

В Excel можно посчитать чистый дисконтированный доход (NPV), то есть сумму дисконтированных значений потока платежей на сегодняшний день. В примере рассчитана величина NPV на основе одного периода инвестиций и четырёх периодов получения доходов (строка 3 «Денежный поток»).

— Формула в ячейке B6 вычисляет NPV с помощью финансовой функции: =ЧПС($B$4;$C$3:$E$3)+B3 (картинка 1).

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

— В ячейке С5 результат получен благодаря формуле =C3/((1+$B$4)^C2) (картинка 2).

Читайте также:  Сравнительный анализ графиков продаж в Excel скачать шаблон

— В ячейке C6 тот же результат получен через формулу <=СУММ(B3:E3/((1+$B$4)^B2:E2))>(картинка 3).

Сравнение инвестиционных предложений

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

— В свободную ячейку нужно ввести формулу =npv(b3/12,A8:A12)+A7, где b3 — учётная ставка, 12 — число месяцев в году, A8:A12 — столбец с цифрами поэтапного возврата инвестиций, A7 — необходимая сумма вложений.

— По точно такой же формуле рассчитывается чистая приведённая стоимость другого инвестпроекта.

Вставка обновляемой информации

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

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

  1. Выберите Данные ► Получение внешних данных ► Из Интернета для открытия диалогового окна Создание веб-запроса.
  2. В поле Адрес введите URL сайта и нажмите кнопку Пуск. Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса, вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
  3. Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
    примера я щелкну на стрелке рядом с таблицей курсов.
  4. Нажмите кнопку Импорт для открытия диалогового окна Импорт данных.
  5. В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
  6. Нажмите кнопку ОК, и Excel импортирует данные (рис. 176.2).

Рис. 176.2. Данные, импортированные из веб-страницы

Рис. 176.2. Данные, импортированные из веб-страницы

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

Иконка программы

При заполнении больших документов с множеством параметров появляется желание как-то упростить процесс. Для этого может быть полезным знание, как создать выпадающий список в excel. Это иногда может избавить от необходимости вводить одни и те же параметры. К примеру, когда вы заполняете названия брендов компании, которые повторяются, то в следующей ячейке страницы (без пропусков) нажмите Alt+стрелка вниз. У вас появится перечень введенных ранее данных этого столбца, из которого есть возможность выбрать необходимый вариант.

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

  1. Открыть вверху пункт «файл».
  2. Перейти в раздел «Параметры».
  3. Во вкладке «Настроить ленту» поставить галочку напротив «Разработчик».

После этого появится дополнительное поле в верхнем меню под названием «Разработчик», где будет кнопка «Вставить». Там будет возможность выбрать пункт «Поле со списком» в подразделе «ActiveX». У вас появится возможность нарисовать поле в любом месте документа Эксель, где необходимо сделать выпадающий перечень. Далее этот элемент следует настроить:

  1. В той же вкладке перейдите на вкладку «Режим конструктора», нажмите кнопку «Свойства».
  2. Откроется много параметров, но основными являются только три, которые следует настроить.
  3. ListFillRange — здесь необходимо задать диапазон используемых значений.
  4. ListRows — тут задается количество данных для выпадающего меню.
  5. ColumnCount — здесь указывается количество столбцов, которые будут использованы.

Пошаговые действия

С функцией мультивыбора

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

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

Напротив ячеек будет возможность мультивыбора, данные из них будут заполняться на той же строке автоматически. Чтобы функция работала верно, нужно использовать программный код, который будет автоматически добавлять значения на ту же строку. Для этого по ярлычку списка кликните правой кнопкой и выберите «Исходный код». Без этого куска на языке программирования функция добавления работать не будет. Появится окно редактора Visual Basic, вставьте следующий кусок кода:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«C2:C5«)) Is Nothing And Target.Cells.Count = 1 Then

If Len(Target.Offset(0, 1)) = 0 Then

Target.Offset(0, 1) = Target

Target.End(xlToRight).Offset(0, 1) = Target

С наполнением

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

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

Раскрывающийся список в ячейке excel

Пример создания

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

Создание выпадающего списка

Для начала рассмотрим самый простой способ, который поможет вам сделать раскрывающийся список. Чтобы осуществить его, делайте всё как в инструкции ниже:

Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

Переходим во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выбираем инструмент проверки данных

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»

Шаг 3. Теперь в поле ввода данных «Источник» вы можете указывать значения, которые будут составлять раскрывающийся список. Сделать это можно следующими способами:

    вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;

В поле «Источник» вводим значения нужных ячеек

Вбиваем в поле ввода адрес первой и последней ячейки через двоеточие, нажимаем «ОК»

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

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

Результат сделанного выпадающего списка

На заметку! Есть ещё один способ указать значение в источнике – написать в поле ввода имя диапазона. Этот способ самый быстрый, но прежде чем прибегать к нему, нужно создать именованный диапазон. О том, как это сделать, мы поговорим позже.

Форма для быстрого создания выпадающего списка

Видео — Создание выпадающих списков в Excel

IPMORTXML для парсинга сайтов в Excel

Синтаксис IMPORTXML в Google Таблице

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

Пример использования IMPORTXML в Google Таблице

  • Ссылка — URL-адрес страницы;
  • Запрос – в формате XPath.

С примером можно ознакомиться в:

Примеры использования IMPORTXML в Google Doc

Парсинг названий

Для работы с парсингом через данную функцию потребуется знание XPATH и составление пути в этом формате. Сделать это можно открыв консоль разработчика. Для примера будет использоваться сайт крупного интернет-магазина и в первую очередь необходимо в Google Chrome открыть окно разработчика кликнув правой кнопкой мыли и в выпавшем меню выбрать «Посмотреть код» (сочетание клавиш CTRL+Shift+I).

Просмотр кода страницы в Google Chrome

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

И как следствие формула:

Пример использования IMPORTXML для парсинга H1 в Google Таблице

Важно! Запрос XPath пишется в кавычках «запрос».

Парсинг различных элементов

Если мы хотим получить баллы, то нам потребуется обратиться к элементу div с классом product-standart-bonus поэтому получаем:

Пример рассмотрения структуры XPath при рассмотрении кода сайта

В этом случае первый тег div обозначает то, откуда берутся данные, когда в скобках [] уточняется его уникальность.

Для уточнения потребуется указать тип в виде @class, который может быть и @id, а после пишется = и в одинарных кавычках ‘значение’ пишется запрос.

Однако, нужное нам значение находиться глубже в теге span, поэтому добавляем /span и вводим:

IMPORTXML в Google Doc с запросом XPath

Парсинг цен без знаний XPath

Если нет знаний XPath и необходимо быстро получить информацию, то требуется выбрав нужный элемент в консоли разработчика кликнуть правой клавишей мыши и в меню выбрать «Copy»-«XPath». Например, при поиске запроса цены получаем:

Копирование XPath для дальнейшего парсинга

Далее используем ее вместе с IMPORTXML.

Использование IMPORTXML в Google Sheets

Все готово цены получены.

Простые формулы с IMPORTXML в Google Sheets

Чтобы получить title страницы необходимо использовать запрос:

Читайте также:  Как получить последнее значение в столбце таблицы Excel

IMPORTXML для парсинга title страницы в Google Sheets

Для вывода description стоит использовать:

IMPORTXML для парсинга description страницы в Google Таблице

Первый заголовок (или любой другой):

IMPORTXML для парсинга H1 страницы в Google Doc

Работа с данными

Обновление данных

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

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

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

Изменить диапазон-источник данных для существующей сводной таблицы можно через специальный диалог Excel 2007-2010. В предыдущих версиях Excel эта интерфейсная возможность реализована в «Мастере работы со сводными таблицами», в случае, когда он запущен из активной сводной таблицы. После открытия мастера необходимо вернуться на один шаг назад:

Исправления источника данных можно также произвести программным способом. Например, через окно вычислений редактора VBA (Immediate):

Чтобы не задумываться над корректностью размеров диапазона-источника данных сводной таблицы, можно изначально при построении задать диапазон строк с большим запасом. Например, зная, что предполагаемый объем строк не превышает 10000, можно сразу задать это значение в виде размера диапазона. Такая избыточность на практике не приведет к видимым замедлениям в работе интерфейса сводной таблицы. Пустые значения в измерениях отчета можно скрыть. Недостаток этого метода проявляется, в первую очередь, при работе с полями типа «дата». Стандартный интерфейс сводной таблицы позволяет реализовать различные группировки при работе с типом «дата» (по месяцам, по кварталам), но при наличии пустых значений эти возможности становятся недоступными, так как Excel определяет такой столбец как текстовый..

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

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

Макрос можно вызывать по событию Worksheet_Activate, либо настроить «горячую» клавишу.

Работа с результатами анализа

Сводная таблица располагается в диапазоне ячеек рабочего листа Excel. Написание формул рабочего листа в границах сводной таблицы не допускается как при вводе вручную, так и программными методами. Теоретически допустима работа с ячейками, располагающимися в пределах границ сводной таблицы, при помощи ссылок для внешних формул. Часто на практике используется также функция VLOOKUP для поиска по столбцу сводной таблицы. Этот способ необходимо использовать с большой осторожностью — интерфейс сводного отчета предполагает изменение положения отображаемых данных относительно прямоугольных координат рабочего листа без какого-либо влияния на источник этих данных. То есть, нет никакой гарантии, что указанная в формуле ссылка внутрь сводной таблицы будет отображать правильное значение при дальнейшей работе с файлом. При этом источник данных может не меняться.

Имеются альтернативные способы обработки результатов сводной таблицы:

  1. Копирование и вставка значений сводной таблицы на другой лист (с использованием функции «Специальная вставка») с дальнейшим поиском дынных уже по этому сформированному диапазону ячеек. Нарушить целостность данных в пределах простой таблицы гораздо сложнее, чем в сводной. Очевидно, что главным недостатком этого способа работы, является использование ручных операций после каждого обновления источника данных.
  2. Использовать возможности функции GETPIVOTDATA (Excel 2002 и более поздние версии). Данная функция предполагает доступ к данным не по координатам рабочего листа, а по измерениям сводной таблицы. Для источников данных типа OLAP-куб предусмотрены специальные функции доступа к данным и измерениям: CUBEVALUE, CUBEMEMBER и другие (Excel 2007-2010). Данный способ работы неудобен, а также существенно замедляет работу, если требуется получить много различных значений сводной таблицы.
  3. Отказаться от сводной таблицы для получения результатов. Вместо этого использовать формулы рабочего листа (см. Часть 1). Этот способ, несмотря на сложность реализации, может оказаться самым удобным в том случае, если на результатах основываются другие вычисления, а источник данных часто обновляется.

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

В новом формате файла xlsx (Excel 2007-2010) существенно изменены возможности интерфейса сводных таблиц. В предыдущие версии интерфейса (97-2003) вносились только «косметические» изменения:

  • Excel 2000 (9.0) – базовая версия интерфейса сводных таблиц.
  • Excel XP (10.0) – новая функция GETPIVOTDATE
  • Excel 2003 (11.0) – похоже, что вообще никаких изменений не вносилось
  • Excel 2007 (12.0) – новая версия интерфейса сводных таблиц с поддержкой расширенных диапазонов. Улучшена производительность, изменен внешний вид интерфейса. Сохранена совместимость со старым форматом.
  • Excel 2010 (14.0) – поддержка надстройки PowerPivot. Работа с обновляемыми OLAP-кубами.

Основные изменения в новом формате файла (2007-2010):

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

Для лучшего понимания отличий скачайте и откройте файлы-примеров nwdata_pivot1.xlsx и nwdata_pivot2.xlsx (в арихиве nwdata_pivot.zip). В первом файле представлен отчет в старом формате, во втором – в новом, исходные данные одинаковые.

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

Вконтакте
Facebook
Twitter
LiveJournal
Мой Мир
Письмо другу
В избранное
Код для блога
Скачать урок

Этот урок можно скачать
в составе одного из наших продуктов:

С помощью фильтров программы «Microsoft ® Excel ®» вы сможете сделать выборку из таблицы по нескольким условиям, оставив на листе только те данные, которые удовлетворяют заданным критериям. Для этого необходимо перейти на вкладку «Данные» и в группе «Сортировка и фильтр» выполнить команду «Фильтр».

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

В появившемся списке выберите условие, которому должны соответствовать искомые данные, например, укажите команду «больше».

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

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

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

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

Указав необходимы параметры, нажмите кнопку «ОК».

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

Использование SELECT и предикатов IN, OR, BETWEEN, LIKE

Предикаты — слова IN, OR, BETWEEN, LIKE в секции WHERE — также позволяют выбрать определённые диапазоны значений (IN, OR, BETWEEN) или значения в строках (LIKE), которые требуется выбрать из таблицы. Запросы с предикатами IN, OR, BETWEEN имеют следующий синтаксис:

Запросы с предикатом LIKE имеют следующий синтаксис:

Пример 7. Пусть требуется выбрать из таблицы Staff имена, должности и число отработанных лет сотрудников, работающих в отделах с номерами 20 или 84. Это можно сделать следующим запросом (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результат выполнения запроса:

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

Пример 8. Пусть теперь требуется выбрать из таблицы Staff те же данные, что и в предыдущем примере. Запрос со словом OR аналогичен запросу со словом IN и перечислением интересующих значений в скобках. Запрос будет следующим (на MS SQL Server — с предваряющей конструкцией USE company1;):

Пример 9. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, зарплата которых между 15000 и 17000 включительно (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результат выполнения запроса:

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

Предикат LIKE используется для выборки тех строк, в значениях которых встречаются символы, указанные после предиката между апострофами (‘).

Пример 10. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и состоят из 7 символов (на MS SQL Server — с предваряющей конструкцией USE company1;):

Читайте также:  3D-меню для анализа дашборда с визуализацией данных в Excel

Символ подчёркивания (_) означает любой символ. Результат выполнения запроса:

Пример 11. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и содержат любые другие буквы в любом количестве (на MS SQL Server — с предваряющей конструкцией USE company1;):

Символ процентов (%) означает любое количество символов. Результат выполнения запроса:

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

Значения, указанные с использованием предикатов IN, OR, BETWEEN, LIKE можно инвертировать при помощи слова NOT. Тогда запрашиваемые данные будут иметь противоположный смысл. Если мы используем NOT IN (20, 84), то будут выведены данные сотрудников, которые работают во всех отделах, кроме имеющих номера 20 и 84. С использованием NOT BETWEEN 15000 AND 17000 можно получить данные сотрудников, зарплата которых не входит в интервал от 15000 до 17000. Запрос с NOT LIKE выведет данные сотрудников, чьи имена не начинаются или не содержат символов, указанных с NOT LIKE.

Написать SQL запросы с SELECT и предикатами IN, NOT IN, BETWEEN самостоятельно, а затем посмотреть решения

Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 12. Вывести список актёров, которые не разу не были утверждены на главную роль. В таблице team данные о главных ролях содержатся в столбце mainteam. Если роль — главная, то в соответствующей строке отмечено ‘Y’.

Пример 13. Вывести список актеров, которые играли во всех спектаклях WilliamShakespeare. Данные об авторах содержается в таблице play в столбце author.

Пример 14. Вывести спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, Group by, Having, AVG, перекрестное соединение таблиц (CROSS JOIN), удобнее без слова JOIN, а с перечислением таблиц через запятую).

Раскрывающийся список с подстановкой данных

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

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

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

Выпадающий список в Excel

Готовый список

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

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

Выпадающий список в Excel

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

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

Выпадающий список в Excel

Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

Как создать раскрывающиеся списки?

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

работа с базой данных в excel

Для того чтобы база данных MS Excel предоставляла возможность выбора данных из раскрывающегося списка, необходимо создать специальную формулу. Для этого нужно присвоить всем сведениям о родителях диапазон значений, имена. Переходим на тот лист, где записаны все данные под названием «Родители» и открываем специальное окно для создания имени. К примеру, в Excel 2007 это можно сделать, кликнув на «Формулы» и нажав «Присвоить имя». В поле имени записываем: ФИО_родителя_выбор. Но что написать в поле диапазона значений? Здесь все сложнее.

Таблицы подстановки данных можно использовать для

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

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

Вычисляемые объекты

Похожий функционал предоставляют вычисляемые объекты. Но они вставляют в таблицу не столбцы, а строки. К примеру, у нас есть сумма продаж менеджеров, а нас интересует сколько составит НДС (18%) от этих продаж и общая сумма с НДС. Создаем вычисляемый объект:

  1. Ставим курсор в любую строчку первого столбца или любой столбец первой строки
  2. Жмем на ленте: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемый объект . Откроется окно вставки:

  1. В поле «Имя» запишем «НДС», в списке «Поля» выбираем «Менеджер»
  2. Кликая дважды на имя каждого менеджера, запишем формулу: =(Алексей+Анна +Виктор +Виктория +Виталий +Денис +Егор +Роман +Светлана)*0,18
  3. Удалим ненужные поля, жмем Ок. Получаем еще одно поле, в котором будет посчитана НДС. Значение в этом поле будет добавлено к общей сумме.

Как случайным образом заполнить значения из списка данных в Excel?

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

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

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

Шаг 1: Щелкните правой кнопкой мыши столбец данного списка и выберите Вставить из контекстного меню.

Шаг 2: Во вставленном столбце введите NO. в качестве заголовка столбца, а затем введите порядковые номера в следующие ячейки, как показано на скриншоте выше:

Шаг 3: Случайным образом заполнять указанные ячейки из данного списка:

случайное заполнение документа из списка 10

(1) В пустую ячейку введите формулу = СЛУЧМЕЖДУ (1,15) , и перетащите маркер заполнения в нужный диапазон.

(2) В ячейку, в которую вы будете вводить имя случайным образом из данного списка, введите формулу = ВПР (C2; $ A $ 1: $ B $ 16,2; FALSE) , и перетащите маркер заполнения в нужный диапазон.

Ноты:

(1) В формуле = СЛУЧМЕЖДУ (1,15) , 1 — наименьший порядковый номер, а 15 — наибольший.

(2) В формуле = ВПР (C2; $ A $ 1: $ B $ 16,2; FALSE) , C2 — это случайный порядковый номер, который вы получили только что, $ A $ 1: $ B $ 16 — это диапазон заданного списка и вставленных порядковых номеров, 2 означает второй столбец в диапазоне $ A $ 1: $ B $ 16.

лента для заметок Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее . Бесплатная пробная версия

Функции RANDBETWEEN и VLOOKUP могут легко помочь вам в случайном заполнении имен из данного списка в Excel. Однако этот метод оставляет две проблемы:

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

Случайно заполняйте значения из списка данных с помощью Kutools for Excel

Чтобы случайным образом заполнить имена из данного списка без дубликатов и сохранить все заполненные имена статичными, вы можете попробовать Kutools for Excel’s Вставить случайные данные утилита, чтобы сделать это с легкостью.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

Шаг 1: Нажмите Kutools > Вставить > Вставить случайные данные.
документ вставить случайные данные

Шаг 2: В открывшемся диалоговом окне Вставка случайных данных перейдите к Пользовательский список , нажмите Добавить кнопка; затем в открывшемся диалоговом окне Create Sequence щелкните значок кнопка; в новом диалоговом окне укажите диапазон заданного списка имен; следующий щелчок OK > Ok.
случайное заполнение документа из списка 7

Шаг 3: Теперь указанный список имен добавлен как настраиваемый список в диалоговом окне «Вставить случайные данные». Выберите диапазон, в который вы будете случайным образом заполнять имена из данного списка, нажмите кнопку Ok или Применить кнопку.
случайное заполнение документа из списка 9

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

Ссылка на основную публикацию