Создание запроса из таблицы. Агрегирование результатов запроса.

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

#запросы #запросизтаблицы #результатызапроса #агрегированиерезультатов

Друзья!

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

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

Так же напоминаем о нашем втором канале @access_ms о профессиональном программировании на Access

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

Типы данных в Excel.

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

▪️ числовое значение
▪️ текст
▪️ формула
▪️ сообщение об ошибке

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

#ТипыДанных #ЧисловоеЗначение #Текст #Формула #СообщениеОбОшибке

Числовые значения.

Числовые значения, или просто числа, выражают различные количественные соотношения данных определенного типа, например объемы продаж, число служащих фирмы, атомный вес, тестовые оценки и т.д. Числовыми значениями могут быть также выражены даты (например, 28.05.2020) и время (например, 10:02:00).
Excel позволяет довольно эффективно работать с этими типами данных.

Вам может быть интересно, какие типы числовых значений используются в Excel. Например, для вас может быть важно выяснить, насколько большими могут быть числа и насколько точным является представление больших чисел.
Для представления чисел в Excel используются 15 десятичных цифровых позиций. Например, если вы вводите большое число, такое как 123 456 789 123 456 789 (18 цифр), Excel сохраняет его с точностью до 15 цифр: 123 456 789 123 456 000. На первый взгляд, такое ограничение может показаться довольно жестким, но на практике это редко вызывает какие-либо проблемы.
Все же не стоит забывать об этом ограничении при вводе номеров кредитных карт. В большинстве систем номер кредитной карты состоит из 16 цифр. Вследствие указанного ограничения после ввода номера карты Excel автоматически заменяет последнюю цифру нулем. Чтобы обойти данное ограничение, следует вводить номера кредитных карт как текст. Для этого перед вводом измените формат ячеек на Текстовый (выберите команду Главная => Число и в раскрывающемся списке Числовой формат выберите пункт Текстовый). Также можно вводить перед номером кредитной карты какой-либо нечисловой символ, например апостроф. Так или иначе, Excel должен интерпретировать содержимое подобных ячеек как текст, а не как число, - позаботьтесь об этом.
Вот другие числовые ограничения Excel
- Наибольшее положительное число - 9,9Е+307
- Наименьшее отрицательное число - 9,9Е+307
- Наименьшее положительное число - 2,2251Е-308
- Наибольшее отрицательное число - 2,2251Е-308

Эти числа представлены в экспоненциальном формате. А если выразить то же самое обычным языком, то, например, наибольшим положительным числом является произведение чисел 9,9 и 10 в степени 307, другими словами - 99 с 307 нулями. Но помните, что Excel хранит только 15 значащих цифр (первых слева) даже в таких огромных числах, а все остальные цифры этого числа будут заменены нулями.

#ТипыДанных #ЧисловыеЗначения

Текст.

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

#ТипыДанных #Текст

​​Формулы.

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

Формулой считается любое математическое выражение, но в формулах также можно использовать некоторые встроенные в Excel мощные функции. На скриншоте показан рабочий лист, с помощью которого можно вычислить объем ежемесячных выплат по кредиту. В нем содержатся числа, текст и формулы. В ячейках в столбце А содержится текст. В столбце В находятся четыре числа и две формулы (формулы в ячейках В6 и В10). В столбце D представлено то, что в действительности находится в ячейках столбца В.

#ТипыДанных #Формулы

Ввод числовых значений.

Чтобы ввести в ячейку числовое значение, переместите в нее табличный курсор (в результате она станет активной), введите требуемое числовое значение и нажмите клавишу <Enter>, <Tab> или одну из клавиш управления курсором. Значение отобразится в ячейке и в строке формул, поскольку в строке формул всегда отображается содержимое активной ячейки.
При вводе чисел можно использовать десятичные точки (или запятые) и знаки денежных единиц; кроме того, допускаются знаки "плюс", "минус" и "процент". Если поставить перед числом минус или заключить его в скобки, Excel будет считать это число отрицательным. При вводе больших чисел для удобства их чтения можно разделять группы разрядов пробелами: тысячи от единиц, миллионы от тысяч и т.д.

#ЧисловыеЗначения #ВводЧисловыхЗначений

​​Ввод текстовых значений.

Вводить в ячейку текст так же просто, как и числовое значение: для этого следует активизировать нужную ячейку, ввести текстовое значение и нажать клавишу <Enter> или одну из клавиш управления курсором. При вводе текста в одну ячейку можно поместить не более 32 тысяч символов. Вы можете ввести в ячейку огромное количество символов, но отобразить их все на экране одновременно будет непросто.
Если вы введете в ячейку текст, который длиннее текущей ширины столбца, ячейки, расположенные справа, пусты, Excel отобразит текст целиком, заполняя соседние ячейки. Если же соседняя ячейка содержит какие-либо данные, программа отобразит столько символов текста, сколько возможно (весь текст по-прежнему находится в ячейке, просто он не отображается). Если вы хотите, чтобы в ячейке, рядом с которой находится заполненная ячейка, длинная строка текста отображалась полностью, можете сделать следующее:
▪️ сократить текст;
▪️ увеличить ширину столбца (перетащить вправо границу столбца в заголовке);
▪️ выбрать более мелкий шрифт;
▪️ свернуть текст в ячейке таким образом, чтобы он отображался в несколько строк (для этого выберите команду Главная =>Выравнивание => Перенести текст).

Если ввести в ячейку достаточно длинный текст, то он может не поместиться в строку формул. Чтобы отобразить в строке формул больше текста, щелкните на нижней границе строки формул и перетащите эту границу вниз, как показано на скриншоте. Кроме того, можно воспользоваться комбинацией клавиш <Ctrl+Shift+U>. Нажимая эту комбинацию клавиш, можно изменить высоту строки формул, чтобы она показывала одну строку или восстановила свой предыдущий размер.

#ТекстовыеЗначения #ВводТекстовыхЗначений

​​Режим ввода данных.

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

В режиме ввода вы непосредственно вводите что-либо в текущую ячейку. По мере ввода с клавиатуры введенный текст появляется в ячейке и в строке формул. В действительности содержимое ячейки не изменяется до тех пор, пока программа не выйдет из режима ввода, что сопровождается помещением введенных вами данных в ячейку, которая была активной. Чтобы выйти из режима ввода, достаточно нажать клавишу <Enter>, <Tab> или любую из навигационных клавиш, имеющихся на клавиатуре (например, <PageUp> или <Home>). Введенное перед этим значение помещается в ячейку, а в строку состояния выводится сообщение Готово. Также выйти из состояния ввода можно, нажав клавишу <Esc>. Однако в этом случае введенные вами данные игнорируются и в активной ячейке сохраняется ее предыдущее значение.

#РежимВводаДанных

​​Редактирование содержимого ячейки.

Если в ячейке содержится несколько символов, то проще всего заменить их, введя новые данные. Однако, если в ячейке находится длинный текст или сложная формула и нужно внести лишь небольшие изменения, проще будет отредактировать содержимое ячейки, чем вводить всю информацию заново.
Чтобы отредактировать содержимое ячейки, воспользуйтесь одним из перечисленных ниже способов перехода в режим редактирования:
▪️ дважды щелкните на ячейке. Это позволит отредактировать содержимое непосредственно в ячейке.
▪️ нажмите клавишу <F2>. Это тоже позволит отредактировать содержимое непосредственно в ячейке.
▪️ активизируйте ячейку, которую нужно отредактировать, а затем щелкните в строке формул. Это позволит отредактировать содержимое ячейки в строке формул.

⚙️ Во вкладке Дополнительно диалогового окна Параметры Excel есть раздел Параметры правки, содержащий несколько опций, влияющих на возможности редактирования данных в ячейках. (Чтобы открыть диалоговое окно Параметры Excel, выберите команду Файл => Параметры.) Если флажок Разрешить редактирование в ячейках сброшен, то вы не сможете редактировать содержимое прямо в ячейке (двойной щелчок ни к чему не приведет). Кроме того, в этом случае нажатие клавиши <F2> позволит редактировать содержимое ячейки в строке формул, но не в самой ячейке.

#РежимРедактирования

​​Автоматическое перемещение табличного курсора после ввода данных.

По умолчанию нажатие клавиши <Enter> после ввода данных в ячейку позволяет автоматически переместить табличный курсор на следующую ячейку внизу. Для того чтобы изменить эту установку, выберите команду Файл => Параметры и щелкните на вкладке Дополнительно. Опция, управляющая описанной возможностью, называется Переход к другой ячейке после нажатия клавиши ВВОД. Здесь же можно указать направление, в котором будет перемещаться табличный курсор после ввода данных (вниз, влево, вправо или вверх).

#ВводДанных #Параметры

​​Ввод повторяющейся информации.

Когда возникает необходимость ввести одни и те же данные в несколько ячеек, программа Excel предлагает удобный способ ускорения работы. Выделите все ячейки, которые нужно заполнить одинаковыми данными, введите числовое значение, текст или формулу, а затем нажмите <Ctrl+Enter>. В результате одни и те же данные будут введены в каждую ячейку выделенной области.

#ВводДанных

​​Автоматическое назначение десятичных запятых.

Этот способ ввода чисел заинтересует тех, кому приходится вводить множество цифр с фиксированным числом десятичных разрядов. Выберите команду Файл => Параметры и щелкните на вкладке Дополнительно. Установите флажок Автоматическая вставка десятичной запятой и убедитесь в том, что в поле Число знаков после запятой установлено нужное число десятичных разрядов. Если описываемая опция активизирована, Excel автоматически расставляет десятичные запятые по мере ввода чисел. Например, если задать два десятичных знака после запятой, то после ввода в ячейку значения 12345 программа автоматически будет интерпретировать это число как 123,45 (десятичную запятую она также добавит автоматически). Чтобы вернуться к первоначальному режиму, сбросьте флажок этой опции в диалоговом окне Параметры Excel. Установка или отмена данной опции не влияет на ранее введенные числа.

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

#ВводДанных #Параметры

​​Использование функции Автозаполнение.

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

В ячейку А1 было введено число 1, а в ячейку А2 - число 3. После выделения обеих ячеек и перетаскивания маркера автозаполнения вниз была создана последовательность чисел. Также появится пиктограмма, после щелчка на которой можно увидеть некоторые дополнительные параметры режима автозаполнения. Эта пиктограмма появляется лишь в том случае, если на вкладке Дополнительно диалогового окна Параметры Excel в разделе Вырезание, копирование и вставка установлен флажок Отображать кнопку параметров вставки при вставке содержимого. Для определения шаблона автозаполнения Excel проводит анализ содержимого исходно выделенных ячеек. Если в двух выделенных ячейках будут содержаться значения 1 и 2, программа придет к заключению, что значения автоматически заполняемых ячеек должны возрастать на единицу. Если же, как в нашем примере, начальными значениями будут 1 и 3, значения в ячейках будут возрастать с шагом 2. В Excel реализованы широкие возможности распознавания шаблона автозаполнения. Если в исходных ячейках будут находиться значения 31/5/2020 и 30/6/2020, в последующие ячейки будут вставлены даты последних дней следующих один за другим месяцев.

#ВводДанных #Автозаполнение

​​Добавление в ячейку новых текстовых строк.

Если вы вводите в ячейку длинный текст, то можете сделать так, чтобы программа отображала его в нескольких строках в пределах одной ячейки. Для добавления в ячейку новой текстовой строки используется комбинация клавиш <Alt+Enter>. Когда вы вводите символ разрыва строки (нажимаете комбинацию клавиш <Alt+Enter>), Excel автоматически устанавливает для текущей ячейки опцию Переносить по словам. Но, в отличие от обычного режима переноса по словам, в данном случае Excel принудительно переносит текст на новые строки именно там, где вы указали. Эта опция предоставляет дополнительные возможности при форматировании текста.

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

#ВводДанных

​​Использование функции Автозамена для ускоренного ввода данных.

С помощью функции Автозамена можно создать сокращения для часто употребляемых слов или фраз. Например, если вы работаете в фирме "Рога и копыта", то для этого названия можно создать сокращение "рик" в качестве элемента функции Автозамена. Тогда каждый раз при наборе "рик" Excel автоматически будет вводить в ячейку значение "Рога и копыта".
В Excel встроен достаточно большой список слов, которые будут автоматически заменяться при вводе (в основном это ошибки, которые часто встречаются при вводе). Для настройки функции Автозамена выберите команду Файл => Параметры и во вкладке Правописание щелкните на кнопке Параметры автозамены. В открывшемся диалоговом окне Автозамена введите нужный элемент и его сокращение. При этом вы можете ввести столько элементов автозамены, сколько хотите. Не забывайте, что не следует использовать в качестве Автозамены те сокращения, которые могут встретиться в вашем тексте.

Список Автозамены общий для всех приложений Microsoft Office, поэтому любые элементы Автозамены, созданные, например, в Word, будут доступны и в Excel.

#ВводДанных #Автозамена

​​Форматирование числовых значений.

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

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

#ВводДанных #ФорматированиеЧисел

​​Форматирование чисел с помощью ленточных команд.

В группе Число ленточной вкладки Главная находится несколько кнопок, позволяющих быстро применять самые распространенные числовые форматы. В раскрывающемся списке Числовой формат, расположенном в этой группе команд, находится одиннадцать наиболее применяемых числовых форматов. Кроме них, в группе Главная => Число присутствуют дополнительные кнопки: Финансовый числовой формат (для выбора денежного формата), Процентный формат и Формат с разделителями. В этой группе также содержатся две кнопки: одна - для увеличения разрядности чисел (количества цифр после запятой), а другая - для уменьшения разрядности чисел.
Если выбрать один из этих форматов, то он сразу будет применен к активной ячейке. Можно также выделить диапазон ячеек (или даже всю строку либо столбец рабочего листа), а затем выбрать один из форматов. Если выделено несколько ячеек, то числовой формат будет применен ко всем выделенным ячейкам.

#ВводДанных #ФорматированиеЧисел

​​Форматирование чисел с помощью диалогового окна Формат ячеек.

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

Существует несколько способов открыть диалоговое окно Формат ячеек. Прежде всего выделите ячейку (или ячейки), которую хотите отформатировать, а затем выполните одно из следующих действий:
▪️ Выберите группу команд Главная => Число и щелкните на маленькой пиктограмме, расположенной в правом нижнем углу группы Число.
▪️ Выберите команду Главная => Число, откройте список Числовой формат и выберите в нем пункт Другие числовые форматы.
▪️ Щелкните правой кнопкой мыши на выделенных ячейках и выберите из  контекстного меню команду Формат ячеек.
▪️ Нажмите комбинацию клавиш <Ctrl+1>.

#ВводДанных #ФорматированиеЧисел #ФорматЯчеек

​​Форматирование чисел с помощью диалогового окна Формат ячеек.

Ниже приведены категории числовых форматов с краткими пояснениями.
▪️ Общий. Формат, принятый по умолчанию. При его выборе числа отображаются в виде целых чисел, десятичных дробей или, если число слишком большое и не помещается в ячейке, в экспоненциальном формате.
▪️ Числовой. Позволяет указать количество десятичных знаков, определить разделитель групп разрядов и способ отображения отрицательных чисел (со знаком "минус", красным цветом или красным цветом со знаком "минус").
▪️ Денежный. Позволяет указать количество десятичных знаков, знак денежной единицы и способ отображения отрицательных чисел (со знаком "минус", красным цветом или красным цветом со знаком "минус"). В этом формате всегда используется разделитель групп разрядов.
▪️ Финансовый. Отличается от денежного тем, что знаки денежной единицы всегда выровнены по вертикали.
▪️ Дата. Позволяет выбрать один из форматов отображения дат.
▪️ Время. Предоставляет на выбор несколько форматов отображения значений времени.
▪️ Процентный. Позволяет выбрать количество десятичных знаков. Символ процента отображается всегда.
▪️ Дробный. Позволяет выбрать один из девяти форматов отображения дробных чисел.
▪️ Экспоненциальный. В этом формате число всегда отображается с буквой Е: 2,00Е+05 = 200 000; 2,05Е+05 = 205 000. Вы можете выбрать количество десятичных знаков для отображения. Второй пример можно прочесть как "2,05 раза по 10 в пятой степени".
▪️ Текстовый. Его применение к числовому значению заставляет Excel рассматривать это число как текст (даже если текст выглядит, как число). Эту возможность удобно применять при обработке номеров кредитных карт.
▪️ Дополнительный. Включает четыре дополнительных числовых формата: Почтовый индекс, Индекс+4, Номер телефона и Табельный номер.
▪️ Все форматы. Позволяет создавать собственные числовые форматы, не входящие ни в одну из перечисленных категорий.

#ВводДанных #ФорматированиеЧисел #ФорматЯчеек