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

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

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

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

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

Выберите таблицу Откройте вкладку «Конструктор» Напечатайте имя таблицы.

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

Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.

Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

В левой части окна находится дерево данных со всеми доступными таблицами Excel.

Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).

Выберите целые таблицы или конкретные поля.

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools используетстандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите Выполнить SQL на вкладке XLTools Выберите поля, которые нужно включить в объединённую таблицу.

По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.

Источник:
http://xltools.net/ru/sql-queries-in-excel/

Запрос на выборку данных (формулы) в EXCEL

13 апреля 2013 г.

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

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

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание . Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список .

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).

Читайте также  Как в Excel пронумеровать строки по порядку

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .

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

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

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

Наиболее популярные статьи из этого раздела:

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

В качестве исходной возьмем таблицу партий товаров.

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

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

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

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

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

Остальная часть формулы аналогична рассмотренным выше.

12. Актуальная цена

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

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

2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

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

В файле примера приведено решение на листе 12. Актуальная цена.

Источник:
http://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel

Excel — Подключение и получение данных с SQL сервера

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

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

Задача для получения данных в Excel

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

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

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

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Настройка Excel для получения данных с SQL сервера

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

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

Читайте также  Тестирование в эмпирическом исследовании по психологии; особенности сбора и обработки данных в дипломной работе

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

  • Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
  • Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

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

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

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

В итоге у меня загрузятся из базы вот такие данные:

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

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Источник:
http://info-comp.ru/obucheniest/375-excel-get-data-from-sql-server.html

Как запустить SQL-запрос в таблице Excel?

Я пытаюсь создать под-таблицу из другой таблицы всех полей фамилии, отсортированных A-Z, которые имеют поле номера телефона, которое не является нулевым. Я мог бы сделать это довольно легко с SQL, но я понятия не имею, как запустить SQL-запрос в Excel. У меня возникает соблазн импортировать данные в postgresql и просто запросить их там, но это кажется немного чрезмерным.

для того, что я пытаюсь сделать, SQL query SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname будет делать трюк. Это кажется слишком простым, чтобы быть чем-то. что Excel не может изначально. Как я могу запустить SQL-запрос из Excel?

11 ответов

есть много прекрасных способов сделать это, которые другие уже предложили. Следуя вдоль «получить данные Excel через SQL track», вот некоторые указатели.

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

в составе Microsoft Office (и ОС) есть два интересующих поставщика: старый » Microsoft.Реактивный.OLEDB», и последний «Microsoft.ТУЗ.Для oledb». Ищите их при настройке соединения (например, с помощью мастера подключения к данным).

после подключения к книге Excel рабочий лист или диапазон эквивалентны таблице или представлению. Имя таблицы рабочего листа — это имя рабочего листа со знаком доллара ( » $ » ), добавленным к нему, и окруженным квадратными скобками («[» и «]»); диапазона, это просто имя диапазона. Чтобы указать неназванный диапазон ячеек в качестве источника записей, добавьте стандартные обозначения строк/столбцов Excel в конец имени листа в квадратных скобках.

собственный SQL будет (более или менее) SQL Microsoft Access. (В прошлом это называлось JET SQL; однако Access SQL эволюционировал, и я считаю, что JET является устаревшим old tech.)

пример чтения рабочего листа: выберите * из [Sheet1$]

пример, чтение диапазона: выберите * из Миранж!—3—>

пример чтения неназванного диапазона ячеек: выберите * из [Sheet1$A1: B10]

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

по умолчанию предполагается, что первая строка источника данных Excel содержит заголовки столбцов, которые могут использоваться как имена полей. Если это не так, вы должны включить этот параметр выкл., или ваша первая строка данных «исчезает» для использования в качестве имен полей. Это делается путем добавления необязательного параметра HDR= в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, равно HDR=Yes. Если у вас нет заголовков столбцов, вам нужно указать HDR=No; поставщик называет ваши поля F1, F2 и т. д.

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

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

типы данных (стоит попробовать) для CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Источник:
http://askdev.ru/q/kak-zapustit-sql-zapros-v-tablice-excel-46474/

Microsoft Excel

трюки • приёмы • решения

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

Сейчас мы выполним несколько сравнительно легких операций по созданию документа, представляющего собой Project Request Form (Форма запроса на выполнение проекта), и обеспечим к нему доступ остальных участников проекта и заинтересованных сторон (этот документ, а также все остальные формы, которые вам встретятся в наших материалах, представлены внизу каждой статьи). Разумеется, вы можете модифицировать эту форму по собственному усмотрению.

Читайте также  Как загрузить рекламную кампанию в т при помощи Excel

Если вы начинаете работу «с чистого листа», откройте Excel и создайте требуемый документ с помощью полей шаблона, показанного ранее. Создав этот документ, вы можете сохранить его на SharePoint. Однако в этом случае вам нет необходимости сохранять всю электронную таблицу — достаточно сохранить лишь определенный диапазон ячеек. Это требуется для того, чтобы минимизировать объем данных про новые фильмы онлайн 2012 года, совместно используемых на SharePoint.

Выделите все ячейки этой формы, щелкните правой кнопкой мыши в любом месте выделенного диапазона и, как показано на рис. 3.3, в появившемся контекстном меню выберите команду Name a Range (Имя диапазона). В результате ваших действий на экране появится диалоговое окно New Name (Создание имени), которое показано на рис. 1. В данном случае выделенному диапазону ячеек мы присвоили имя
ProjectRequestForm (см. рис. 2). Обратите внимание: в поле Name (Имя) диалогового окна New Name (Создание имени) не допускается использование пробелов или специальных символов.

Рис. 1. Присвоение познания диапазону

Из раскрывающегося списка Scope (Область) следует выбрать область (рабочей книги), в которой будет храниться имя указанного вами диапазона ячеек — Workbook (Рабочая книга), Sheet 1 (Лист 1), Sheet 2 (Лист 2) или Sheet 3 (Лист 3). (По умолчанию в каждой новой рабочей книге Excel автоматически создает три рабочих листа. Если в вашей рабочей книге больше рабочих листов, то их имена будут отображены в раскрывающемся списке Scope (Область).)

Рис. 2. Диалоговое окно New Name

Если всписке Scope (Область) выбран элемент Workbook (Рабочая книга), то указанное вами имя диапазона ячеек появится в диалоговом окне Go То (Переход), независимо от того, какой рабочий лист активизирован в данный момент. Если же в списке Scope выбрано название только какого-то одного рабочего листа, то в диалоговом окне Go То (горячая клавиша F5) будут перечислены лишь те именованные диапазоны, которые находятся в пределах данного рабочего листа.

Предположим, что в раскрывающемся списке Scope (Область) вы выбрали элемент Sheet 1 (Лист 1). Затем активизировали другой рабочий лист и нажали клавишу F5, чтобы открыть диалоговое окно Go То (Переход). В этом случае в списке диалогового окна Go То имя указанного вами диапазона ячеек будет отсутствовать.

В поле Comment (Примечание) вводят замечания относительно выделенного диапазона ячеек. В поле Refers То (Диапазон) отображен фактический адрес выделенного диапазона ячеек, которому вы присваиваете имя. В данном случае выделенный диапазон находится на рабочем листе Sheet 1 и занимает ячейки с A1 по В24. Символ $ означает, что ссылка из любого другого места рабочей книги на данный диапазон ячеек всегда будет абсолютной. Иными словами, ссылка на ячейки не изменится, оставаясь всегда одной и той же: начиная со столбца А, ячейка 1 и заканчивая столбцом В, ячейка 24.

Теперь вам необходимо опубликовать свою работу. Существует множество способов сохранения документа в Excel, однако в пашем случае документ необходимо опубликовать на сайте SharePoint (или MOSS), чтобы этим документом могли воспользоваться другие участники проекта и заинтересованные стороны.

Источник:
http://excelexpert.ru/kak-sozdat-formu-zaprosa-na-vypolnenie-proekta-s-pomoshhyu-excel

Блог Мачула Владимира

  • Home »
  • Редактор Excel »
  • Запросы в Excel. Часть 1

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

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

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

Пускай наша таблица содержит такие поля:

  • Таб_N
  • Фамилия
  • Имя
  • Отчество
  • Пол
  • Дата рождения
  • ИНН
  • Должность
  • Отдел
  • Последний отпуск (дата)
  • Прием на работу (дата)
  • Уволен (дата)

Думаю, для иллюстрации работы этого будет достаточно.

Теперь немного напомню, какие требования к таблице стоит соблюдать:

  1. Она не упорядочена сверху вниз — поэтому я не вводил нумерацию, а оставил табельный номер. Новых сотрудников можно просто добавлять снизу или даже куда-нибудь в середину.
  2. Нет одинаковых строк — у нас в любом случае за это отвечает табельный номер.
  3. Данные в столбцах должны представлять собой логически неделимую информацию. Хорошо видно на Ф.И.О. Если мы укажем всё в одном столбце, то найти человека или отсортировать данные по имени-отчеству будет проблематично.

Ещё один момент! Этой таблице нужно присвоить ИМЯ. Допустим, банально, но привычно — Кадры. 🙂 Я имею в виду не имя файла, а задать имя для таблицы средствами редактора. Это делается командой Имя таблицы, расположенной на закладке Конструктор таблицы в области Свойства. Благодаря имени мы сможет обращаться к таблице из других файлов (книг) Excel и видеть её поля и данные.

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

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

Вот такую таблицу мы получим:

Я не заносил туда много данных, лишь бы хватало данных проиллюстрировать возможности, и не сортировал ни по одному из столбцов.

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

Источник:
http://machula.ru/zaprosyi-v-excel-chast-1/