Создать программу в excel

VBA Excel. Начинаем программировать с нуля

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

Знакомство с редактором VBA

  1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
  2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

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

  1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

  1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

  1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

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

  1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox “Привет” .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

  1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.


Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

Источник: vremya-ne-zhdet.ru

Создание приложения Canvas из Excel в Power Apps Create a canvas app from Excel in Power Apps

В этом разделе вы создадите первое приложение Canvas в Power Apps, используя данные из таблицы Excel. In this topic, you’ll create your first canvas app in Power Apps using data from an Excel table. Выберите файл Excel, создайте приложение, а затем запустите созданное приложение. You’ll select an Excel file, create an app, and then run the app that you create. Каждое созданное приложение включает экраны для просмотра записей, отображения сведений о записи и создания или обновления записей. Every created app includes screens to browse records, show record details, and create or update records. Вы можете быстро создать приложение, использующее данные Excel, а затем настроить его так, как вам требуется. By generating an app, you can quickly get a working app using Excel data, and then you can customize the app to better suit your needs.

Файл Excel должен находиться в облачной учетной записи хранения, например OneDrive, Dropbox или Google Диск. The Excel file must be in a cloud-storage account, such as OneDrive, Google Drive, or Dropbox. В этой статье используется OneDrive для бизнеса. This topic uses OneDrive for Business.

Если у вас нет лицензии на Power Apps, вы можете зарегистрироваться бесплатно. If you don’t have a license for Power Apps, you can sign up for free.

Технические условия Prerequisites

Для точного выполнения инструкций в этой статье скачайте файл FlooringEstimates в Excel и сохраните его в облачной учетной записи хранения. To follow this topic exactly, download the Flooring Estimates file in Excel, and save it in your cloud storage account.

Вы можете использовать собственный файл Excel, однако данные в нем должны быть отформатированы в виде таблицы. You can use your own Excel file, but the data must be formatted as a table. Дополнительные сведения см. в разделе Форматирование таблицы. For more information, see Format a table.

Создание приложения Create the app

В области Создавайте собственные приложения наведите указатель на плитку Начать с данных и выберите команду Создать это приложение. Under Make your own app, hover over Start from data, and then select Make this app.

В области Создать на основе своих данных выберите Макет телефона на плитке своей облачной учетной записи хранения. Under Start with your data, click or tap Phone layout on the tile for your cloud-storage account.

Если появится запрос, нажмите Подключиться и предоставьте данные этой учетной записи. If prompted, click or tap Connect, and provide your credentials for that account.

В разделе Choose an Excel file (Выбор файла Excel) найдите файл FlooringEstimates.xlsx и выберите его. Under Choose an Excel file, browse to FlooringEstimates.xlsx, and then click or tap it.

В разделе Choose a table (Выбор таблицы) щелкните FlooringEstimates, а затем нажмите кнопку Подключить. Under Choose a table, click or tap FlooringEstimates, and then click or tap Connect.

Запуск приложения Run the app

Откройте режим предварительного просмотра, нажав клавишу F5 (либо нажав значок воспроизведения в правом верхнем углу). Open Preview by pressing F5 (or by clicking or tapping the play icon near the upper-right corner).

Измените порядок сортировки, нажав значок сортировки в правом верхнем углу. Toggle the sort order by clicking or tapping the sort icon near the upper-right corner.

Отфильтруйте список, введя или вставив один или несколько символов в поле поиска. Filter the list by typing or pasting one or more characters in the search box.

Например, введите или вставьте honeytoken , чтобы отобразить единственную запись, для которой эта строка отображается в названии продукта, категории или обзоре. For example, type or paste Honey to show the only record for which that string appears in the product’s name, category, or overview.

Читайте также:  Возведение в степень в excel

Добавить запись: Add a record:

Выберите значок “плюс”. Select the plus icon.

Добавьте необходимые данные, а затем щелкните значок флажка, чтобы сохранить изменения. Add whatever data you want, and then select the checkmark icon to save your changes.

Изменить запись: Edit a record:

Выберите стрелку для записи, которую требуется изменить. Select the arrow for the record that you want to edit.

Выберите значок карандаша. Select the pencil icon.

Обновите одно или несколько полей, а затем щелкните значок флажка, чтобы сохранить изменения. Update one or more fields, and then select the checkmark icon to save your changes.

В качестве альтернативы щелкните значок Отмена, чтобы отменить изменения. As an alternative, select the cancel icon to discard your changes.

Удаление записи. Delete a record:

Щелкните стрелку “Далее” для записи, которую необходимо удалить. Select the next arrow for the record that you want to delete.

Щелкните значок корзины. Select the trash icon.

Дальнейшие действия Next steps

Настройте экрана обзора по умолчанию в соответствии со своими потребностями. Customize the default browse screen to better suit your needs. Например, можно сортировать и фильтровать список только по имени продукта, а не по категории или обзору. For example, you can sort and filter the list by product name only, not category or overview.

Источник: docs.microsoft.com

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

1. Создаем сам алгорим вычисления и необходимые формулы в Excel
2. Проверяем работу программы – т.е. выполняем тестирование программы в Exсel
3. Подбираем картинку для фона – редактируем ее в Photoshop
4. Вставляем картинку фона в Excel
5. Тестируем и подправляем полученную программу – если в этом есть необходимость

1. Создаем сам алгорим вычисления и необходимые формулы в Exсel

1.1 Создадим программу для вычисления необходимого количества штучных материалов – для покрытия кровли
Исходные данные:
– обычно известно размер здания – по крайней мере, его можно измерить
– форма кровли выбирается исходя из визуальных предпочтений
– материал кровли выбирается исходя из имеющийся информации и толщины кошелька
– выбирается окончательный вид кровли – односкатная, двухскатная, четырехскатная и т.д.-
в любом случае кровля состоит из участков имеющих форму: треугольник, квадрат, параллелограмм
– количество материала необходимое для покрытия отдельного участка кровли зависит от:
размер отдельного участка в плане здания;
геометрический вид участка кровли;
уклон кровли;
размер штучного материала;
площадь эффективного перекрытия единицы штучного материала;
– суммарная площадь всех участков кровли и общая потребность в материалах верхнего покрытия с учетом отходов.
1.2 Предположим размер здания в плане 12 х 9 м (исходя из модуля 3 м)
1.3 Форма кровли – четырехскатная (вальмовая) – т.е. все четыре ската имеют наклон щипцы расположены в 4,5 м от торцов здания, карнизные свесы 250 мм, выход крайнего листа за лобовую доску 150мм, кровля без мансардная, высота от конька до чердачного перекрытия 4,5 м
1.4 Материал кровли – волнистые асбестоцементные листы – размеров 1750 х 1125, площадь эффективного перекрытия 1,5 м2
смежное перекрытие по длине листа 250 мм (сверху и по низу листа) и боковое перекрытие 125 мм ( с двух сторон)
1.5 Уклон скатов кровли определим расчетно – исходя из проекции скатов на чердачное перекрытие
Для того чтобы немного усложнить задачу – предположим что конек кровли смещен в сторону главного фасада –
то есть находится на расстоянии 3+6 (если смотреть в плане)
1.6 Создаем формулы:
A – ширина здания в плане
L – длина здания в плане
H – заданная высота кровли
C – длина ската
K – величина карнизных свесов
S – площадь скатов здания
B1 – расстояние от стены до конька по скату 1 (проекция)
B2 – расстояние от стены до конька по скату 2 (проекция)
F1 – расстояние от стены до конька по боковому скату 1 (проекция)
F2 – расстояние от стены до конька по боковому скату 2 (проекция)
1.7 Выводим все это на листе – вводим формулы.
2. Проверяем все работает
3. Через поиск Яндекса находим нужную по теме картинку – вставляем ее в Photoshop на новый слой – прозрачность слоя задаем от 10 до 20%
сохраняем картинку на жесткий диск компьютера.
4. В Excel, через “Разметка страницы” -> “Вставить фон” = картинку вставляем на выбранный лист с программой
5. Проверяем и при необходимости правим созданную программу.
6. По образу и подобию – есть возможность создавать не сложные программы для повседневных вычислений
7. Есть возможность немного усложнить программу – тем самым расширив функционал. Делается это через создание полей с выпадающими списками – в них будет возможность выбрать заранее заданные значения. И еще как вариант – можно задать выбор данных, через применение функции “ЕСЛИ”
8. Программу лучше сохранить в двух вариантах – один “только для чтения” и второй “рабочий вариант” – так больше вероятность, что вы ее
не измените до не рабочего состояния, после первого же запуска. Как сделать только для чтения? В проводнике на файле программы – кликаем правой кнопкой мыши и в контекстном меню выбираем “Свойства”. В атрибутах файла ставим галочку в поле “Только чтение”
и нажимаем “Применить”. Работать лучше на копии – но там глочку только чтение необходимо снимать.
9. Для работы – необходимо подставить свои данные в не выделенные поля и получить результат.

Источник: pc.krognet.com

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

1. Создаем сам алгорим вычисления и необходимые формулы в Excel
2. Проверяем работу программы – т.е. выполняем тестирование программы в Exсel
3. Подбираем картинку для фона – редактируем ее в Photoshop
4. Вставляем картинку фона в Excel
5. Тестируем и подправляем полученную программу – если в этом есть необходимость

1. Создаем сам алгорим вычисления и необходимые формулы в Exсel

1.1 Создадим программу для вычисления необходимого количества штучных материалов – для покрытия кровли
Исходные данные:
– обычно известно размер здания – по крайней мере, его можно измерить
– форма кровли выбирается исходя из визуальных предпочтений
– материал кровли выбирается исходя из имеющийся информации и толщины кошелька
– выбирается окончательный вид кровли – односкатная, двухскатная, четырехскатная и т.д.-
в любом случае кровля состоит из участков имеющих форму: треугольник, квадрат, параллелограмм
– количество материала необходимое для покрытия отдельного участка кровли зависит от:
размер отдельного участка в плане здания;
геометрический вид участка кровли;
уклон кровли;
размер штучного материала;
площадь эффективного перекрытия единицы штучного материала;
– суммарная площадь всех участков кровли и общая потребность в материалах верхнего покрытия с учетом отходов.
1.2 Предположим размер здания в плане 12 х 9 м (исходя из модуля 3 м)
1.3 Форма кровли – четырехскатная (вальмовая) – т.е. все четыре ската имеют наклон щипцы расположены в 4,5 м от торцов здания, карнизные свесы 250 мм, выход крайнего листа за лобовую доску 150мм, кровля без мансардная, высота от конька до чердачного перекрытия 4,5 м
1.4 Материал кровли – волнистые асбестоцементные листы – размеров 1750 х 1125, площадь эффективного перекрытия 1,5 м2
смежное перекрытие по длине листа 250 мм (сверху и по низу листа) и боковое перекрытие 125 мм ( с двух сторон)
1.5 Уклон скатов кровли определим расчетно – исходя из проекции скатов на чердачное перекрытие
Для того чтобы немного усложнить задачу – предположим что конек кровли смещен в сторону главного фасада –
то есть находится на расстоянии 3+6 (если смотреть в плане)
1.6 Создаем формулы:
A – ширина здания в плане
L – длина здания в плане
H – заданная высота кровли
C – длина ската
K – величина карнизных свесов
S – площадь скатов здания
B1 – расстояние от стены до конька по скату 1 (проекция)
B2 – расстояние от стены до конька по скату 2 (проекция)
F1 – расстояние от стены до конька по боковому скату 1 (проекция)
F2 – расстояние от стены до конька по боковому скату 2 (проекция)
1.7 Выводим все это на листе – вводим формулы.
2. Проверяем все работает
3. Через поиск Яндекса находим нужную по теме картинку – вставляем ее в Photoshop на новый слой – прозрачность слоя задаем от 10 до 20%
сохраняем картинку на жесткий диск компьютера.
4. В Excel, через “Разметка страницы” -> “Вставить фон” = картинку вставляем на выбранный лист с программой
5. Проверяем и при необходимости правим созданную программу.
6. По образу и подобию – есть возможность создавать не сложные программы для повседневных вычислений
7. Есть возможность немного усложнить программу – тем самым расширив функционал. Делается это через создание полей с выпадающими списками – в них будет возможность выбрать заранее заданные значения. И еще как вариант – можно задать выбор данных, через применение функции “ЕСЛИ”
8. Программу лучше сохранить в двух вариантах – один “только для чтения” и второй “рабочий вариант” – так больше вероятность, что вы ее
не измените до не рабочего состояния, после первого же запуска. Как сделать только для чтения? В проводнике на файле программы – кликаем правой кнопкой мыши и в контекстном меню выбираем “Свойства”. В атрибутах файла ставим галочку в поле “Только чтение”
и нажимаем “Применить”. Работать лучше на копии – но там глочку только чтение необходимо снимать.
9. Для работы – необходимо подставить свои данные в не выделенные поля и получить результат.

Читайте также:  Замена эксель

Источник: pc.krognet.com

Как работать в Excel с таблицами для чайников: пошаговая инструкция

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

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

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

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

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» – «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» – «Ширина столбца» – вводим заданный программой показатель (как правило это 8,43 – количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

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

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+”=”).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+”=”.

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Читайте также:  Excel готовые шаблоны

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

Как создать таблицу в Excel: пошаговая инструкция

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

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» – инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» – «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

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

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+”=”).

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

Иногда пользователю приходится работать с огромными таблицами. Чтобы посмотреть итоги, нужно пролистать не одну тысячу строк. Удалить строки – не вариант (данные впоследствии понадобятся). Но можно скрыть. Для этой цели воспользуйтесь числовыми фильтрами (картинка выше). Убираете галочки напротив тех значений, которые должны быть спрятаны.

Источник: exceltable.com

5 полезных функций Excel для начинающих программистов

Рабо­ту в таб­лич­ном редак­то­ре Excel тоже мож­но авто­ма­ти­зи­ро­вать и запро­грам­ми­ро­вать. Вы смо­же­те здо­ро­во помочь сво­им бух­гал­те­рам с отчё­та­ми, мар­ке­то­ло­гам — с ана­ли­зом рекла­мы, а сами научи­тесь раз­би­рать­ся в алго­рит­мах.

Мы раз­бе­рём 5 полез­ных функ­ций Excel. Если вы дума­е­те, что Excel — это толь­ко про циф­ры и таб­лич­ки, то вот вам видео, где в Excel запро­грам­ми­ро­ва­ли насто­я­щую 3D-игру.

Что­бы было про­ще разо­брать­ся, все фор­му­лы и при­ме­ры мы запи­са­ли в Excel-файл, кото­рый мож­но ска­чать и исполь­зо­вать для экс­пе­ри­мен­тов. Каж­дый при­мер — на отдель­ной вклад­ке сни­зу.

Это одна из самых важ­ных функ­ций в Excel. Как и в насто­я­щем язы­ке про­грам­ми­ро­ва­ния, она про­ве­ря­ет какое-то усло­вие, и если оно выпол­ня­ет­ся — пишет в ячей­ку что-то одно, а если нет — пишет что-то дру­гое.

В общем виде она выгля­дит так:

В усло­вии может быть что угод­но: срав­не­ние яче­ек, дру­гие фор­му­лы, срав­не­ния и мате­ма­ти­че­ские коман­ды — всё, что вам нуж­но про­ве­рить.

На прак­ти­ке мож­но сде­лать, напри­мер, так: пусть Excel про­ве­ря­ет воз­раст, и пишет доку­мент, кото­рый в этом воз­расте удо­сто­ве­ря­ет лич­ность. До 14 лет это сви­де­тель­ство о рож­де­нии, а после — пас­порт. Для это­го исполь­зу­ем такую коман­ду в ячей­ке:

Про­грам­ма про­ве­рит, что сто­ит в преды­ду­щей ячей­ке, и если там чис­ло мень­ше 18, то напи­шет «Сви­де­тель­ство о рож­де­нии», а если уже есть 18 лет, то «Пас­порт»:

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто быва­ет нуж­но посчи­тать коли­че­ство строк или запол­нен­ных яче­ек по каким-то пара­мет­рам, напри­мер узнать коли­че­ство работ­ни­ков стар­ше 40 лет, или посчи­тать все стро­ки, где есть сло­во «пере­мен­ная». Для это­го исполь­зу­ют функ­цию СЧЁТЕСЛИ:

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

Допу­стим мы ведём семей­ный бюд­жет и нам нуж­но узнать, сколь­ко раз у нас были покуп­ки боль­ше 5000 руб­лей в одном чеке. Для это­го исполь­зу­ем такое:

Теперь послож­нее. Если нам нуж­но посчи­тать коли­че­ство яче­ек по несколь­ким усло­ви­ям одно­вре­мен­но, то исполь­зу­ют функ­цию СЧЁТЕСЛИМН:

То, что в пря­мо­уголь­ных скоб­ках — допол­ни­тель­ные усло­вия, их может быть мак­си­мум 127, а сами скоб­ки при этом ста­вить не нуж­но. Обра­ти­те вни­ма­ние что для каж­до­го ново­го усло­вия мож­но задать свой диа­па­зон. Функ­ция про­ве­рит все усло­вия, каж­дое в сво­ём диа­па­зоне, и если все усло­вия одно­вре­мен­но выпол­ня­ет­ся — посчи­та­ет коли­че­ство таких яче­ек.

Для при­ме­ра най­дём все тра­ты боль­ше 5000 в одном чеке, за кото­рые отве­ча­ет Миша:

Теперь у нас пер­вый диа­па­зон отве­ча­ет за сум­му в чеке, а вто­рой — за име­на, поэто­му и усло­вия у каж­до­го из них раз­ные.

Функ­ция воз­вра­ща­ет мак­си­маль­ное зна­че­ние из выбран­но­го диа­па­зо­на:

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

ПОИСКПОЗ

Поиск пози­ции часто исполь­зу­ют, когда нуж­но най­ти поло­же­ние опре­де­лён­но­го эле­мен­та в таб­ли­це:

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

Давай­те най­дём номер стро­ки с мак­си­маль­ной покуп­кой:

Это чис­ло нам пона­до­бит­ся для сле­ду­ю­щей функ­ции

ИНДЕКС

Эта функ­ция воз­вра­ща­ет зна­че­ние кон­крет­ной ячей­ки в ука­зан­ном диа­па­зоне:

=ИНДЕКС(диапазон; номер_строки; [номер_столбца])

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

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

Когда Excel встре­тит такую фор­му­лу, он возь­мёт наш диа­па­зон из вто­ро­го столб­ца, затем возь­мёт номер стро­ки с самой боль­шой покуп­кой (она у нас в ячей­ке Е10, нашли в про­шлом при­ме­ре), и опре­де­лит, что самую круп­ную покуп­ку сде­лал Миша.

Что дальше

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

Источник: thecode.media