Исправление неработающих формул в перенесенных файлах

Использование типа данных Stock в Excel для Интернета

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

Стремянка:

• А. Введите в ячейку имя или тикер акций (например, "AAPL" для Apple).

• б) Прокрутите списки Избранное и Контактные лица для веб-поддержки. Выберите ячейку, а затем перейдите на вкладку Данные на ленте.

• В. В группе Типы данных выберите Акции.

• Г. После того как Excel распознает его как запас, рядом с ячейкой отобразится небольшой значок.

• д. Щелкните маленький значок или нажмите кнопку вставки данных, чтобы получить дополнительные сведения, связанные с акциями (например, цена, рыночная капитализация, 52-недельный максимум/минимум и т. д.).

Пример:

• Если ячейка A1 содержит тиккер акций "AAPL":

• Щелкните Данные > запасы.

• Вы можете извлечь дополнительные сведения, такие как текущая цена и т. д., выбрав эту ячейку, а затем выбрав конкретные данные о акции, например Цена.

Использование Power Query для финансовых данных из веб-API (для опытных пользователей)

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

Стремянка:

• Перейдите на вкладку Данные .

• Выберите Получить > данныхиз Интернета.

• Введите URL-адрес поставщика финансовых данных, например API финансового веб-сайта (например, Yahoo Finance).

• Power Query позволит управлять данными и преобразовывать их перед загрузкой в Excel.

Excel для Интернета не имеет встроенной функции Google Sheets "GOOGLETRANSLATE", которая автоматически переводит текст на разных языках.

Однако функции Excel можно использовать в сочетании с внешними службами, такими как Microsoft Translator, с помощью Power Automate (для веб-переводов).

Обходной путь для Excel в Интернете

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

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

Интеграция Power Automate.

• Вы можете создать рабочий процесс с помощью Power Automate для автоматического перевода текста с выбранного языка на целевой с помощью службы Переводчика Майкрософт.

• Для этого необходимо настроить Power Automate и связать его с Excel Online.

Пример использования Power Automate (Microsoft Translator):

1. Настройте рабочий процесс в Power Automate , который интегрируется с Microsoft Translator.

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

В Excel нет прямого эквивалента функции "ЗАПРОС", доступной в Google Sheets, но аналогичные функции можно реализовать с помощью других встроенных функций Excel, таких как FILTER, LOOKUP, SORT, IF, VLOOKUP и XLOOKUP. Вот как реплицировать варианты использования функции "QUERY" Google Sheets в Excel в Интернете:

1. Базовая фильтрация данных (эквивалент SELECT WHERE)

В Google Sheets вы используете:

=QUERY(A1:D10; "SELECT A, B WHERE C > 100")

В Excel используйте функцию FILTER:

=FILTER(A2:D10, C2:C10 > 100)

При этом извлекаются все строки, в которых значение в столбце "C" больше 100, возвращая столбцы от A до D.

2. Выбор определенных столбцов (эквивалент select)

В Google Sheets:

=QUERY(A1:D10; "SELECT A, C")

В Excel используйте сочетание INDEX и FILTER:

=INDEX(A2:D10, , {1,3})

При этом возвращаются только столбцы "A" и "C" из диапазона "A2:D10".

3. Сортировка данных (эквивалент ORDER BY)

В Google Sheets:

=QUERY(A1:D10, "SELECT * ORDER BY C DESC")

В Excel используйте функцию SORT :

=SORT(A2:D10, 3, -1)

Это сортирует данные в "A2:D10" на основе значений в столбце "C" в порядке убывания.

4. Агрегирование данных (эквивалент GROUP BY)

В Google Sheets:

=QUERY(A1:D10; "SELECT A, SUM(B) GROUP BY A")

В Excel используйте СУММЕСЛИ или СУММЕСЛИ:

=СУММЕСЛИ(B2:B10, A2:A10, A2)

Это суммирует значения в столбце "B", где столбец "A" соответствует определенным условиям, эффективно группируя по "A".

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

5. Условный выбор (эквивалент WHERE с логическими операторами)

В Google Sheets:

=QUERY(A1:D10; "SELECT A, B WHERE C > 100 AND D < 50")

В Excel используйте функцию FILTER с логическими операторами:

=FILTER(A2:D10, (C2:C10 > 100) * (D2:D10 < 50))

При этом выполняется фильтрация строк, в которых столбец "C" больше 100, а столбец "D" меньше 50.

6. Подсчет конкретных условий (эквивалент SELECT COUNT)

В Google Sheets:

=QUERY(A1:D10, "SELECT COUNT(A) WHERE C > 100")

В Excel используйте функцию СЧЁТЕСЛИ или СЧЁТЕСЛИ:

=СЧЁТЕСЛИ(C2:C10; ">100")

Это подсчитывает количество строк, в которых столбец "C" имеет значения больше 100.

7. Использование нескольких критериев (эквивалент where с условиями ИЛИ)

В Google Sheets:

=QUERY(A1:D10, "SELECT * WHERE C > 100 OR D < 50")

В Excel используйте функцию FILTER с оператором "+" для логического ИЛИ:

=FILTER(A2:D10, (C2:C10 > 100) + (D2:D10 < 50))

Возвращает строки, в которых столбец "C" больше 100 или столбец "D" меньше 50.

8. Объединение таблиц (эквивалент join)

В Google Sheets:

=QUERY(A1:D10; "SELECT A, B, E FROM A JOIN B ON A.ID = B.ID")

В Excel используйте XLOOKUP или VLOOKUP для объединения двух таблиц:

=XLOOKUP(A2:A10, F2:F10, G2:G10)

Он ищет значения из таблицы "B" (столбцы "F" и "G") и извлекает соответствующие данные в таблицу "A" на основе соответствующих идентификаторов.

9. Динамическая фильтрация на основе входных данных (аналогично WHERE с переменными)

В Google Sheets:

=QUERY(A1:D10; "SELECT A, B WHERE C = ""&E1&""")

В Excel используйте ФИЛЬТР со ссылками на ячейки:

=FILTER(A2:D10, C2:C10 = E1)

Она фильтрует таблицу на основе значения, введенного в ячейку "E1".

Сводка функций:

• FILTER: фильтрует данные на основе указанных условий.

• SORT: сортирует данные по указанному столбцу.

• INDEX: возвращает определенные строки или столбцы из диапазона.

• SUMIFS: суммирует значения на основе нескольких условий.

• СЧЁТЕСЛИ/ СЧЁТЕСЛИ: подсчитывает строки, соответствующие заданным условиям.

• XLOOKUP/ VLOOKUP: объединяет данные из нескольких таблиц на основе соответствующих значений.

Хотя в Excel нет прямой функции QUERY, такой как Google Sheets, эти сочетания функций Excel охватывают почти все варианты использования для запроса данных.

Ссылки:

Функция filter

Excel для Интернета не имеет прямого эквивалента функции IMPORTHTML Google Sheets, которая позволяет импортировать таблицы или списки с веб-страницы в электронную таблицу.

Однако аналогичные результаты можно достичь с помощью Power Query в классической версии Excel. К сожалению, Power Query недоступны в Excel для Интернета, но на рабочем столе можно выполнить следующие действия:

Действия в Классическом приложении Excel (с помощью Power Query):

1. Откройте Excel (классическая версия).

2. Перейдите на вкладку Данные.

3. Выберите Получить >данных  из Интернета.

4. Введите URL-адрес веб-страницы, содержащей таблицу или список HTML.

5. Выберите таблицу или список на веб-странице, которую вы хотите импортировать.

6. Загрузите данные в Excel.

Импорт в Excel Online:

После импорта данных с помощью Power Query в классической версии можно сохранить файл в OneDrive или SharePoint и продолжить работу с ним в Excel для Интернета. Однако сам импорт должен выполняться с помощью классической версии.

Excel для Интернета не имеет прямого эквивалента функции IMPORTDATA Google Sheets, которая используется для импорта данных из URL-адреса (например, csv или TSV-файлов).

Однако существует альтернативный метод, использующий Power Query в классической версии Excel, который затем можно просматривать и редактировать в Excel для Интернета. Это можно сделать следующим образом:

Действия по импорту данных из URL-адреса в Excel (классическая версия):

• Откройте Excel (классическая версия).

• Перейдите на вкладку Данные.

• Выберите Получить >данных  из Интернета.

• Введите URL-адрес файла (CSV, TSV и т. д.), который требуется импортировать.

• Excel вытащит данные из URL-адреса, и вы можете загрузить их на лист.

• Сохраните файл и отправьте его в OneDrive или SharePoint.

• Теперь вы можете открыть файл и работать с ним в Excel для Интернета, хотя автоматическое обновление и динамический импорт должны выполняться с помощью классической версии.

Ссылка на ссылку:

• Импорт данных из Интернета

• Power Query источников данных в версиях Excel

• Использование Power Query в Excel для Интернета 

Excel для Интернета не имеет прямого эквивалента функции IMPORTFEED Google Sheets, которая импортирует данные RSS-канала или Atom в электронную таблицу.

Однако вы можете достичь чего-то подобного, используя Power Query в классической версии Excel для импорта RSS-каналов, а затем просмотра данных и работы с ним в Excel для Интернета. К сожалению, Excel для Интернета изначально не поддерживает эту функцию.

Действия по импорту RSS-канала в Excel (классическая версия):

• Откройте Excel (классическая версия).

• Перейдите на вкладку Данные.

• Выберите Получить > данныхиз других источников > из Интернета.

• Введите URL-адрес RSS-канала.

• Excel получит данные из RSS-канала и позволит загрузить их на лист.

• Сохраните файл и отправьте его в OneDrive или SharePoint.

• Теперь вы можете открыть этот файл и работать с ним в Excel для Интернета, хотя динамические обновления из веб-канала должны выполняться с помощью классической версии.

Excel для Интернета не имеет прямого эквивалента функции IMPORTXML в Google Sheets, которая позволяет импортировать и анализировать данные из структурированных XML- или HTML-документов с помощью запросов XPath.

Однако аналогичные результаты можно получить с помощью Power Query в классической версии Excel для импорта XML-данных, которые затем можно открыть в Excel для Интернета. Это можно сделать следующим образом:

Действия по импорту XML-данных в Excel (классическая версия):

• Откройте Excel (классическая версия).

• Перейдите на вкладку Данные.

• Выберите Получить данные > из файла > из XML.

• Найдите и выберите XML-файл или вставьте URL-адрес XML-канала.

• откроется Power Query, что позволяет при необходимости просматривать и преобразовывать данные.

• Загрузите данные на лист.

• Сохраните файл и отправьте его в OneDrive или SharePoint.

• Откройте файл и поработайте с ним в Excel для Интернета, хотя импорт XML и все преобразования данных должны выполняться с помощью классической версии.

Excel для Интернета не имеет прямого эквивалента функции "REGEXTRACT" Google Sheets, которая извлекает текст на основе регулярного выражения.

Однако для достижения аналогичных результатов можно использовать сочетание функций Excel. Хотя в Excel нет встроенной поддержки регулярных выражений (регулярных выражений), вы можете извлекать текстовые шаблоны с помощью таких функций, как TEXT, MID, SEARCH и LEFT, в зависимости от сложности ваших потребностей. Для расширенных регулярных задач часто требуется Power Query, но они недоступны в Excel для Интернета.

Пример. Извлечение части текста без регулярных выражений

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

1. Использование "LEFT" и "ПОИСК" для извлечения текста перед разделителем
   
   Например, чтобы извлечь текст перед тире в ячейке A1:
   =LEFT(A1, SEARCH("-", A1) - 1)
   При этом извлекается все до первого дефиса ("-").

2. Использование "MID" и "ПОИСК" для извлечения текста между разделителями
   
   Чтобы извлечь текст между двумя дефисами в ячейке A1, выполните следующие действия:
   =MID(A1, SEARCH("-", A1) + 1, SEARCH("-", A1, SEARCH("-", A1) + 1) - SEARCH("-", A1) - 1)
   При этом текст извлекается между двумя тире ("-") символами.

Использование Power Query (только для настольных компьютеров):

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

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

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

Пример. Использование функции ПОИСКА для простого сопоставления текста

Если вы хотите проверка, существует ли определенная подстрока в ячейке (аналогично базовой функции REGEXMATCH), можно использовать функцию ПОИСКА. Функция SEARCH не так гибка, как регулярные выражения, но она может находить подстроки в строке:

1. Базовый пример:

• Чтобы проверка, существует ли слово "apple" в ячейке "A1":

• =IF(ISNUMBER(SEARCH("apple", A1)), TRUE, FALSE)

• — При обнаружении "apple" формула возвращает значение TRUE.

• — Если нет, возвращается значение FALSE.

Для более сложного сопоставления шаблонов:

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

Excel для Интернета не имеет прямого эквивалента функции "REGEXREPLACE" Google Sheets, которая позволяет заменять части текстовой строки на основе регулярного выражения (регулярного выражения).

Однако в классической версии Excel можно использовать VBA (Visual Basic для приложений) или Power Query для более сложных регулярных замен. В Excel для Интернета вы по-прежнему можете добиться простой замены с помощью функции "ЗАМЕНА", хотя она не так мощна, как регулярное выражение.

Простая альтернатива с использованием "ЗАМЕНЫ" в Excel для Интернета

Для замены основного текста (без использования регулярных выражений) можно использовать функцию SUBSTITUTE:

​​​​​​​

Если вы хотите заменить все вхождения "apple" на "orange" в ячейке "A1", можно использовать:

=SUBSTITUTE(A1, "apple", "orange")

Эта функция заменяет каждое вхождение "apple" в тексте на "orange".

Для замены сложных шаблонов (с использованием регулярных выражений)

Чтобы заменить текст на основе шаблона (регулярного выражения), необходимо:

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

Excel для Интернета не имеет встроенного эквивалента функции DETECTLANGUAGE Google Sheets, которая идентифицирует язык заданного текста.

Однако существуют обходные пути, которые можно использовать:

Вариант 1. Внешние инструменты

1. Microsoft Translator. Для определения языка текста можно использовать внешние средства, такие как Microsoft Translator. Скопируйте текст в средство перевода, определите язык и вставьте его обратно в Excel.

2. API Google Translate. Если вы знакомы с программированием, вы можете использовать APIGoogle Translate для определения языка и создания пользовательского решения. Это требует интеграции API и невозможно изначально в Excel для Интернета.

Вариант 2. Power Automate с microsoft Cognitive Services

Если вы хотите автоматизировать этот процесс в Excel Online, вы можете использовать Power Automate с Microsoft Azure Cognitive Services для определения языка. Ниже рассказывается, как это сделать.

Действия:

1. Настройте Power Automate с помощью Excel для Интернета.

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

3. Интеграция с Azure Cognitive Services для определения языка текста.

4. Выведите обнаруженный язык обратно в Excel.

Для этого решения потребуется доступ к службам Azure и настройка рабочего процесса Power Automate.

Excel для Интернета сейчас не поддерживает спарклайны напрямую. Эта функция доступна в классической версии Excel, но не в веб-версии.

Обходной путь для Excel в Интернете:

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

1. Диаграммы:

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

   • Перейдите на вкладку Вставка и выберите Диаграмма , чтобы создать диаграмму, которая помещается в диапазон данных.

2. Условное форматирование:

   • Используйте условное форматирование для создания визуального представления данных. Например, можно использовать гистограммы для отображения значений относительно друг друга.

   • Выберите данные, а затем перейдите в раздел Главная > условное форматирование > гистограммы данных.

3. Представление изображения:

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

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

Использование существующих функций для вычисления IMTANH

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

Пошаговое руководство

1. Введите комплексное число в ячейку, скажем, A1. Например, 2+3i.

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

=IMDIV(IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))),IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))))

Пример. Гиперболический тангенс комплексного числа

• Комплексное число: 2+3i в ячейке A1

• Формула: =IMDIV(IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))),IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))))

• Результат: 1.00323862735361 - 0.00376402564150425i

Описание

• IMEXP: вычисляет экспоненциал комплексного числа.

• IMSUM: добавляет два комплексных числа.

• IMPRODUCT: умножает два комплексных числа.

• IMSUB: вычитает одно комплексное число из другого.

• IMDIV: делит одно комплексное число на другое.

Эта формула эффективно реплицирует функцию IMTANH, используя экспоненциальную форму гиперболического котангента.

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

Использование существующих функций для вычисления IMCOTH

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

Пошаговое руководство

1. Введите комплексное число в ячейку, скажем, A1. Например, 2+3i.

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

=IMDIV(IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))),IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))))

Пример. Гиперболический котангент комплексного числа

• Комплексное число: 2+3i в ячейке A1

• Формула: =IMDIV(IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))),IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1;A1))))

• Результат: 0,99675796569358 + 0,00373971037633696i

Описание

• IMEXP: вычисляет экспоненциал комплексного числа.

• IMSUM: добавляет два комплексных числа.

• IMPRODUCT: умножает два комплексных числа.

• IMSUB: вычитает одно комплексное число из другого.

• IMDIV: делит одно комплексное число на другое.

Эта формула эффективно реплицирует функцию IMCOTH, используя экспоненциальную форму гиперболического котангента.

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

Использование проверки данных и формул

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

1. Выделите ячейки, в которых требуется применить проверку.

2. Перейдите на вкладкуДанные .

3. Щелкните Проверка данных.

4. Выберите Настраиваемый в раскрывающемся меню Разрешить .

5. Введите следующую формулу в поле Формула:

=AND(ISERROR(FIND(" ",A1)), LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1, IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)0), ISERROR(FIND(",",A1)), NOT(IFERROR(SEARCH(".",A1,SEARCH("@",A1))-SEARCH("@",A1),0)=1), LEFT(A1,1)<>"@", RIGHT(A1,1)<>"@")

Объяснение формулы

• ISERROR(FIND(" ",A1))): гарантирует отсутствие пробелов в адресе электронной почты.

• LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1: гарантирует наличие одного символа "@".

• IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)),0): гарантирует, что после символа "@" есть период.

• ISERROR(FIND(",",A1))): гарантирует отсутствие запятых.

• NOT(IFERROR(SEARCH(".",A1;SEARCH("@",A1))-SEARCH("@",A1),0)=1): гарантирует, что точка не находится непосредственно после символа "@".

• LEFT(A1,1)<>".": гарантирует, что адрес электронной почты не начинается с точки.

• RIGHT(A1,1)<>".": гарантирует, что адрес электронной почты не заканчивается точкой.

Пример варианта использования

1. Введите адреса электронной почты в столбце A (например, A1:A10).

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

3. Недопустимые адреса электронной почты будут помечены на основе условий, заданных в формуле.

​​​​​​​​​​​​​​

Excel в Интернете не имеет прямого эквивалента функции ISURL Google Sheets, но вы можете выполнить аналогичную проверку URL-адреса с помощью сочетания функций Excel. Ниже приведен метод для проверка, если ячейка содержит допустимый URL-адрес:

Использование формул для проверки URL-адресов

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

1. Выделите ячейки, в которых требуется применить проверку.

2. Перейдите на вкладку Данные.

3. Щелкните Проверка данных.

4. ВыберитеНастраиваемый в раскрывающемся меню Разрешить .

5. Введите следующую формулу в поле Формула:

=AND(ISNUMBER(FIND(".", A1)), OR(LEFT(A1, 7) = "http://", LEFT(A1, 8) = "https://"))

Объяснение формулы

• ISUMBER(FIND(".", A1))): гарантирует наличие хотя бы одного периода в URL-адресе.

• OR(LEFT(A1, 7) = "http://", LEFT(A1, 8) = "https://"): url-адрес начинается с "http://" или "https://".

Пример варианта использования

1. Введите URL-адреса в столбце A (например, A1:A10).

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

3. Недопустимые URL-адреса будут помечены на основе условий, заданных в формуле.

​​​​​​​

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

Способ 1. Использование TEXTJOIN и FILTERXML

1. Введите данные в диапазон, например A1:C3.

2. Для выравнивания диапазона используйте следующую формулу:

=FILTERXML("<><b>" & TEXTJOIN("</b><b>", TRUE, A1:C3) & "</b></a>", "//b")

Описание

• TEXTJOIN: объединяет значения в диапазоне в одну строку, разделенную </b><b>.

• FILTERXML: анализирует объединенную строку как XML и извлекает значения.

Пример

• Диапазон данных: A1:C3, содержащий:

• 1 2 3

• 4 5 6

• 7 8 9

• Формула: =FILTERXML("<><b>" & TEXTJOIN("</b><b>", TRUE, A1:C3) & "</b></a>", "//b")

• Результат: один столбец со значениями 1, 2, 3, 4, 5, 6, 7, 8, 9.

Способ 2. Использование INDEX и SEQUENCE

1. Введите данные в диапазон, например A1:C3.

2. Для выравнивания диапазона используйте следующую формулу:

=INDEX(A1:C3, ROUNDUP(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3)) / COLUMNS(A1:C3), 0), MOD(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3), , 0), COLUMNS(A1:C3)) + 1)

Описание

• SEQUENCE: создает последовательность чисел.

• ROUNDUP: определяет индекс строки.

• MOD: определяет индекс столбца.

• INDEX: извлекает значение из указанной строки и столбца.

Пример

• Диапазон данных: A1:C3, содержащий:

• 1 2 3

• 4 5 6

• 7 8 9

• Формула: =INDEX(A1:C3, ROUNDUP(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3)) / COLUMNS(A1:C3), 0), MOD(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3), , 0), COLUMNS(A1:C3)) + 1)

• Результат: один столбец со значениями 1, 2, 3, 4, 5, 6, 7, 8, 9.

Эти методы эффективно реплицируют функцию FLATTEN путем преобразования диапазона данных в один столбец.

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

Использование существующих функций для вычисления IMLOG

Для вычисления логарифма комплексного числа для любой базы можно использовать естественный логарифм (IMLN) и изменение базовой формулы:

Пошаговое руководство

1. Введите комплексное число в ячейку, скажем, A1. Например, 2+3i.

2. Введите основание в другую ячейку, например B1. Например, 10.

3. Чтобы вычислить логарифм, используйте следующую формулу:

=IMDIV(IMLN(A1), IMLN(B1))

Пример. Логарифм комплексного числа с основанием 10

• Комплексное число: 2+3i в ячейке A1

• Базовый: 10 в ячейке B1

• Формула: =IMDIV(IMLN(A1), IMLN(B1))

• Результат: логарифм 2+3i с основанием 10.

Описание

• IMLN: вычисляет естественный логарифм комплексного числа.

• IMDIV: делит одно комплексное число на другое.

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

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

Использование формул для проверки дат

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

• Выделите ячейки, в которых требуется применить проверку.

• Перейдите на вкладку Данные.

• Щелкните Проверка данных.

• Выберите Настраиваемый в раскрывающемся меню Разрешить .

• Введите следующую формулу в поле Формула:
  
  =AND(ISNUMBER(A1), A1>0, A1<DATE(9999;12;31))

Объяснение формулы

• ISNUMBER(A1): гарантирует, что ячейка содержит число.

• A1>0: гарантирует, что дата указана после 1 января 1900 г. (дата начала Excel).

• A1<DATE(9999;12;31): гарантирует, что дата находится до 31 декабря 9999 года.

Пример варианта использования

1. Введите даты в столбце A (например, A1:A10).

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

3. Недопустимые даты будут помечены на основе условий, заданных в формуле.

​​​​​​​

Excel в Интернете не имеет прямого эквивалента функции COUNTUNIQUEIFS Google Sheets, но аналогичные результаты можно достичь с помощью сочетания существующих функций. Это можно сделать следующим образом:

Использование сочетания СУММ, ЕСЛИ, ЧАСТОТЫ и СОВПАДЕНИЯ

• Введите данные в диапазон, скажите A1:A10 для значений, которые вы хотите подсчитать уникальным образом, и B1:B10 для условий.

• Используйте следующую формулу массива для подсчета уникальных значений на основе условий:

• =SUM(IF(FREQUENCY(IF(B1:B10="criteria", MATCH(A1:A10, A1:A10, 0)), ROW(A1:A10)-ROW(A1)+1), 1))

Пример. Подсчет уникальных значений на основе одного критерия

• Диапазон данных: A1:A10, содержащий значения.

• Диапазон условий: B1:B10, содержащий условия.

• Критерий: "Да" (его можно заменить фактическим критерием).

• Формула: =SUM(IF(FREQUENCY(IF(B1:B10="Да", MATCH(A1:A10, A1:A10, 0)), ROW(A1:A10)-ROW(A1)+1), 1))

• Результат. Количество уникальных значений в A1:A10, где соответствующее значение в B1:B10 — "Да".

Описание

• MATCH: находит относительное положение каждого значения в диапазоне.

• ЕСЛИ: применяет критерии для фильтрации значений.

• FREQUENCY: подсчитывает вхождения каждого уникального значения.

• СУММ: суммирует количество уникальных данных.

Использование Power Query для более сложных сценариев

Для более сложных сценариев с несколькими критериями можно использовать Power Query:

• Загрузите данные в Power Query.

• Применяйте фильтры в соответствии со своими критериями.

• Удалите дубликаты, чтобы получить уникальные значения.

• Подсчитайте строки, чтобы получить уникальное число.

Пример варианта использования в Power Query

• Загрузка данных из таблицы или диапазона.

• Фильтрация строк на основе условий.

• Удалите дубликаты.

• Подсчитайте строки, чтобы получить уникальное число.

Эти методы эффективно реплицируют функцию COUNTUNIQUEIFS путем объединения существующих функций и средств Excel.

В Excel в Интернете можно вычислить погрешность с помощью сочетания существующих функций. Функция MARGINOFERROR в Google Sheets эквивалентна использованию CONFIDENCE. Функция T вместе со стандартными отклонениями и функциями count в Excel. Это можно сделать следующим образом:

Пошаговое руководство

• Введите данные в диапазон, например A1:A10.

• Вычислите среднее значение выборки с помощью функции AVERAGE:

• =AVERAGE(A1:A10)

• Вычислите стандартное отклонение выборки с помощью STDEV. Функция S:

• =STDEV. S(A1:A10)

• Вычислите размер выборки с помощью функции COUNT:

• =COUNT(A1:A10)

• Определите уровень достоверности (например, 0,95 для 95 % достоверности).

• Вычислите погрешность с помощью значения CONFIDENCE. Функция T:

• =ДОСТОВЕРНОСТЬ. T(1 – 0,95, STDEV. S(A1:A10), COUNT(A1:A10))

Пример. Вычисление погрешности для примера набора данных

• Диапазон данных: A1:A10, содержащий примеры значений.

• Уровень достоверности: 95% (0,95).

• Формулы:

  • Среднее пример: =AVERAGE(A1:A10)

  • Стандартное отклонение образца: =STDEV. S(A1:A10)

  • Размер выборки: =COUNT(A1:A10)

  • Поле ошибки: =ДОСТОВЕРНОСТЬ. T(1 – 0,95, STDEV. S(A1:A10), COUNT(A1:A10))

Описание

• УВЕРЕННОСТЬ. T. Вычисляет погрешность для указанного уровня достоверности, стандартного отклонения и размера выборки.

• STDEV. S: вычисляет стандартное отклонение выборки.

• COUNT: подсчитывает количество точек данных в образце.

Этот метод эффективно реплицирует функцию MARGINOFERROR с помощью CONFIDENCE. Функция T вместе со стандартными отклонениями и вычислениями счетчиков

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

Пошаговое руководство

• Введите метку времени эпохи Unix в ячейку, например A1. Например, 1655906710.

• Чтобы преобразовать метку времени в дату, используйте следующую формулу:

Для меток времени в секундах

=A1 / 86400 + ДАТА(1970;1;1)

Для меток времени в миллисекундах

=A1 / 86400000 + ДАТА(1970;1;1)

Пример

Пример 1. Преобразование метки времени Unix в секундах

• Метка времени: 1655906710 в ячейке A1

• Формула: =A1 / 86400 + ДАТА(1970;1;1)

• Результат: 22.06.2022 14:05:10

Пример 2. Преобразование метки времени Unix в миллисекундах

• Метка времени: 1655906710000 в ячейке A1

• Формула: =A1 / 86400000 + ДАТА(1970;1;1)

• Результат: 22.06.2022 14:05:10

Описание

• 86400: количество секунд в день.

• 86400000: количество миллисекунда в день.

• ДАТА(1970;1;1): дата начала эпохи Unix.

Дополнительные советы

​​​​​​​​​​​​​​