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

Создание и внедрение имен в MS Excel

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

Присвоение имени спектру ячеек

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

1. С помощью команды Сделать из выделенного фрагмента:

Выделяем в столбце таблицы спектр ячеек D5:D60 в блок;
В области Определенные имена на вкладке Формулы ленты меню жмем клавишу Сделать из выделенного (либо нажимается композиция кнопок Ctrl+Shift+F3);
Если имя берется из заголовка столбца, то в выпадающем диалоговом окне ставим галочку напротив пт В строке выше;
жмем клавишу ОК.

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

2. Создание имени через поле Имя:

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

Выделяем спектр ячеек D5:D60; alt=»Ввод имени в Excel» width=»300″ height=»63″ />
В поле Имя вводим имя Доходзаполугодие, заменив отображаемый там адресок первой ячейки спектра;
Подтверждаем ввод нажатием клавиши Enter. Все, имя сотворено!

3. Создание имени через команду Присвоить имя:

Для сотворения имени через клавишу Присвоить имя нужно:

Выделить ячейки D5:D60 без заголовка столбца;
В области Определенные имена на вкладке Формулы ленты меню жмем клавишу Присвоить имя;
В открывшемся окне Создание имени в поле Имя вводится заглавие спектра “Доходзаполугодие”;
В поле Область задаем область деяния этого имени – Лист1 либо Книжка;
Жмем клавишу ОК.

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

Присваивание имен формулам и константам

Присваивать имена константам либо формулам имеет смысл, если они нередко употребляются. К примеру, есть коэффициент трения сталь-сталь, имеющий значение 0,15. Применять просто цифру не удобно, можно скоро запамятовать, что же это все-таки за 0,15 там встречается. Потому будем применять наиболее информационное имя Коэф_тр_стали.

Как сделать имя для константы?

Делаем последующие шаги:

В области Определенные имена на вкладке Формулы ленты меню жмем клавишу Присвоить имя;
В открывшемся окне Создание имени заполняем поля, как приведено на рисунке, введя значение константы в поле Спектр;
С нажатием на клавишу ОК получаем имя, которое можно подставлять в формулы.

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

Интересно почитать: Excel как подсчитать количество символов в ячейке

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

Допустим, имя Коэф_тр_стали необходимо вставить в некоторое выражение =Коэф_тр_стали*5.

Здесь порядок действий последующий:

Ставим курсор в подходящую ячейку;
Вводим символ равенства «=»;
В области Определенные имена на вкладке Формулы ленты меню жмем клавишу Присвоить имя;
В области Определенные имена на вкладке Формулы ленты меню жмем клавишу выпадающего списка Применять в формуле;
Из списка имен избираем необходимое, опосля чего же оно возникает в формуле;
Завершаем построение выражения введя знаки “*5”.

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

Раздельно стоит побеседовать о именах таблиц. Начиная с версии MS Office 2007 таблицы верно создавать через команду Таблица, расположенную в области Таблицы вкладки Вставка. При всем этом Excel автоматом присваивает таблице имя: Таблица1 либо Таблица2 и т.д., но его можно поменять через Конструктор таблиц, чтоб создать наиболее выразительными.

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

Незначительно в стороне от таблицы введем формулу =СУММ(Продукт[Стоимость]). По мере ввода формулы редактор Excel даже предложит избрать посреди остальных имен формул и имя таблицы, и имя столбца. В итоге мы получим сумму по столбцу Стоимость.

Ссылки вида Имя_Таблицы[Имя_столбца] именуются Структурированными ссылками.

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

Функция ЕСЛИ в Excel

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

Лог_выражение – хоть какое значение, формула либо выражение, которое при вычислении дает значение ИСТИНА либо ЛОЖЬ.

Значение_если_правда – значение, которое ворачивается, если Лог_выражение имеет значение ИСТИНА.

Значение_если_ересь – значение, которое ворачивается, если Лог_выражение имеет значение ЛОЖЬ.

К примеру:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
Другими словами, если ячейка А1 не пустая, ворачивается 1, если пустая, то 0.
При всем этом не принципиально будет ли в ячейке А1 число, текст либо любые остальные знаки.

Допустим нам необходимо, за заказ выше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_правда В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

Интересно почитать: В экселе пустая ячейка

Протянем формулу для других заказов.

Неверное построение вложенных функций ЕСЛИ

Нередко бывает, что нужно применить наиболее 1 условия.
Допустим нам нужно при сумме заказа выше 15000 руб, создать скидку 10%, а выше 20000 руб 15%.

Excel принимает до 7 уровней вложения функций и инспектирует условия начиная с первого.

К примеру, мы напишем формулу некорректно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

Другими словами если мы напишем первым условием В2>15000, то для суммы выше 20000, скидка 15% применяться не будет.

К примеру, для ячейки В4 проверится 1-ое Лог_выражение (B4>15000), Excel увидит, что это выражение правильно и применит скидку 10%.

Потому начинать будем с суммы заказа больше 20%.

Как выстроить вложенные функции ЕСЛИ

При помощи мастера функций выберем функцию ЕСЛИ и заполним поля Лог_выражение и Значение_если_правда.

Лог_выражение напишем В2>20000
Значение_если_правда – В2*0,85
В пустое поле Значение_если_ересь нужно встать курсором и в поле имени (слева от строчки формул) избрать функцию ЕСЛИ.

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

В новеньком окне пропишем последующее условие:

Протянем формулу для других заказов.

Функция ИЛИ (итог меняется при выполнении 1-го из нескольких критерий)

Добавим к нашему примеру очередное условие:
Если заказ оплачен наиболее чем на 50%, предоставляется скидка тоже 10%.
Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без конфигураций.

Встаем курсором на вторую функцию ЕСЛИ и жмем на кнопку Вставить функцию (слева от строчки формул).

Покажутся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Встать курсором в пустое поле Лог_выражение и в Поле имени избрать функцию ИЛИ.

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

В показавшемся окне прописываем наши 2 условия:

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строчку автоматом возникает 3-я. Оставляем ее пустой и жмем ОК.

Протянем формулу для других заказов.

Функция И (итог меняется при выполнении нескольких критерий)

Скидки за заказ оставим на прежнем уровне:
Заказ наиболее 20000 – 15%
Заказ наиболее 15000 – 10%

Но если заказ наиболее 15000 и оплачен наиболее чем на 50% – скидка 20%

Для наглядности занесем все наши условия в таблицу

Первую функцию ЕСЛИ оставляем без конфигураций.

Встаем курсором на вторую функцию ЕСЛИ и жмем на кнопку Вставить функцию (слева от строчки формул).

В показавшемся окне стираем Лог_выражение: B2>15000.

Встать курсором в пустое поле Лог_выражение и в Поле имени избрать функцию И.

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

В показавшемся окне вписываем 2 наших условия

Интересно почитать: Как сделать в excel сделать выпадающий список в ячейке

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строчку автоматом возникает 3-я. Оставляем ее пустой и жмем ОК.

При нажатии на ОК окно закроется.

Мы прописали лишь первую строку (Лог_выражение) в нашем втором условии ЕСЛИ. Опять встаем на 2-ую ЕСЛИ и жмем на кнопку Вставить функцию (слева от строчки формул).

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_правда исправляем 0,9 на 0,8, чтоб при соблюдении 2-х критерий Excel считал скидку 20%
Значение_если_ересь стираем B2 (будем прописывать в нее 3-е условие).

Встаем курсором на Значение_если_ересь и в поле имени избираем функцию ЕСЛИ.

Покажется окно в которое мы будем прописывать 3-е условие.

Протягиваем формулу для других заказов.

Направьте внимание в какой последовательности мы применяем вложенность функций ЕСЛИ.

Если мы пропишем 2-м условием Заказ наиболее 15000 – 10%, а 3-м выполнение 2-х критерий, Excel обусловит 2-е условие как правда и 3-е условие инспектировать не будет.

Pandas read_excel() — чтение файла Excel в Python

В Python данные из файла Excel считываются в объект DataFrame. Для этого употребляется функция read_excel() модуля pandas.

Лист Excel — это двухмерная таблица. Объект DataFrame также представляет собой двухмерную табличную структуру данных.

1. Пример использования Pandas read_excel()

Представим, что у нас есть документ Excel, состоящий из 2-ух листов: «Employees» и «Cars». Верхняя строчка содержит заголовок таблицы.

Ниже приведен код, который считывает данные листа «Employees» и выводит их.

1-ый параметр, который воспринимает функция read_excel ()— это название файла Excel. 2-ой параметр (sheet_name) описывает лист для считывания данных.

При выводе содержимого объекта DataFrame мы получаем двухмерные таблицы, идентичные по собственной структуре со структурой документа Excel.

2. Перечень заголовков столбцов листа Excel

Чтоб получить перечень заголовков столбцов таблицы, употребляется свойство columns объекта Dataframe. Пример реализации:

3. Вывод данных столбца

Мы можем получить данные из столбца и конвертировать их в перечень значений. Пример:

4. Пример использования Pandas to Excel: read_excel()

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

5. Чтение файла Excel без строчки заголовка

Если в листе Excel нет строчки заголовка, необходимо передать его значение как None.

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