У меня есть длинная таблица со столбцами для образца, номера анализа, метода испытания и т. д. и, что важно, химического элемента, который был проанализирован. Я хотел бы отсортировать свою таблицу сначала по образцу, а затем по химическому элементу. Символы элементов должны быть в порядке увеличения атомного номера: H < He < Li < Be < B < C < N и т. д. вплоть до Pb. Можно ли это сделать в Excel?
решение1
Сортировать данные по атомному номеру. Вот запрос, который возвращает некоторые данные из Википедии:
let
Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/List_of_elements_by_atomic_properties")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Z", type text}, {"Name", type text}, {"Symbol", type text}, {"Average atomic mass", type text}, {"Electronegativity (Pauling)", type text}, {"First Ionization Energy (eV)", type text}, {"Radii (pm) Atomic", type text}, {"Radii (pm) Van der Waals", type text}, {"Radii (pm) Covalent", type text}, {"Valence electrons", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Z] <> "Z")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Z", Int64.Type}, {"Valence electrons", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","—","",Replacer.ReplaceText,{"Radii (pm) Van der Waals"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","—","",Replacer.ReplaceText,{"Radii (pm) Covalent"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","—","",Replacer.ReplaceText,{"Radii (pm) Atomic"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"Radii (pm) Atomic", Int64.Type}, {"Radii (pm) Covalent", Int64.Type}, {"First Ionization Energy (eV)", type number}}),
#"Replaced Value3" = Table.ReplaceValue(#"Changed Type2","—","",Replacer.ReplaceText,{"Electronegativity (Pauling)"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value3",{{"Electronegativity (Pauling)", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Z", "Atomic Number"}})
in
#"Renamed Columns"
Вы можете использовать Данные>Получить данные>Из других источников>Пустой запрос, затем, когда откроется редактор Power Query, вы можете открыть Расширенный редактор, затем вставить запрос выше в окно Расширенного редактора. Когда вы нажмете «Закрыть и загрузить», данные будут помещены обратно на новый лист в вашей рабочей книге. Затем вы можете:
- Используйте
XLOOKUP
для добавления атомного номера к вашим данным, а затем сортируйте по этому столбцу, или, если вы хотите получить немного более сложный, но более мощный метод, - Загрузите оба набора данных в модель данных PowerPivot, создайте соединение по имени элемента, затем установите свойства сортировки для столбца элемента, чтобы использовать атомный номер. Если вы это сделаете, элементы будут сортироваться по атомному номеру во всех сводных таблицах, созданных из модели данных PowerPivot
Кстати, если вы загрузите этот запрос в Power Query, вы также можете выбрать столбец «Имя», а затем использовать опцию Преобразование>Создать тип данных, например:
Что позволяет вам делать такие интересные вещи, как:
решение2
Да, это возможно. Например, если у вас есть это.
Вы можете щелкнуть по столбцу, который хотите отсортировать, и использовать инструмент «Сортировка и фильтр» на вкладке «Главная» в Excel, чтобы отсортировать его от меньшего к большему, как показано ниже.
Затем, когда появится это сообщение, убедитесь, что оно включено, Расширьте выбор и нажмите Сортировать.
решение3
(1) Создайте пользовательскую функцию Excel VBA ATOMICNUMBER()
Нажмите Alt+ F11, чтобы открыть редактор Visual Basic (на Mac нажмите FN+ ALT+ F11), а затем нажмите Вставка > Модуль.
Скопируйте и вставьте следующий код в новый модуль.
Function ATOMICNUMBER(arg1 as String) as Integer
elementSymbols = Array("foo", "H", "He", "Li", "Be", "B", "C", "N", "O", "F", "Ne", "Na", "Mg", "Al", "Si", "P", "S", "Cl", "Ar", "K", "Ca", "Sc", "Ti", "V", "Cr", "Mn", "Fe", "Co", "Ni", "Cu", "Zn", "Ga", "Ge", "As", "Se", "Br", "Kr", "Rb", "Sr", "Y", "Zr", "Nb", "Mo", "Tc", "Ru", "Rh", "Pd", "Ag", "Cd", "In", "Sn", "Sb", "Te", "I", "Xe", "Cs", "Ba", "La", "Ce", "Pr", "Nd", "Pm", "Sm", "Eu", "Gd", "Tb", "Dy", "Ho", "Er", "Tm", "Yb", "Lu", "Hf", "Ta", "W", "Re", "Os", "Ir", "Pt", "Au", "Hg", "Tl", "Pb", "Bi", "Po", "At", "Rn", "Fr", "Ra", "Ac", "Th", "Pa", "U", "Np", "Pu", "Am", "Cm", "Bk", "Cf", "Es", "Fm", "Md", "No", "Lr", "Rf", "Db", "Sg", "Bh", "Hs", "Mt", "Ds", "Rg", "Cn", "Nh", "Fl", "Mc", "Lv", "Ts", "Og")
ATOMICNUMBER = 0
For i = 1 To UBound(elementSymbols)
If elementSymbols(i) = arg1 Then
ATOMICNUMBER = i
Exit For
End If
Next
End Function
Закройте редактор Visual Basic.
(2) Добавить вспомогательный столбец
Вставьте новый столбец рядом с символами элементов.
Заполните его=ATOMICNUMBER(
соседняя ячейка, содержащая символ элемента)
.
(3) Сортировать
Выберите все данные.
Меню Данные > Сортировать...
Сначала сортируйте по образцу, затем по атомному номеру.